VocabReminder
Easily build a great vocabulary without studying! Now on the App Store!
Click here for more info about the app

Managing Timestamps in MySQL with a Trigger

       

MySQL doesn't support having two columns with time stamping on both initialization and/or on updating at the same time. It would be nice to be able to do this where the created_at column gets the current_timestamp on initialization and the updated_at gets changed on updating the row.

like so doesn't work...

create table entries( body blob, created_at datetime default current_timestamp, updated_at timestamp default current_timestamp on update current_timestamp );

Seems like a feature a lot of folks would like. There are two work-arounds. The first is baking it into your application code with something like


create table entries(
  body blob,
  created_at datetime default null,
  updated_at timestamp default current_timestamp on update current_timestamp
);
insert into entries (body, created_at) values ('hello world', now());

The second way is to create a trigger and call the trigger on your insert action on a row.


create table entries (
  body  blob,
  created_at datetime default null,
  updated_at timestamp default null on update current_timestamp
);
create trigger init_created_at before insert on entries for each row set new.created_at = now();
Now whenever a new row is created the trigger will be executed and set the time to the current timestamp. You can forget about the created_at column in your code because it's not meant to be changed.

Tagged w/ #databases #mysql #sql #timestamping #triggersdatabases