blocks|key|266901|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|266902|，但想象一下一个包含不断变化的列的大表。您必须比较每一列，如果数据库发生更改，您必须调整触发器。而且，比较每一行硬编码的代码也不是很“好”:)|blockquote|266903|266904|266905|是啊，但这才是继续下去的方式。|266906|顺便说一句，在更新之前进行先发制人的检查也是一种好的做法：|266907|UPDATE+foo+SET+b+=+3+WHERE+a=3+and+b+<>+3;|code-block|syntax|javascript|266908|在您的示例中，这将使它更新(并因此覆盖)两行，而不是三行。|offset|length|style|BOLD|266909|entityMap^0|0|0|0|0|0|0|0|H|2|0^^$0|@$1|2|3|-4|4|5|6|W|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|X|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|Y|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|Z|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|10|7|@]|8|@]|9|$]]|$1|H|3|I|4|5|6|11|7|@]|8|@]|9|$]]|$1|J|3|K|4|L|6|12|7|@]|8|@]|9|$M|N]]|$1|O|3|P|4|5|6|13|7|@$Q|14|R|15|S|T]]|8|@]|9|$]]|$1|U|3|-4|4|5|6|16|7|@]|8|@]|9|$]]]|V|$]]

<blockquote>
 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 :)
</blockquote>

Yeah, but that's the way to proceed.

As a side note, it's also good practice to pre-emptively check before updating:

<pre><code>UPDATE foo SET b = 3 WHERE a=3 and b &lt;&gt; 3;
</code></pre>

In your example this would make it update (and thus overwrite) two rows instead of three.

blocks|key|265524|text|作为一种解决办法，您可以使用时间戳(旧的和新的)来检查，当行没有更改时，时间戳不会更新。(这可能是造成混乱的原因？因为它也被称为“on+update”，但在没有发生更改时不会执行)，因此一秒内的更改将不会执行触发器的该部分，但在某些情况下，这可能会很好(例如，当您有一个应用程序拒绝快速更改时)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|265525|例如，而不是|265526|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|code-block|syntax|javascript|265527|你可以使用|265528|IF+NEW.ts+<>+OLD.ts+
THEN++
++INSERT+INTO+bar+(a,+b)+VALUES(NEW.a,+NEW.b)+;
END+IF|265529|这样，您就不必每次更新方案时都更改触发器(您在问题中提到的问题)。|265530|编辑:添加了完整的示例|offset|length|style|BOLD|265531|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;
%2B------%2B------%2B---------------------%2B
%7C+a++++%7C+b++++%7C+ts++++++++++++++++++%7C
%2B------%2B------%2B---------------------%2B
%7C++++1+%7C++++1+%7C+2011-06-14+09:29:46+%7C
%7C++++2+%7C++++2+%7C+2011-06-14+09:29:46+%7C
%7C++++3+%7C++++3+%7C+2011-06-14+09:29:46+%7C
%2B------%2B------%2B---------------------%2B
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;
%2B------%2B------%2B---------------------%2B
%7C+a++++%7C+b++++%7C+ts++++++++++++++++++%7C
%2B------%2B------%2B---------------------%2B
%7C++++3+%7C++++3+%7C+2011-06-14+09:29:46+%7C
%2B------%2B------%2B---------------------%2B
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;
%2B------%2B------%2B---------------------%2B
%7C+a++++%7C+b++++%7C+ts++++++++++++++++++%7C
%2B------%2B------%2B---------------------%2B
%7C++++1+%7C++++1+%7C+2011-06-14+09:29:46+%7C
%7C++++2+%7C++++2+%7C+2011-06-14+09:29:46+%7C
%7C++++3+%7C++++4+%7C+2011-06-14+09:34:59+%7C
%2B------%2B------%2B---------------------%2B
3+rows+in+set+(0.00+sec)

--+and+the+trigger+ran
select+*+from+bar;
%2B------%2B------%2B---------------------%2B
%7C+a++++%7C+b++++%7C+ts++++++++++++++++++%7C
%2B------%2B------%2B---------------------%2B
%7C++++3+%7C++++4+%7C+2011-06-14+09:34:59+%7C
%2B------%2B------%2B---------------------%2B
1+row+in+set+(0.00+sec)|265532|它之所以能够工作，是因为mysql在处理时间戳方面的行为。只有在更新中发生更改时，才会更新时间戳。|265533|文档在这里：|265534|https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html|265535|desc+foo;
%2B-------%2B-----------%2B------%2B-----%2B-------------------%2B-----------------------------%2B
%7C+Field+%7C+Type++++++%7C+Null+%7C+Key+%7C+Default+++++++++++%7C+Extra+++++++++++++++++++++++%7C
%2B-------%2B-----------%2B------%2B-----%2B-------------------%2B-----------------------------%2B
%7C+a+++++%7C+int(11)+++%7C+YES++%7C+++++%7C+NULL++++++++++++++%7C+++++++++++++++++++++++++++++%7C
%7C+b+++++%7C+int(11)+++%7C+YES++%7C+++++%7C+NULL++++++++++++++%7C+++++++++++++++++++++++++++++%7C
%7C+ts++++%7C+timestamp+%7C+NO+++%7C+++++%7C+CURRENT_TIMESTAMP+%7C+on+update+CURRENT_TIMESTAMP+%7C
%2B-------%2B-----------%2B------%2B-----%2B-------------------%2B-----------------------------%2B|265536|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|0|B|0|0|0|0|0|1X|0|0|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1C|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1D|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1E|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1F|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1G|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1H|8|@$Q|1I|R|1J|S|T]]|9|@]|A|$]]|$1|U|3|V|5|F|7|1K|8|@]|9|@]|A|$G|H]]|$1|W|3|X|5|6|7|1L|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1M|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1N|8|@]|9|@$Q|1O|R|1P|1|1Q]]|A|$]]|$1|12|3|13|5|F|7|1R|8|@]|9|@]|A|$G|H]]|$1|14|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|11]]]]

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

<pre><code>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
</code></pre>

you could use

<pre><code>IF NEW.ts &lt;&gt; OLD.ts 
THEN 
 INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b) ;
END IF
</code></pre>

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

<pre><code>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)
</code></pre>

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: 
<a href="https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html" rel="noreferrer">https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html</a>

<pre><code>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 |
+-------+-----------+------+-----+-------------------+-----------------------------+
</code></pre>

blocks|key|268755|text|我无法评论，所以请注意，如果您的列支持空值，则OLD.x<>NEW.x是不够的，因为|type|unstyled|depth|inlineStyleRanges|entityRanges|data|268756|SELECT+IF(1<>NULL,1,0)|offset|length|style|CODE|268757|返回0，与相同|268758|NULL<>NULL+1<>NULL+0<>NULL+'AAA'<>NULL|code-block|syntax|javascript|268759|因此它不会跟踪从NULL到NULL的更改|268760|在这个场景中，正确的方法是|268761|((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))|268762|entityMap^0|0|0|M|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|L|7|14|8|@]|9|@]|A|$M|N]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

I cant comment, so just beware, that if your column supports NULL values, OLD.x&lt;>NEW.x isnt enough, because

<code>SELECT IF(1&lt;&gt;NULL,1,0)</code>

returns 0 as same as

<pre><code>NULL&lt;&gt;NULL 1&lt;&gt;NULL 0&lt;&gt;NULL 'AAA'&lt;&gt;NULL
</code></pre>

So it will not track changes FROM and TO NULL

The correct way in this scenario is 

<pre><code>((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))
</code></pre>

blocks|key|2960280|text|您可以通过先使用NULL-safe+equals+operator+<=>然后使用negating+the+result+using+NOT比较每个字段来完成此操作。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2960281|完整的触发器将变成：|2960282|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+;|code-block|syntax|javascript|2960283|(基于different+answer+of+mine。)|2960284|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_equal-to|1|https://dev.mysql.com/doc/refman/5.7/en/logical-operators.html#operator_not|2|//stackoverflow.com/a/24041832/1419007^0|Y|3|1V|3|8|T|0|15|T|1|0|0|0|3|O|2|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@$9|15|A|16|1|17]|$9|18|A|19|1|1A]]|E|$]]|$1|F|3|G|5|6|7|1B|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1C|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1D|8|@]|D|@$9|1E|A|1F|1|1G]]|E|$]]|$1|O|3|-4|5|6|7|1H|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]|W|$5|R|S|T|E|$U|X]]|Y|$5|R|S|T|E|$U|Z]]]]

You can do this by comparing each field using the <a href="https://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_equal-to">NULL-safe equals operator <code>&lt;=&gt;</code></a> and then <a href="https://dev.mysql.com/doc/refman/5.7/en/logical-operators.html#operator_not">negating the result using <code>NOT</code></a>.

The complete trigger would become: 

<pre><code>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 &lt;=&gt; NEW.a AND OLD.b &lt;=&gt; NEW.b) THEN
 INSERT INTO `my_other_table` (
 `a`,
 `b`
 ) VALUES (
 NEW.`a`,
 NEW.`b`
 );
 END IF;
 END;$$

DELIMITER ;
</code></pre>

(Based on a <a href="//stackoverflow.com/a/24041832/1419007">different answer of mine</a>.)

blocks|key|268811|text|在这里，如果有任何行影响新插入，那么它将在数据库中的不同表上更新。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|268812|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+;|code-block|syntax|javascript|268813|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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 ;
</code></pre>

blocks|key|2960312|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2960313|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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
</code></pre>

blocks|key|3174191|text|使用以下查询查看哪些行发生了更改：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3174192|(select+*+from+inserted)+except+(select+*+from+deleted)|code-block|syntax|javascript|3174193|此查询的结果应包含与旧记录不同的所有新记录。|3174194|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Use the following query to see which rows have changes:

<pre><code>(select * from inserted) except (select * from deleted)
</code></pre>

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

blocks|key|2960403|text|这里有两个有趣的死胡同(从MySQL+5.7开始)-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2960404|2960405|+new.*和old.*结构无效，MySQL抱怨Unknown+table+'new'或syntax+to+use+near+'*，这排除了select等技巧...|ordered-list-item|offset|length|style|CODE|2960406|2960407|2960408|documentation+for+"ROW_COUNT()"有一个有用的线索-|unordered-list-item|2960409|2960410|对于UPDATE语句，|2960411|+-rows值缺省情况下是实际更改的行数|blockquote|2960412|2960413|2960414|实际上，在update语句之后，ROW_COUNT()会正确地显示update语句中发生更改的行数。但是，在更新期间，在触发器内部，ROW_COUNT()+=+0+always。该函数在行级触发器中没有有用的值，而且到目前为止，MySQL中也没有语句级触发器。|BOLD|2960415|希望这个“空结果”能防止未来的挫折。|2960416|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_row-count^0|0|0|1|5|7|5|O|J|18|L|1Y|6|0|0|0|0|V|0|0|0|0|0|0|0|1U|F|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1B|8|@$F|1C|G|1D|H|I]|$F|1E|G|1F|H|I]|$F|1G|G|1H|H|I]|$F|1I|G|1J|H|I]|$F|1K|G|1L|H|I]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|1O|8|@]|9|@$F|1P|G|1Q|1|1R]]|A|$]]|$1|O|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1T|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|1U|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1V|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1X|8|@$F|1Y|G|1Z|H|Y]]|9|@]|A|$]]|$1|Z|3|10|5|6|7|20|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|21|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18]]]]

Here are two interesting dead ends (as of MySQL 5.7)-
<ol>
<li>The <code>new.*</code> and <code>old.*</code> constructs are invalid, MySQL complains about <code>Unknown table 'new'</code> or <code>syntax to use near '*</code>, which precludes tricks like
<code>select</code> ... <code>from (select (select new.* union select old.*)a having count(*)=2) has_change</code>
</li>
<li>The <a href="https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_row-count" rel="nofollow noreferrer">documentation for &quot;ROW_COUNT()&quot;</a> has a useful clue-
</li>
</ol>
<blockquote>
For UPDATE statements, the affected-rows value by default is the number of rows actually changed
</blockquote>
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 &quot;null result&quot; prevents future frustration.

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:

<pre><code>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 |
+------+------+
</code></pre>

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 

<blockquote>
 IF NOW.b &lt;> OLD.b
</blockquote>

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 

<blockquote>
 Rows matched: 1 Changed: 0 Warnings: 0
</blockquote>

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 Trigger after update only if row has changed

数据库

MySQL

只有在数据确实被更改的情况下，才有可能使用"after update“触发器。我知道“新旧”。但在使用它们时，我只能比较列。例如"NEW.count <> OLD.count“。但是我想要这样的东西:如果"NEW <> OLD“就运行触发器举个例子：create table foo (a INT, b INT);cre...

问仅当行已更改时，MySQL触发器after update
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问仅当行已更改时，MySQL触发器after updateEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问仅当行已更改时，MySQL触发器after update
EN