Update Multiple Timestamps With Triggers - Useful MySQL Tricks Series
One feature about MySQL that provides some frustration to developers is the fact that only one timestamp field can have a default value of CURRENT_TIMESTAMP. This becomes an issue when a table needs to have a created timestamp and a modified timestamp, and the developer does not want to or cannot use the development language to insert a timestamp into the database. Which field do you give the current timestamp default to? What if you have more than two timestamp fields? How does one ensure that these timestamps are always correctly updated? The answer to these questions lies in a fundamental action of MySQL databases: The trigger.
Triggers are actions that occur based on the execution of a SQL statement. For example, you could set up a trigger to update a field of Table B when a record is inserted into Table A.
This applies to timestamps in that a trigger can be set up to place the current timestamp in any desired field. Follow these steps to get your tables set up with automatically modified timestamps:
1. Upon creating a table, set the created timestamp field to the default value CURRENT_TIMESTAMP. As this field only needs to be entered once, we can give this field the default value and not have to worry about creating it from the trigger.
2. Create a trigger entitled ‘table_timestamp’, where ‘table’ would be the table for which you are creating the trigger. This is not necessary, but helps in organizing your triggers. The syntax below explains how to create a trigger:
DELIMITER // CREATE TRIGGER `yourdatabase`.` table_timestamp’ BEFORE UPDATE ON `yourdatabase`.`table` FOR EACH ROW begin
The DELIMITER tag is essential, and must come before and after the trigger syntax. In addition, “BEFORE UPDATE” means that the trigger executes before updates take place on ‘table’.
The ‘FOR EACH ROW begin’ syntax simply tells mysql to execute this trigger on each update.
3. Now, it’s time to define what fields will be updated by the trigger. In this case, we have a modified timestamp field that needs to be set to the current timestamp:
This command ensures that whenever a row is modified, the current timestamp is recorded. You could expand on this in any number of ways to manipulate timestamps in the database, but that is beyond the scope of this short tutorial.
4. Finally, the trigger needs to be ended.
The ‘end’ command tells mysql the trigger is finished running, and note that we put the delimiter at the end of the trigger as well. And there you have it! You can manage multiple timestamps easily through a simple trigger.
DELIMITER // CREATE TRIGGER `yourdatabase`.`table_timestamp` BEFORE UPDATE ON `yourdatabase`.`table` FOR EACH ROW begin set new.modified_timestamp=CURRENT_TIMESTAMP(); end //