How trigger will fire on multiple rows insert in single transaction

8
votes

I created a FOR INSERT trigger that will fire after inserting. Below are the different scenarios for which I would like to know how trigger will fire.

The trigger is on an employee table. I begin one transaction where I insert 4 rows. My doubt is how trigger will fire.

  1. Will it fire immediately after inserting each row?
  2. Will it fire after completion of all the rows in that transaction

We are able to access the inserted & deleted special tables from the trigger.

  1. How many rows will be present in those tables each time? Only one record? Or multiple records?

Thank you in advance.

1
sqlsql-servertriggers
Without seeing how you're performing the insert of 4 rows, it's not possible to say what the trigger will see. Is this 4 separate insert statements, or one insert statement inserting all 4 rows? - Damien_The_Unbeliever
I want to know in both scenarios. - Ramakrishna

1 Answers

14
votes

The trigger will fire after each INSERT statement.

If you have 4 INSERT statements, each of 1 row, the trigger will be fired 4 times with 1 record in the inserted special table after each insert.

If you have 1 INSERT statement of 4 rows, the trigger will be fired just 1 time with 4 records in the inserted special table after the insert.

If they are transactional inserts, the actions you perform in the trigger will be transactional too. This is very important if the trigger has an internal transaction.