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  lt   OLD count    But I want something like  run trigger if  NEW  lt   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  lt   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

User · Answer

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

User · Answer

MYSQL TRIGGER BEFORE UPDATE IF OLD a lt  gt NEW b  USE  pdvsa ent aycg    DELIMITER     CREATE TRIGGER  cisterna BUPD  BEFORE UPDATE ON  cisterna  FOR EACH ROW  BEGIN  IF OLD id cisterna estado lt  gt NEW id cisterna estado OR OLD observacion cisterna estado lt  gt NEW observacion cisterna estado OR OLD fecha cisterna estado lt  gt 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

User · Answer

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  lt  gt  OLD a or NEW b  lt  gt  OLD b    etc  all the way to NEW z  lt  gt  OLD z     THEN     INSERT INTO bar  a  b  VALUES NEW a  NEW b    END IF   you could use  IF NEW ts  lt  gt  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  lt  gt  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    ------- ----------- ------ ----- ------------------- -----------------------------

User · Answer

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

User · Answer

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

User · Answer

I cant comment  so just beware  that if your column supports NULL values  OLD x lt  NEW x isnt enough  because  SELECT IF 1 lt  gt NULL 1 0   returns 0 as same as  NULL lt  gt NULL 1 lt  gt NULL 0 lt  gt NULL  AAA  lt  gt 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 lt  gt NEW x

User · Answer

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  lt  gt  3    In your example this would make it update  and thus overwrite  two rows instead of three

[mysql] MySQL Trigger after update only if row has changed

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to triggers