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.

Ashliashlie

In case of columns change, you'll need to adjust trigger anyway, coz it inserts with INSERT INTO bar VALUES(NEW.a, NEW.b);. Is there any solutions to avoid it? Will something like INSERT INTO bar VALUES(SELECT * FROM foo WHERE…); work? – Fsh

@ juwens, You deserve 10+ for this question. Incredible how they could have built the behaviour so counter-intuitively! – Mashe

@zcat, Nope, you do not necessarily need to adjust the trigger every time the table changes. You could do INSERT INTO bar, SELECT * FROM foo WHERE foo.id = OLD.id, and be fine. – Mashe

As a side note, Postgres behaves like this too, but you can put a WHEN NEW.* IS DISTINCT FROM OLD.* condition in the create trigger statement, so MySQL ought to implement something like that... – Schmidt

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

Annis

i don't see how this could work. Could you please explain in more detail what you mean by that. – Ashliashlie

@derkommissar: I added an example – Annis

Is there any reference link available – Venegas

This will not work if the update frequency is below a second. The timestamp will change (but to the same value). You have to use a timestamp(6) that should be accurate enough tracking all updates – Tunisia

As from my answer bellow, don't forget to use <=> (null aware operator) to track changes FROM null and back TO null – Yearling

Please implement @WaxCage's comment about the null aware operator in your example, as it may cause headaches to the unaware (me ;)) – Angelicangelica

this stopped working for me in the new mariadb. new.ts is always CURRENT_TIMESTAMP even with 0 changes. anyone have any idea how to change this? – Castled

Answer 2

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.

Lefthand

@Denis, this is not needed, MySQL checks to see if the value if really changed and only starts the UPDATE (update+trigger) if there is a need. Your check just makes things slower. – Grissom

@Johan: It is needed, and MySQL doesn't do that. It wouldn't respect the SQL standard if it did -- and the OP would not asking his question in the first place. – Lefthand

@Denis, Yep I checked with a test trigger of my own, in 5.0 and 5.5 and it does do that. How very annoying. – Grissom

It's not annoying, it's normal and actually desirable: sometimes, it's useful to have a trigger on update and to have it fire irrespective of whether changes occurred or not. The indirect consequence is it's up to the developer (or his ORM, even though the latter never do best I'm aware) to not update if no changes actually occurred. – Lefthand

@Yohan: you might find this discussion interesting. – Lefthand

@Johan: interesting... your removing your -1 vote revealed a bug in the SO code. I beat the 200-rep per day limit by two points, lol. :D – Lefthand

Maybe we can do a loop and get infinite rep :-). Should only take a few days and some wearing out of mice to beat Jon Skeet. – Grissom

The weird point is: MySQL seems to check if there's a difference. Because it prints the line "Rows matched: 1 Changed: 0". But it seems to ignore it. Thanks for the link. That's a neat feature of pgS. – Ashliashlie

Answer 3

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

Yearling

Thanks for your valuable answer/hint! This makes the timestamp-solution even more atractive and the compare-solution practically unusable. – Ashliashlie

Or you can use COALESCE() which returns the first of its arguments that is not NULL. So you could write it as IF COALESCE(OLD.X,'') <> COALESCE(NEW.X,'') – Howler

Or simply use mysql null aware comparison operator <=>. – Excerpta

Answer 4

You can do this by comparing each field using the NULL-safe equals operator <=> and then negating the result using NOT.

The complete trigger would become:

DROP TRIGGER IF EXISTS `my_trigger_name`;

DELIMITER $$

CREATE TRIGGER `my_trigger_name` AFTER UPDATE ON `my_table_name` FOR EACH ROW 
    BEGIN
        /*Add any fields you want to compare here*/
        IF !(OLD.a <=> NEW.a AND OLD.b <=> NEW.b) THEN
            INSERT INTO `my_other_table` (
                `a`,
                 `b`
            ) VALUES (
                NEW.`a`,
                NEW.`b`
            );
        END IF;
    END;$$

DELIMITER ;

(Based on a different answer of mine.)

Umberto

Answer 5

In here if there any row affect with new insertion Then it will update on different table in the database.

DELIMITER $$

CREATE TRIGGER "give trigger name" AFTER INSERT ON "table name" 
FOR EACH ROW
BEGIN
    INSERT INTO "give table name you want to add the new insertion on previously given table" (id,name,age) VALUES (10,"sumith",24);
END;
$$
DELIMITER ;

Picador

Answer 6

Use the following query to see which rows have changes:

(select * from inserted) except (select * from deleted)

The results of this query should consist of all the new records that are different from the old ones.

Pindling

Sadly this doesn't answer the question. Updates don't delete rows and the OP is trying to stop a trigger triggering if the update changed no data (updating a column with a value of 1 to a value of 1 changes no data, but the update operation still runs, so the trigger does too.) – Mysia

It was my understanding that an UPDATE query will put the OLD values in the deleted recordset and the NEW values in the inserted recordset. While this will not prevent the trigger from running, it can be used to prevent the trigger from taking any actions, which is usually adequate. – Pindling

it might be worth illustrating your answer with expanded code. However, keep in mind this question is also 5 years old. – Mysia

Here's a quote from Microsoft An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table." – Pindling

Sample code: CREATE TRIGGER ins_sum AFTER UPDATE ON foo INSERT INTO bar VALUES(select * from inserted except select * from deleted); – Pindling

Answer 7

MYSQL TRIGGER BEFORE UPDATE IF OLD.a<>NEW.b

USE `pdvsa_ent_aycg`;

DELIMITER $$

CREATE TRIGGER `cisterna_BUPD` BEFORE UPDATE ON `cisterna` FOR EACH ROW

BEGIN

IF OLD.id_cisterna_estado<>NEW.id_cisterna_estado OR OLD.observacion_cisterna_estado<>NEW.observacion_cisterna_estado OR OLD.fecha_cisterna_estado<>NEW.fecha_cisterna_estado

    THEN 

        INSERT INTO cisterna_estado_modificaciones(nro_cisterna_estado, id_cisterna_estado, observacion_cisterna_estado, fecha_cisterna_estado) values (NULL, OLD.id_cisterna_estado, OLD.observacion_cisterna_estado, OLD.fecha_cisterna_estado); 

    END IF;

END

Scenario

Answer 8

Here are two interesting dead ends (as of MySQL 5.7)-

1. The new.* and old.* constructs are invalid, MySQL complains about Unknown table 'new' or syntax to use near '*, which precludes tricks like select ... from (select (select new.* union select old.*)a having count(*)=2) has_change

2. The documentation for "ROW_COUNT()" has a useful clue-

For UPDATE statements, the affected-rows value by default is the number of rows actually changed

And indeed, after an update statement, ROW_COUNT() correctly shows the count of rows that had changes from the update. However, during the update, inside the trigger, ROW_COUNT() = 0 always. That function has no useful value in a row-level trigger, and there's no statement-level trigger in MySQL as of this answer.

Hope this "null result" prevents future frustration.

Damalas