14

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

CREATE TRIGGER col_update
AFTER UPDATE ON col
FOR EACH ROW BEGIN
UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id
END

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 store the update time in the corresponding column?

I wish to use a trigger because there are many columns in the table. If I try to set the update time manually, I would need to modify many queries.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Googlebot
  • 4,551
  • 26
  • 70
  • 96

3 Answers3

19

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.

András Váczi
  • 31,778
  • 13
  • 102
  • 151
1

If like in your case, you want to change the updated field every time the record has been changed, you do not need a trigger.

You can use MySQL's automatic initialization/updating like this:

ALTER TABLE `yourtabelname` ADD `updated` DATETIME on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Or at the time of table creation, from the docs:

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
Tobias Beuving
  • 111
  • 2
0

Use, WHEN (pg_trigger_depth() < 1)

CREATE TRIGGER trigger_name
after insert or update
ON [TABLE] FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE public.proc_name();
nbk
  • 8,699
  • 6
  • 14
  • 27
ppmview
  • 1