MySQL Local Variable Scope and Triggers

MySQLtrigger

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?

Best Answer

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

Related Solutions

Postgresql – Triggers not being run on a partitioned table

The trigger is AFTER INSERT OR UPDATE. As you can see in your function, it ends with:

RETURN NULL;

That means that the row is discarded and any changes to the row are void. Actually, the row returned from an AFTER trigger is discarded in any case. The RETURN NULL is just a reminder.

You need to do this in a BEFORE trigger or issue an explicit UPDATE statement. The second option is very tricky, however, because you could end up with an endless loop. Better make it a BEFORE trigger.

As an aside: the conditional expression could be simplified with a CASE statement (since version 8.4):

CASE NEW.timeleft
WHEN 'SHORT' THEN
    NEW.expires := NEW.updated + interval '30 minutes';
WHEN 'MEDIUM' THEN
    NEW.expires := NEW.updated + interval '2 hours';
...
ELSE
    RAISE EXCEPTION 'timeleft was unrecognised. ...';
END CASE;

Mysql – Trouble with triggers and insert .. on duplicate key update

This question came back on my radar after a recent comment was posted.

This issue at the time turned out to be this reporting table that was meant to be maintained by triggers was on a slave only.

The insert on duplicate key statements were getting flagged as not safe for statement based replication and getting pushed through in RBR (the stream was in mixed mode).

RBR events do not fire triggers.

Best Answer

Related Solutions

Postgresql – Triggers not being run on a partitioned table

Mysql – Trouble with triggers and insert .. on duplicate key update

Related Question