Created & Update columns

Here is a handy trigger for automatically setting and updating created and updated columns in a MySQL table.


CREATE TABLE users ( created_at timestamp, updated_at timestamp );
DELIMITER //
create trigger users_trg_insert
BEFORE INSERT ON `users`
FOR EACH ROW BEGIN
set NEW.created_at = CURRENT_TIMESTAMP;
set NEW.updated_at = CURRENT_TIMESTAMP;
END
//
DELIMITER //
create trigger users_trg_update
before UPDATE ON `users`
FOR EACH ROW BEGIN
set NEW.created_at = CURRENT_TIMESTAMP;
set NEW.updated_at = OLD.updated_at;
END
//

Basically the code forces the servers timestamp on inserts and re-uses the old value for created_at on updates.