MySQL 'Update Timestamp' Column - Trigger

Question

I'm adding a column tsu (timestamp update) of type DATETIME to a number of my tables.

I need to write BEFORE UPDATE triggers that will update the column to CURRENT_TIMESTAMP(), but I can't get it right. Tried:

DELIMITER $$
CREATE
    TRIGGER `cams`.`tsu_update_csi` BEFORE UPDATE
    ON `cams`.`csi`
    FOR EACH ROW BEGIN
        UPDATE csi SET tsu = CURRENT_TIMESTAMP WHERE csi_code = OLD.csi_code;
    END$$
DELIMITER ;

Can anyone point me in the right direction pls? MTIA

Answer 1

If the field can be defined as a timestamp, you can use the following:

ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP);

Answer 2

Okay, try this one:

DELIMITER $$ CREATE  
    TRIGGER `cams`.`tsu_update_csi` BEFORE UPDATE  
    ON `cams`.`csi`  
      FOR EACH ROW BEGIN  
        SET NEW.tsu = CURRENT_TIMESTAMP;   
END$$ DELIMITER ;

Answer 3

Don't know if it'll work for you, but you can always make it a TIMESTAMP field with no default value -- MySQL will automatically set the value of the first such field defined in the table to the current timestamp on every update.