4

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?

Alix Axel
  • 151,645
  • 95
  • 393
  • 500

1 Answers1

2

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.

Eric
  • 92,005
  • 12
  • 114
  • 115