0

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.

JorgeeFG
  • 5,651
  • 12
  • 59
  • 92

1 Answers1

0

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.

Alma Do
  • 37,009
  • 9
  • 76
  • 105
  • Thanks. Is there a topic about the `on update` clause? I couldn't find anything about it. – JorgeeFG Oct 21 '13 at 13:08
  • What do you mean 'topic'? You should just search through SO then, if you want. But I think explanations in official manual page is quite enough to clarify general purpose of this feature – Alma Do Oct 21 '13 at 13:10
  • With topic I mean official documentation about what `on update` is, I know its very obvious, but I'd like to see what can I do with it, if it creates a trigger, etc. – JorgeeFG Oct 21 '13 at 13:15
  • Well, it doesn't (i.e. defining such field will not create similar trigger). Obviously it creates some event-binding inside MySQL implementation on code level, but you can find exact answer only inside source code. – Alma Do Oct 21 '13 at 13:17