MySQL Trigger after update only if row has changed

Question

Is there any possibility to use an "after update" trigger only in the case the data has been REALLY changed. I know of "NEW and OLD". But when using them I'm only able to compare columns. For example "NEW.count <> OLD.count".

But I want something like: run trigger if "NEW <> OLD"

An Example:

create table foo (a INT, b INT); create table bar (a INT, b INT);  INSERT INTO foo VALUES(1,1); INSERT INTO foo VALUES(2,2); INSERT INTO foo VALUES(3,3);  CREATE TRIGGER ins_sum     AFTER UPDATE ON foo     FOR EACH ROW     INSERT INTO bar VALUES(NEW.a, NEW.b);  UPDATE foo SET b = 3 WHERE a=3; Query OK, 0 rows affected (0.00 sec) Rows matched: 1  Changed: 0  Warnings: 0   select * from bar; +------+------+ | a    | b    | +------+------+ |    3 |    3 | +------+------+ 

The point is, there was an update, but nothing has changed. But the trigger ran anyway. IMHO there should be a way it doesn't.

I know that I could have used

IF NOW.b <> OLD.b

for this example.

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 column of the row hardcoded :)

Addition

As you can see on the line

Rows matched: 1 Changed: 0 Warnings: 0

MySQL knows that the line didn't change. But it doesn't share this knowledge with the trigger. A trigger like "AFTER REAL UPDATE" or something like this would be cool.

Answer 1

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 | +-------+-----------+------+-----+-------------------+-----------------------------+