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?
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.
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.
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, 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 processs multiple record inserts one record at a time. This is not true and your trigger must account for handing the multiple record insert.
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