1

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!

Improve this question
1
  • Wondered that mysql allows it :-S Commented Aug 19, 2011 at 1:30

1 Answer 1

Reset to default
1

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).
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

For #3, would be better to use a session variable (ie, @var instead of @@var). You can even use it in such a way that it turns off when it detects the recursion happened (ie, in the trigger and proc, guard the potentially recursive code in IF IFNULL(@unique_recurse_var, 0) = 0 THEN SET @unique_recurse_var = 1; <do guarded operation>; ELSE SET @unique_recurse_var = 0; END IF;). You probably want unittests to make sure the functality never breaks though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.