It depends on the scope of the variable.
I once wrote a post Disable trigger for just one table which uses a variable called @TRIGGER_DISABLED
.
In order for the variable to disable the trigger, your session has to instantiate the value SET @TRIGGER_DISABLED=0;
or SET @TRIGGER_DISABLED=1;
before any DML
again the table. If you disconnect and reconnect, you have to instantiate the variable each time you reconnect.
In your case, when the DB session starts, @amountPaid
and @totalPayments
would have to set to 0 before doing anything. You cannot disconnect and reconnect if you are keeping running totals.
If you always disconnect and reconnect, the running totals could be stored in a global table that uses the MEMORY storage engine with the fields and a session ID of your choosing..
sorry fir bringing different code but am working with this wanted to make the question short...this trigger works but how to include the ON DUPLICATE KEY UPDATE
giving me tough time and dosent work out
DELIMITER
$$
CREATE TRIGGER inc_trig
BEFORE INSERT
ON income_
FOR EaCH ROW
BEGIN
DECLARE fees,bus,uniform,c_uniform,sp_wear,pullover,car INT;
SET fees=1; SET bus=2; SET uniform=3; SET c_uniform=4; SET sp_wear=5; SET pullover=6; SET car=7;
IF NEW.income_name_ = fees THEN
INSERT INTO income_summary_monthly (date_,fees_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);
ELSEIF NEW.income_name_ = bus THEN
INSERT INTO income_summary_monthly (date_,bus_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);
ELSEIF NEW.income_name_ = uniform THEN
INSERT INTO income_summary_monthly (date_,uniform_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);
ELSEIF NEW.income_name_ = c_uniform THEN
INSERT INTO income_summary_monthly (date_,c_uniform_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);
ELSEIF NEW.income_name_ = sp_wear THEN
INSERT INTO income_summary_monthly (date_,sp_wear_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);
ELSEIF NEW.income_name_ = pullover THEN
INSERT INTO income_summary_monthly (date_,pullover_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);
ELSEIF NEW.income_name_ = car THEN
INSERT INTO income_summary_monthly (date_,c_a_r_,month_,term_,schid_)
VALUES (new.date_,new.amount_,new.month_,new.term_,new.schid_);
END IF;
END;
$$
Best Answer
You forgot
END //