仅当行已更改时，MySQL 才会在更新后触发
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6296313/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Trigger after update only if row has changed
提问by juwens
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".
是否有可能仅在数据已真正更改的情况下使用“更新后”触发器。我知道“新旧”。但是在使用它们时，我只能比较列。例如“NEW.count <> OLD.count”。
But I want something like: run trigger if "NEW <> OLD"
但我想要类似的东西：如果“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
如果现在.b <> 旧.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
行匹配：1 更改：0 警告：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.
MySQL 知道该行没有改变。但它不与触发器共享此知识。像“AFTER REAL UPDATE”之类的触发器或类似的东西会很酷。
回答by Inca
As a workaround, you could use the timestamp (old and new) for checking though, that one is notupdated 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.
由于 mysql 处理时间戳的行为，它正在工作。只有在更新中发生更改时才会更新时间戳。
Documentation is here:
https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
文档在这里：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 |
+-------+-----------+------+-----+-------------------+-----------------------------+
回答by Denis de Bernardy
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) tworows instead of three.
在您的示例中，这将使其更新（从而覆盖）两行而不是三行。
回答by Wax Cage
I cant comment, so just beware, that if your column supports NULL values, OLD.x<>NEW.x isnt enough, because
我无法评论，所以请注意，如果您的列支持 NULL 值，则 OLD.x<>NEW.x 还不够，因为
SELECT IF(1<>NULL,1,0)
SELECT IF(1<>NULL,1,0)
returns 0 as same as
返回 0 与
NULL<>NULL 1<>NULL 0<>NULL 'AAA'<>NULL
So it will not track changes FROM and TO NULL
所以它不会跟踪 FROM 和 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))
回答by user2428118
You can do this by comparing each field using the NULL-safe equals operator <=>and then negating the result using NOT.
您可以通过使用NULL 安全等于运算符<=>比较每个字段，然后使用 否定结果来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.)
回答by sumith madhushan
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 ;
回答by Hawthorne
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.
此查询的结果应包含与旧记录不同的所有新记录。
回答by Herwin Rey
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
