Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to make a trigger to record the time of any update as

UPDATE col SET updated=NOW() WHERE; // or

The problem is that when this trigger tries to UPDATE the updated column, it is also another UPDATE event which runs the trigger. This will create an infinite loop, which does not work.

How can I restore the update time in the corresponding column?

NOTE: I wish to use trigger, as there are many columns in the table. If I try to set the update time manually, I need to modify many queries.

share|improve this question
up vote 11 down vote accepted

Use a BEFORE trigger instead, and set the updated column assigning a value to NEW.updated (this would be a simple assignment, not an UPDATE). This way you won't trigger additional UPDATEs.

Your trigger body will simply look like

SET NEW.updated = NOW()

I usually use AFTER triggers only for modifying other tables, BEFORE for modifying the new (or updated) row, or suppress DELETE.

In general, you could also fiddle around with the TG_LEVEL special variable to prevent subsequent triggers from firing - in my view it's always better to avoid it, as it means too many triggers.

share|improve this answer
What is the benefit of BEFORE over AFTER (not in this case), because you said you usually use BEFORE? and what do you mean by setting the updated column as NEW.updated? – All Jul 3 '12 at 17:45
@Ali - there is no benefit, their respective use cases differ. I rephrased my answer, it was ambiguous - thanks for the warning. – dezso Jul 3 '12 at 18:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.