MySQL Trigger After Update Only If Row Has Changed

MySQL Trigger after update only if row has changed

As a workaround, you could use the timestamp (old and new) for checking though, that one is not updated when there are no changes to the row. (Possibly that is the source for confusion? Because that one is also called 'on update' but is not executed when no change occurs)
Changes within one second will then not execute that part of the trigger, but in some cases that could be fine (like when you have an application that rejects fast changes anyway.)

For example, rather than

IF NEW.a <> OLD.a or NEW.b <> OLD.b /* etc, all the way to NEW.z <> OLD.z */ 
THEN  
  INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b) ;
END IF

you could use

IF NEW.ts <> OLD.ts 
THEN  
  INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b) ;
END IF

Then you don't have to change your trigger every time you update the scheme (the issue you mentioned in the question.)

EDIT: Added full example

create table foo (a INT, b INT, ts TIMESTAMP);
create table bar (a INT, b INT);

INSERT INTO foo (a,b) VALUES(1,1);
INSERT INTO foo (a,b) VALUES(2,2);
INSERT INTO foo (a,b) VALUES(3,3);

DELIMITER ///

CREATE TRIGGER ins_sum AFTER UPDATE ON foo
    FOR EACH ROW
    BEGIN
        IF NEW.ts <> OLD.ts THEN  
            INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b);
        END IF;
    END;
///

DELIMITER ;

select * from foo;
+------+------+---------------------+
| a    | b    | ts                  |
+------+------+---------------------+
|    1 |    1 | 2011-06-14 09:29:46 |
|    2 |    2 | 2011-06-14 09:29:46 |
|    3 |    3 | 2011-06-14 09:29:46 |
+------+------+---------------------+
3 rows in set (0.00 sec)

-- UPDATE without change
UPDATE foo SET b = 3 WHERE a = 3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

-- the timestamo didnt change
select * from foo WHERE a = 3;
+------+------+---------------------+
| a    | b    | ts                  |
+------+------+---------------------+
|    3 |    3 | 2011-06-14 09:29:46 |
+------+------+---------------------+
1 rows in set (0.00 sec)

-- the trigger didn't run
select * from bar;
Empty set (0.00 sec)

-- UPDATE with change
UPDATE foo SET b = 4 WHERE a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- the timestamp changed
select * from foo;
+------+------+---------------------+
| a    | b    | ts                  |
+------+------+---------------------+
|    1 |    1 | 2011-06-14 09:29:46 |
|    2 |    2 | 2011-06-14 09:29:46 |
|    3 |    4 | 2011-06-14 09:34:59 |
+------+------+---------------------+
3 rows in set (0.00 sec)

-- and the trigger ran
select * from bar;
+------+------+---------------------+
| a    | b    | ts                  |
+------+------+---------------------+
|    3 |    4 | 2011-06-14 09:34:59 |
+------+------+---------------------+
1 row in set (0.00 sec)

It is working because of mysql's behavior on handling timestamps.
The time stamp is only updated if a change occured in the updates.

Documentation is here:

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

desc foo;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| a     | int(11)   | YES  |     | NULL              |                             |
| b     | int(11)   | YES  |     | NULL              |                             |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

MySQL trigger with conditions on UPDATE

Try something like this:

CREATE TRIGGER Increment 
AFTER UPDATE ON Subscription
FOR EACH ROW
BEGIN
    IF NEW.State <> OLD.State AND NEW.STATE='C' THEN
        UPDATE USER
        SET num_sub=num_sub+1
        WHERE USER.id = NEW.user_id; -- change this condition as you need
    END IF;
END

This should also work:

CREATE TRIGGER Increment 
AFTER UPDATE ON Subscription
FOR EACH ROW
    UPDATE USER
    SET num_sub=num_sub+1
    WHERE USER.id = NEW.user_id
      AND NEW.State <> OLD.State
      AND NEW.STATE='C';

Note: NEW and OLD refer to the row from the Subscription table which is being updated. OLD contains the values before the update. NEW contains the values after the update. So to check if the state has been changed you can compare NEW.State <> OLD.State.

Trigger for rows where specific column was changed

You need to compare the inserted and deleted tables, joining by primary key, and verify the results are different.

Note that the UPDATE() function only tells you if the column was present in the UPDATE, not if the value actually changed.

CREATE TRIGGER testTrigger
ON table
AFTER UPDATE
AS

  SET NOCOUNT ON;

  IF(UPDATE(KeyProperty))
  BEGIN
    UPDATE t
    SET Flag = True
    FROM (
        SELECT i.Id, i.KeyProperty
        FROM inserted i
        EXCEPT
        SELECT d.Id, d.KeyProperty
        FROM deleted d
    ) i
    JOIN table t ON t.Id = i.Id
  END

GO

Find which columns have changed in MySQL triggers

It would be best to use a CDC tool like Debezium for this instead of triggers.

You can get a list of columns belonging to your Entity table by querying INFORMATION_SCHEMA.COLUMNS, but that doesn't help. It's not possible to generate dynamic statements in a trigger based on the columns, because MySQL doesn't support running dynamic SQL in a trigger.

If you must use triggers, then you just have to hard-code all 40 columns.

You could streamline it a little bit, by using the new feature in MySQL 8.0 feature to add a WHERE clause to a SELECT without a table reference. If the WHERE clause is false, the SELECT returns zero rows. Then you can string these together with UNION to make a set of rows, one for each column that changed.

BEGIN
    INSERT INTO ENTITY_AUD (baseTableId, beforeValue, afterValue, changedBy)
    SELECT OLD.id, OLD.quantity, NEW.quantity, NEW.updated_by
    WHERE OLD.quantity <> NEW.quantity
    UNION 
    SELECT OLD.id, OLD.price, NEW.price, NEW.updated_by
    WHERE OLD.price <> NEW.price
    UNION
    SELECT OLD.id, OLD.billing, NEW.billing, NEW.updated_by
    WHERE OLD.billing <> NEW.billing
    UNION
    ...37 others...
END

---

This question has come up numerous times in the past. Here are a few I found, but undoubtedly there are more.

• mysql Trigger for logging, find changed columns (2012)
• MySQL update trigger - find changed columns? (2013)
• Mysql triggers - capture each column change (2014)
• MYSQL update Trigger check changes in all columns and insert values to other table (2019)
• MySQL Trigger compare all columns in OLD and NEW (2021)

mysql trigger when update or insert - update field in affected row/rows

When you want to change a column of the row being inserted/updated, don't use UPDATE, just use SET NEW.status = ... That will change the value only on the current row that spawned the trigger.

Also you must qualify the other columns as NEW.stock and NEW.delivery_days.

BEGIN
  IF NEW.stock != OLD.stock or NEW.delivery_days != OLD.delivery_days
  THEN 
    SET NEW.status = CheckStatus(NEW.stock, NEW.delivery_days);
  END IF;
END

I agree with the comment above that you shouldn't name your table new, or any other SQL keyword or reserved word, because it will make your code confusing to anyone who reads it.

MySQL After Update TRIGGER with IF Conditional Statement

You don't need a query to get the status, just use new.status in the IF statement.

DELIMITER $$

CREATE TRIGGER book_returned AFTER UPDATE ON borrowed_books
FOR EACH ROW
IF new.status = 'returned'
THEN
    UPDATE books b
    SET b.availableCopies=b.availableCopies + New.copies 
    WHERE b.book_ID=new.book_ID;
END IF;
$$

DELIMITER ;

Create mysql trigger to update specific column after update row

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.

---

Related Topics