How Can I Make a MySQL Trigger Execute Only When Data Actually Changes After an UPDATE?

MySQL UPDATE trigger: only executed when data changes

MySQL triggers allow you to automatically perform actions when specific database events (such as table updates) occur. However, sometimes you may want a trigger that only executes when the data in the updated row actually changes.

Question:

The built-in "AFTER UPDATE" trigger does not distinguish between updates that change data and updates that do not. This can lead to unnecessary trigger execution, especially when dealing with tables with many columns.

Solution:

While it is not possible to compare changes to all columns directly in a trigger, there is a workaround using timestamps. Whenever a row is modified, MySQL automatically updates the row's timestamp column. Therefore, you can use this timestamp to determine whether the data has changed.

Implementation:

1. Add timestamp column: Create a timestamp column in the table you want to trigger on.
2. Create a trigger: Create an "AFTER UPDATE" trigger that compares the old timestamp value to the new timestamp value. If they are different, perform the required action; otherwise, do nothing.

Here is an example:

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;

Advantages:

• Only executed when data changes.
• No custom code required to compare columns.
• Can be used with tables of any size and number of columns.

Note:

This solution relies on MySQL's automatic timestamp update behavior. If your application explicitly updates the timestamp, it may not work as expected.

The above is the detailed content of How Can I Make a MySQL Trigger Execute Only When Data Actually Changes After an UPDATE?. For more information, please follow other related articles on the PHP Chinese website!