Auto last_modified_date and creation_date in MySQL 5.0

There's a few different solutions out there to have two auto updating timestamp columns in MySQL 5.0. One using two triggers works, but seems overly complicated as you can have the last_modified_date updated by MySQL without a trigger.

Here's my recipe after fooling around a bit:

1) Declare you table with an auto updating last_modified date:

CREATE TABLE foos (
id int primary key auto_increment,
bar varchar(64) not null,
creation_date timestamp default '0000-00-00 00:00:00',
last_modified_date timestamp on update current_timestamp default current_timestamp
)

The trick here is that you have to define a default for the creation_date timestamp, otherwise MySQL will complain that you can't have two updating timestamps. In this case I've chosen to use the update attribute on the last_modified_date column as I'm guessing that mechanism is faster than the trigger and I expect more updates in this table than inserts.

2) Create a trigger to insert the current time in the creation date column on insert:

create trigger foos_insert before insert on foos
for each row set NEW.creation_date = now();

This combination will cleanly keep both of your columns up to date as you perform inserts and updates. Note that if you'd like, you could default the last_modfied_date to null as well to easily be able to query for all rows that have never been updated.