Are Sql Triggers synchronous or asynchronous?

Question

I have a table that has an insert trigger on it. If I insert in 6000 records into this table in one insert statement from a stored procedure, will the stored procedure return before the insert trigger completes?

Just to make sure that I'm thinking correctly, the trigger should only be called (i know 'called' isn't the right word) once because there was only 1 insert statement, right?

My main question is: will the sproc finish even if the trigger hasn't completed?

Solution 1

Your insert trigger will run once for the entire insert statement. This is why it is important to use the inserted temporary table to see what has actually been inserted, and not just select the most recent single record, or something like that.

I just tested an insert and update trigger and indeed, they are considered part of the insert by sql server. the process will not finish until the trigger finishes.

Solution 2

Triggers are part of the transaction that called them.

One important thing about triggers that you must be aware of is that the trigger fires once for each transaction (at least in SQL server, you should check other dbs, but even if it will process row by row, that is usually a poor idea), so if you insert 6000 records the trigger fires once not 6000 times. Many people are not aware of this and write triggers as if they will process multiple record inserts one record at a time. This is not true and your trigger must account for handing the multiple record insert.

Solution 3

The trigger call is not asynchronous. Each call to your insert procedure will result in the trigger being fired, and the procedure will not return until the trigger finishes.

Take a look at the query plan to see how it works. You'll see that the statements in the trigger will be called for each call to the procedure.

Solution 4

The thing is, every time the TRIGGER criteria is met, the TRIGGER fires. It fires once in batch processing or Transaction. See my lesson 101 on TRIGGER

View more solutions