Are Sql Triggers synchronous or asynchronous?

sql stored-procedures triggers timing
Miles picture Miles · Oct 16, 2008 · Viewed 13.4k times · Source

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?

Answer

tom.dietrich picture tom.dietrich · Oct 16, 2008

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.

Related questions

SQL Differences between stored procedure and triggers

I'm having trouble understanding the difference between a stored procedure and a trigger in sql. If someone could be kind enough to explain it to me that would be great. Thanks in advance

Read More
How To Create Generic SQL Server Stored Procedure To Perform Inserts Into Audit Table Based on Inserted and Deleted In Trigger

I have implemented an audit trail framework based on the information provided by the first answer to the following post: SQL Server history table - populate through SP or Trigger? Ultimately, the framework that I have implemented uses three triggers …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More