MySQL (DEFAULT + ON UPDATE) TIMESTAMPs

Question

I've a table where I've two fields:

dateCreated
dateUpdated

I want both fields to hold timestamps, dateCreated to have a DEFAULT CURRENT TIMESTAMP flag and dateUpdated to have a ON UPDATE CURRENT TIMESTAMP behavior, however seems that I can't have more than one timestamps field types on the same table.

It would be really useful if the database could take care of this for me, can I circumvent this issue somehow?

Answer 1

YES WE CAN.

You can use a trigger to update the field, like so:

create trigger CreationTimestamp after insert on MyTable for each row
begin
    update MyTable set CreateField = UpdateField where id = new.id
end

This way, you can set your auto-update field using the native MySQL method, and use this to capture the creation date.