Trying to understand how to make a trigger in mysql to automatically set a value in a record field. Not even sure if I need a BEFORE or AFTER trigger.

I have a table with one field named date_created and another date_updated, both of type datetime. The date_created field is set to NOT NULL and default to CURRENT_TIMESTAMP. The date_updated field is currently set to NULL, but I can change that if needed.

Whenever a new record is created, the date_created field gets filled automatically. This way I can omit the field from an input FORM.

Now whenever the record gets updated, I'd like for the date_updated field to be filled-in automatically - for any subsequent updates.

I've tried several versions but keep getting errors.

CodePudding user response：

You don't need a trigger for this case. Use DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

Read this manual page for details: https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html