mysqltriggersonupdate

Where can 'on update' be used?

I need help with on update in MySQL.

Currently I need to set up a created and updated row in a table documents that has keywords related by a Foreign Key.

So I create a trigger for the keywords to update the updated time of the related document and I need to create another for Documents table.

The question is if I can use on update instead of a trigger.

I've tried searching about on update statement to see if it refers to the whole record being updated, in how many columns can it be used, etc, but I didn't find any MySQL doc's topic on it.

Any help will be much appreciated.

Solution

  • For implementing this issue you don't need to use trigger. That is because created has logical meaning to be written once and for all. So you'll pass something like NOW() when inserting your row.

    Now, as for updated, which has logical meaning of last change timestamp - then with defining timestamp field you can specify ON UPDATE CURRENT_TIMESTAMP clause:

    CREATE TABLE t1 (   
  s TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

    -that's it. I.e. while in common case for update events you'll have to use triggers, this is the case, when MySQL provides built-in solution. To say more, this is the case when you're expected to use that - i.e. that is why it exist in MySQL.

    Common answer to question 'when' is opinion based, but implementation of logical 'update date' is the general purpose.