CODEWITHSUNDEEP
mysql
pll
pll

Reputation: 297

MYSQL Trigger recursion avoidance

I have a general AFTER UPDATE trigger for my users table to check if a column has changed, and if so, call a stored procedure.

The problem is the stored procedure does some calculations and itself updates a field in users.

How do I avoid the recursion if my stored procedure updates the users table, which invokes the trigger, which again invokes the stored procedure?

Thanks!

Upvotes: 1

Views: 1600

Answers (1)

Doug Kress
Doug Kress

Reputation: 3537

MySQL doesn't let you disable triggers (without dropping and recreating them), but you have a couple of options:

  1. Don't update the users table from within the procedure.
  2. Add a field to the users that the procedure would set to a specific value on update. When the trigger sees that value for that field, don't call the procedure.
  3. Use a global variable to accomplish the above (NOT connection safe - will disable triggers for all connections).

Upvotes: 1

Related Questions