对于“在重复键更新上插入”中的每一行，是否在插入前触发器被执行？询问 [英] Does BEFORE INSERT trigger get executed for every row in "insert on duplicate key update" query

问题描述

这是如何工作的？如果要运行的查询如下，mysql仅在插入时或每次发生插入或更新时才调用INSERT INSERT触发器吗？

How does this work ? Does mysql invoke BEFORE INSERT trigger only in case of inserts or everytime an insert or update happens if the query to be run is as follows "

INSERT INTO tSomething VALUES（.. 。）关于重复键更新......

INSERT INTO tSomething VALUES (...) ON DUPLICATE KEY UPDATE ......

我想在插入行时根据其他col的值来更新列的值。但是，我没有想要这种行为进行更新。将始终执行的查询就是上面提到的查询。为此，我选择了INSERT AFTER。但是，我无法使用AFTER INSERT TRIGGER将col更新为

I want to update a column's value depending upon other col's values when inserting a row. However, I don't want this behaviour for updates. The query which will be executed always would be the one mentioned above. I chose INSERT AFTER for this. However, I can't update a col using AFTER INSERT TRIGGER as

SET NEW.score = NEW.score1 + NEW.score2;

SET NEW.score = NEW.score1 + NEW.score2 ;

我读到某个地方，在这种情况下，在INSERT触发器之前，该触发器将被触发

I read somewhere that BEFORE INSERT trigger in this case would allow triggers to be fired everytime when above query is executed. Is this true ? If yes, how do I solve this problem using a trigger.

推荐答案

mysql仅在插入或每次插入或更新happ时才调用AFTER INSERT触发器ens

右来自手册：

在插入前触发器将被激活每行，然后是AFTER INSERT触发器，还是BEFORE UPDATE和AFTER UPDATE触发器，具体取决于该行是否有重复的键

a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row

我不确定您到底要做什么。

I'm not sure what exactly you are after.

句子 但是，我不希望这种行为进行更新使我感到困惑。如果您只有AFTER INSERT触发器，则在运行UPDATE语句时将不会触发该触发器。而且，如果您的语句更新一行，它将触发UPDATE触发器，而不是INSERT触发器。

The sentence "However, I don't want this behaviour for updates" confuses me somehow. If the only thing you have is an AFTER INSERT trigger, then it will not be fired if you run an UPDATE statement. And if your statement updates a row, it will fire an UPDATE trigger, not your INSERT trigger.

这篇关于对于“在重复键更新上插入”中的每一行，是否在插入前触发器被执行？询问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！