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 changeUPDATE 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 changeselect * 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 runselect * from bar;Empty set (0.00 sec)-- UPDATE with changeUPDATE foo SET b = 4 WHERE a=3;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0-- the timestamp changedselect * 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 ranselect * 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 |+-------+-----------+------+-----+-------------------+-----------------------------+

BUT imagine a large table with changing columns. You have to compare every column and if the database changes you have to adjust the trigger. AND it doesn't "feel" good to compare every row hardcoded :)

Yeah, but that's the way to proceed.

As a side note, it's also good practice to pre-emptively check before updating:

UPDATE foo SET b = 3 WHERE a=3 and b <> 3;

In your example this would make it update (and thus overwrite) two rows instead of three.

I cant comment, so just beware, that if your column supports NULL values, OLD.x<>NEW.x isnt enough, because

SELECT IF(1<>NULL,1,0)

returns 0 as same as

NULL<>NULL 1<>NULL 0<>NULL 'AAA'<>NULL

So it will not track changes FROM and TO NULL

The correct way in this scenario is

((OLD.x IS NULL AND NEW.x IS NOT NULL) OR (OLD.x IS NOT NULL AND NEW.x IS NULL) OR (OLD.x<>NEW.x))