Prior to MySQL < 5.6.5 only one DATETIME or TIMESTAMP field in a table could be updated automatically on INSERT or UPDATE.
From MySQL >=5.6.5 this restriction went away, so it’s now possible to have both a created_at and modified_at field for example (you can have more) and have MySQL default both on INSERT to the current date time and on UPDATE set the modified_at equally to the current date and time.
// MySQL >=5.6.5
ALTER TABLE the_table ADD modified_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE the_table ADD created_at datetime DEFAULT CURRENT_TIMESTAMP;
The only way to automate this functionality with MySQL < 5.6.5 was to use triggers.
// MySQL < 5.6.5
ALTER TABLE the_table ADD modified_at TIMESTAMP;
ALTER TABLE the_table ADD created_at datetime NOT NULL;
DELIMITER |
DROP TRIGGER IF EXISTS the_table_insert_trigger//
CREATE TRIGGER the_table_insert_trigger
BEFORE INSERT ON the_table
FOR EACH ROW
BEGIN
IF NEW.created_at = '0000-00-00 00:00:00' THEN
SET NEW.created_at = NOW();
END IF;
END;
END;
|
DELIMITER;