MySQL 'Update Timestamp' Column - Trigger

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

I need to write BEFORE UP开发者_JAVA百科DATE 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

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 ;

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

ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP);

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.

ts2 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP works for DATETIME and TIMESTAMP fields, for one and multiple fields

so you can use datecreated CURRENT_TIMESTAMP (as the default value)

and dateupdated CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP works for DATETIME and TIMESTAMP fields (as the default value)

You can use the above as SQL commands or default values in MySQL workbench

There are a lot of non-working or incomprehensible examples on the network. I spent several hours to put together a working example for my EFFCORE CMS. Here is this example:

Trigger on UPDATE

DELIMITER ;;
CREATE TRIGGER `updated_at__on_update`
BEFORE UPDATE
ON `demo`
FOR EACH ROW
BEGIN
   SET new.updated_at = UNIX_TIMESTAMP();
END;;
DELIMITER ;

Trigger on INSERT

DELIMITER ;;
CREATE TRIGGER `updated_at__on_insert`
BEFORE INSERT
ON `demo`
FOR EACH ROW
BEGIN
   SET new.updated_at = UNIX_TIMESTAMP();
END;;
DELIMITER ;

Table demo

CREATE TABLE `demo` (
    `id` int NOT NULL AUTO_INCREMENT,
    `text` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
    `updated_at` int NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);