Create mysql trigger to update specific column after update row

Question

I want to update the specific column in MySQL after any change occurred in a row. I use below query to create trigger but when to change record data, an error happened.

trigger query:

DELIMITER $$
CREATE TRIGGER after_user_update AFTER UPDATE ON group_chat_message
FOR EACH ROW
BEGIN 
   UPDATE `group_chat_message` SET 
   `server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8))
    WHERE id = NEW.id
    ;
END;$$
DELIMITER ;

_{(source: imohammadi.ir)}

Answer 1

Your trigger should be :

DELIMITER $$
CREATE TRIGGER after_user_update BEFORE UPDATE ON group_chat_message
FOR EACH ROW
BEGIN 
   SET NEW.`server_modified_at` = CONCAT(SUBSTRING(REPLACE(NOW(), '-', ''),1,8),SUBSTRING(REPLACE(NOW(), ':', ''),12,8));
END;$$
DELIMITER ;

So basically, you should trigger it BEFORE and not AFTER, and you don't need to create this UPDATE instruction in the trigger. Instead, just modify the NEW value of server_modified_at before updating the row

---

Note that, if the aim is to store the timestamp when a record is modified, you can create a column like this in your table :

server_modified_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP

and MySQL will set the current timestamp to your server_modified_at column automatically when the row is updated, you don't need a trigger for that.

Answer 2

You should simply modify your CREATE TABLE-Statement:

What you need can be achieved without a trigger, by using ON UPDATE for Timestamps and/or DateTime columns:

CREATE TABLE t1 (
  some columns,
  lastModified_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  lastModified dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The fields will be set to NOW() everytime a row is modified (ON UPDATE) or created (DEFAULT).

https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html