MySQL更新后的触发器，如果行已更改 [英] MySQL Trigger after update only if row has changed
问题描述
只有在数据已更改的情况下，才可能使用更新后触发器。
我知道NEW和OLD。但是当使用它们时，我只能比较列。
例如NEW.count<> OLD.count。
但我想要的是：run trigger ifNEW<> OLD
例如：
create table foo ）;
创建表格栏（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
更新后的foo
每个行
INSERT INTO bar VALUES（NEW.a，NEW.b）;
UPDATE foo SET b = 3 WHERE a = 3;
查询OK，0行受影响（0.00秒）
匹配行数：1更改：0警告：0
select * from bar;
+ ------ + ------ +
| a | b |
+ ------ + ------ +
| 3 | 3 |
+ ------ + ------ +
是，有一个更新，但没有更改。
但是触发器仍然运行。
我知道我可以使用
如果NOW.b
此示例。
但是假设一个包含更改列的大表。
您必须比较每个列，如果数据库更改，您必须调整触发器。
，并且没有感觉比较硬编码的行的每一列都很好）。
添加
正如你可以看到
行匹配：1更改：0警告：0
MySQL知道该行没有更改。但它不与触发器共享这个知识。
像AFTER REAL UPDATE这样的触发器或类似的东西会很酷。
可以使用时间戳（旧和新）来检查，当没有更改行时，不会更新。 （可能是混乱的原因，因为那个也被称为'on update'，但是没有发生改变时不执行）
一秒钟内的更改将不会执行触发器的那部分，但在某些情况下（例如，当您有一个应用程序拒绝快速更改时）。
例如，而不是
如果NEW.a<> OLD.a或NEW.b<> OLD.b / *等等，一直到NEW.z<> OLD.z * /
THEN
INSERT INTO bar（a，b）VALUES（NEW.a，NEW.b）;
END IF
您可以使用
如果NEW.ts<> OLD.ts
THEN
INSERT INTO bar（a，b）VALUES（NEW.a，NEW.b）;
END IF
然后你不必每次更新
（
create table foo（a INT，b INT，ts TIMESTAMP）;
创建表格栏（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更新后的foo
每个行
BEGIN
如果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行0.00 sec）
- UPDATE无变化
UPDATE foo SET b = 3 WHERE a = 3;
查询OK，0行受影响（0.00秒）
行匹配：1更改：0警告：0
- 时间戳没有更改
select * from foo WHERE a = 3;
+ ------ + ------ + --------------------- +
| a | b | ts |
+ ------ + ------ + --------------------- +
| 3 | 3 | 2011-06-14 09:29:46 |
+ ------ + ------ + --------------------- +
集合中的1行0.00秒）
- 触发器没有运行
select * from bar;
空集（0.00秒）
- 更改更新
UPDATE foo SET b = 4 WHERE a = 3;
查询OK，1行受影响（0.00秒）
行匹配：1更改：1警告：0
- 更改时间戳
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行0.00秒）
- 并且触发器运行
select * from bar;
+ ------ + ------ + --------------------- +
| a | b | ts |
+ ------ + ------ + --------------------- +
| 3 | 4 | 2011-06-14 09:34:59 |
+ ------ + ------ + --------------------- +
集合中的1行0.00 sec）
它是工作，因为mysql的行为处理时间戳。
只有在更新中发生更改时，才会更新时间戳。
文档位于此处：
https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization .html
desc foo;
+ ------- + ----------- + ------ + ----- + ------------ ------- + ----------------------------- +
|字段|类型| Null | Key |默认|额外|
+ ------- + ----------- + ------ + ----- + ------------ ------- + ----------------------------- +
| a | int（11）| YES | | NULL | |
| b | int（11）| YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP |更新CURRENT_TIMESTAMP |
+ ------- + ----------- + ------ + ----- + ------------ ------- + ----------------------------- +
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.
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 |
+-------+-----------+------+-----+-------------------+-----------------------------+
这篇关于MySQL更新后的触发器，如果行已更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
