What is the best way to set up last-modified and date-record-added fields?

Question

Hi I would like to set and forget two fields for tracking the date the record was added and also the date the record was last modified in a mySQL database.

I am using "ON UPDATE CURRENT_TIMESTAMP" and was hoping I would just change UPDATE to INSERT.

No luck however. Can anyone give me the heads up on the best way to achieve this? - preferably inside the database itself.

Answer 1

This assumes MySQL 5. Simply add two triggers:

create table foo (a1 INT, created timestamp, updated timestamp) engine=innodb;

DELIMITER |

CREATE TRIGGER foo_created BEFORE INSERT ON foo
FOR EACH ROW BEGIN
SET new.created := now();
SET new.updated := now();
END;

|

CREATE TRIGGER foo_updated BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
SET new.updated := now();
END;
|

DELIMITER     ;

insert into foo (a1) values(7);
select * from foo;

update foo set a1=9;