How Can I Prevent MySQL 'AFTER UPDATE' Triggers from Firing on Unchanged Rows?

Prevent MySQL triggers from executing on unmodified rows

MySQL triggers execute when a specific operation occurs on a table, such as an insert, update, or delete. However, by default, the "after update" trigger activates even if no changes have been made to the table rows.

Why does the "After Update" trigger execute even if there are no changes?

The normal way to detect changes in an updated row is to compare individual column values ​​between the "new" and "old" versions of the row. However, as the number of columns in the table increases, this approach becomes tedious and error-prone.

Solution: Leverage row timestamps to identify changes

A neat way to solve this problem is to use a row timestamp column. MySQL maintains two columns of timestamps for each row: creation timestamp and update timestamp. These timestamps are automatically updated every time a row is inserted or updated.

Trigger execution based on timestamp comparison

By comparing the new and old timestamps we can determine if changes were made to the row. The following trigger code illustrates this approach:

<code class="language-sql">CREATE TRIGGER ins_sum AFTER UPDATE ON foo
FOR EACH ROW
BEGIN
    IF NEW.ts <> OLD.ts THEN
        -- 已进行更改；执行触发器主体
    END IF;
END;</code>

In this trigger, "NEW.ts" and "OLD.ts" represent the new and old timestamp values ​​respectively. If the values ​​are different, a change occurred and the trigger body is executed.

Example

Consider the following example:

<code class="language-sql">CREATE TABLE foo (a INT, b INT, ts TIMESTAMP);

-- 插入一些行
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 ;

-- 不更改地更新
UPDATE foo SET b = 3 WHERE a = 3;

-- 更改地更新
UPDATE foo SET b = 4 WHERE a = 3;</code>

The trigger will not run after performing the first update (without making any changes). However, the second update changes the row and the trigger is executed, inserting the new row into the "bar" table.

This solution eliminates the need to explicitly compare individual column values ​​and ensures that the trigger is only executed when an actual change is made to the row.

The above is the detailed content of How Can I Prevent MySQL 'AFTER UPDATE' Triggers from Firing on Unchanged Rows?. For more information, please follow other related articles on the PHP Chinese website!