As of MySQL 5.6 its easy-peasy... give it a try:
create table tweet (
id integer not null auto_increment primary key,
stamp_created timestamp default now(),
stamp_updated timestamp default now() on update now(),
message varchar(163)
) or `inserted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`insert_src_ver_id` INT NULL,
`updated` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
`update_src_ver_id` INT NULL, for MySQL 5.5 or older CREATE TABLE `entity` (
`entityid` int(11) NOT NULL AUTO_INCREMENT,
`createDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastModified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`name` varchar(255) DEFAULT NULL,
`comment` text,
PRIMARY KEY (`entityid`),
)
Then add these triggers:DELIMITER ;;
CREATE trigger entityinsert BEFORE INSERT ON entity FOR EACH ROW BEGIN SET NEW.createDate=IF(ISNULL(NEW.createDate) OR NEW.createDate='0000-00-00 00:00:00', CURRENT_TIMESTAMP, IF(NEW.createDate<CURRENT_TIMESTAMP, NEW.createDate, CURRENT_TIMESTAMP));SET NEW.lastModified=NEW.createDate; END;;
DELIMITER ;
CREATE trigger entityupdate BEFORE UPDATE ON entity FOR EACH ROW SET NEW.lastModified=IF(NEW.lastModified<OLD.lastModified, OLD.lastModified, CURRENT_TIMESTAMP);
- If you insert without specifying createDate or lastModified, they will be equal and set to the current timestamp.
- If you update them without specifying createDate or lastModified, the lastModified will be set to the current timestamp.
- If you insert, you can specify a createDate older than the current timestamp, allowing imports from older times to work well (lastModified will be equal to createDate).
- If you update, you can specify a lastModified older than the previous value ('0000-00-00 00:00:00' works well), allowing to update an entry if you're doing cosmetic changes (fixing a typo in a comment) and you want to keep the old lastModified date. This will not modify the lastModified date.
