There was a discussion at my workplace about whether triggers in SQL Server are synchronous or asynchronous. I set up a test that shows that they are synchronous.
A WAITFOR DELAY inside of the trigger causes the original transaction to be delayed also.
IF NOT EXISTS(select 1 from sys.databases where name = ‘TriggerTest’)
CREATE DATABASE TriggerTest
IF NOT EXISTS(select 1 from sys.tables where name = ‘Birthdays’)
CREATE TABLE Birthdays( [name] varchar(100), [birthdate] datetime)
IF EXISTS(select 1 from sys.triggers where name = ‘sync_test’)
DROP TRIGGER sync_test
/* Create trigger with begin tran, never commits */
CREATE TRIGGER sync_test ON dbo.Birthdays FOR INSERT
–Wait for one minute
WAITFOR DELAY ’00:01′
truncate table dbo.Birthdays
/* Insert into Birthdays hangs for one minute */