Does BEFORE INSERT trigger get executed for every

问题:

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 (...) ON DUPLICATE KEY UPDATE ......

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 ;

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.

回答1:

Does mysql invoke AFTER INSERT trigger only in case of inserts or everytime an insert or update happens

Right from the manual:

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.

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.