MySQL Local Variable Scope and Triggers

Question

I have a couple triggers that automatically update invoice data and set some statuses in the database.

However, it appears that if 2 Triggers have variables that are named the same, they get overwritten when one trigger performs an update the causes the other trigger to run.

For Example,

My BEFORE INSERT trigger on my payments table sets the variable @amountPaid

SET @amountPaid = @totalPayments + NEW.amount

Sometimes the before insert on payments will trigger an update to the main record which fires my BEFORE UPDATE trigger which selects the total payments into @amountPaid for it's own conditionals. However, at this point @amountPaid is 0 from the this perspective because the insert on payments hasn't completed, but this isn't important. What's very odd is that the @amountPaid variable in the payments trigger is now 0.

So now I'm curious as to how scope works in MySQL?

Answer 1

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..