MySQL Trigger après une mise à jour seulement si la ligne a été modifiée

Question

Existe-t-il une possibilité d'utiliser un déclencheur "after update" uniquement dans le cas où les données ont été VRAIMENT modifiées. Je connais l'existence de "NEW et OLD". Mais lorsque je les utilise, je ne peux que comparer des colonnes. Par exemple "NEW.count <> OLD.count".

Mais je veux quelque chose comme : lancer le déclencheur si "NEW <> OLD"

Un exemple :

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

Le fait est qu'il y a eu une mise à jour, mais rien n'a changé . Mais la gâchette a quand même fonctionné. IMHO il devrait y avoir un moyen de ne pas le faire.

Je sais que j'aurais pu utiliser

SI NOW.b <> OLD.b

pour cet exemple.

MAIS imaginez un grand tableau dont les colonnes changent. Vous devez comparer chaque colonne et si la base de données change, vous devez ajuster le déclencheur. Et ce n'est pas très agréable de comparer chaque colonne de la ligne codée en dur :)

Addition

Comme vous pouvez le voir sur la ligne

Rangs assortis : 1 Modifié : 0 Avertissements : 0

MySQL sait que la ligne n'a pas changé. Mais il ne partage pas cette connaissance avec le déclencheur. Un déclencheur comme "AFTER REAL UPDATE" ou quelque chose comme ça serait cool.

Answer 1

Comme solution de rechange, vous pouvez utiliser l'horodatage (ancien et nouveau) pour la vérification, qui est le suivant pas mis à jour lorsqu'il n'y a pas de modification de la ligne. (C'est peut-être la source de la confusion ? Car celle-ci est également appelée "on update" mais n'est pas exécutée lorsqu'il n'y a pas de changement). Les modifications effectuées en moins d'une seconde n'exécuteront donc pas cette partie du déclencheur, mais dans certains cas, cela peut convenir (comme lorsque vous avez une application qui rejette de toute façon les modifications rapides).

Par exemple, au lieu de

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

vous pourriez utiliser

IF NEW.ts <> OLD.ts 
THEN  
  INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b) ;
END IF

Ainsi, vous n'avez pas à modifier votre déclencheur à chaque fois que vous mettez à jour le schéma (le problème que vous avez mentionné dans la question).

EDIT : Ajout d'un exemple complet

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)

Cela fonctionne grâce au comportement de mysql dans la gestion des horodatages. L'horodatage n'est mis à jour que si un changement est intervenu dans les mises à jour.

La documentation est ici :
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 |
+-------+-----------+------+-----+-------------------+-----------------------------+

Answer 2

MAIS imaginez un grand tableau avec des colonnes changeantes. Vous devez comparer chaque colonne et si la base de données change, vous devez ajuster le déclencheur. Et ce n'est pas très agréable de comparer chaque ligne codée en dur :)

Oui, mais c'est ainsi qu'il faut procéder.

Par ailleurs, il est bon de procéder à une vérification préventive avant d'effectuer une mise à jour :

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

Dans votre exemple, cela aurait pour effet de le mettre à jour (et ainsi écraser ) deux rangs au lieu de trois.

Answer 3

Je ne peux pas faire de commentaires, mais attention, si votre colonne supporte les valeurs NULL, OLD.x<>NEW.x n'est pas suffisant, car

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

renvoie 0 comme identique à

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

Il ne suivra donc pas les changements de FROM et TO NULL.

La méthode correcte dans ce scénario est

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

Answer 4

Vous pouvez le faire en comparant chaque champ à l'aide de la fonction Opérateur d'égalité sécurisé par les NULL <=> et ensuite en niant le résultat en utilisant NOT .

Le déclencheur complet deviendrait :

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 ;

(Sur la base d'un réponse différente de la mienne .)

Answer 5

Ici, si une ligne est affectée par une nouvelle insertion, elle sera mise à jour dans une autre table de la base de données.

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 ;