CODEWITHSUNDEEP
mysqldatabase-trigger
Thomas Hupkens
Thomas Hupkens

Reputation: 1590

Mysql ON UPDATE CURRENT_TIMESTAMP vs. trigger that sets time to now() after update

While searching for something else, I found this trigger in our test database:

begin
     set NEW.UpdatedAt = now();
end

I'm puzzled... Is this any different from changing the column definition to the following?

alter table TableName change UpdatedAt ON UPDATE CURRENT_TIMESTAMP;

Are any advantages that I'm not seing to doing it in a trigger?

Upvotes: 1

Views: 1209

Answers (1)

spencer7593
spencer7593

Reputation: 108460

For earlier versions of MySQL, the ON UPDATE was available only for columns of datatype TIMESTAMP. That functionality was not supported for DATETIME or any other datatypes.

And with TIMESTAMP columns, it could be kind of tricky to get table defined with two TIMESTAMP columns to be automatically set; to get NOW() assigned to one column when the row was inserted, and to get NOW() assigned to a different column when the row was updated.

Triggers can work around those limitations.

Upvotes: 2

Related Questions