CODEWITHSUNDEEP
mysqlsqltriggers
undefined
undefined

Reputation: 2101

AFTER INSERT trigger and INSERT IGNORE / ON DUPLICATE KEY update

Should the trigger (AFTER INSERT) be fired, if no rows were inserted?

The trigger is fired, even if no rows were inserted with INSERT IGNORE ... or INSERT ... ON DUPLICATE KEY UPDATE ....
Is this behavior normal?

Does the AFTER INSERT mean after a row is inserted, or after an INSERT query has been made?
Testing shows the second, but why?

Upvotes: 0

Views: 2037

Answers (1)

Rahul
Rahul

Reputation: 77896

Does the AFTER INSERT mean after a row is inserted, or after an INSERT query has been made?

Per MySQL Spec (specific excerpt)

Triger Syntax: trigger_time: { BEFORE | AFTER }

trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.

•INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.

With that, the trigger gets activated after the row has been inserted.

The behavior you ae seeing is as well commented on in MySQL document

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger activates 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.

Upvotes: 3

Related Questions