SQL Server Triggers are synchronous

 

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
GO

Use TriggerTest
GO
IF NOT EXISTS(select 1 from sys.tables where name = ‘Birthdays’)
CREATE TABLE Birthdays( [name] varchar(100), [birthdate] datetime)
GO

IF EXISTS(select 1 from sys.triggers where name = ‘sync_test’)
DROP TRIGGER sync_test
GO
/* Create trigger with begin tran, never commits */
CREATE TRIGGER sync_test ON dbo.Birthdays FOR INSERT
AS
BEGIN

–Wait for one minute
WAITFOR DELAY ’00:01′

END
GO

truncate table dbo.Birthdays

/* Insert into Birthdays hangs for one minute */
INSERT Birthdays
VALUES(‘Mike’, ‘1982-7-6’)

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s