Blog Post

SQL Server Triggers and Transactions

,

In this post I’m going to detail with examples how triggers behave with transactions…

How Do Triggers Behave With Transactions?

To demo this let’s insert a record into a table that has an after insert trigger from a transaction and call rollback from the trigger. If you’ve read my nested transactions post you’ll know that any ROLLBACK nested or not will end all transactions and that nested transactions are really a lie.

CREATE TABLE TriggerTest
(
   Blah NVARCHAR(20)
)
GO
CREATE TRIGGER TestInsertTrigger ON TriggerTest AFTER INSERT
AS
ROLLBACK
GO

If we then run an insert into the table from a transaction and then call commit on the transaction will it be saved or will the trigger roll it back?

BEGIN TRAN
INSERT INTO TriggerTest VALUES('test')
COMMIT

SSMS Trigger Rollback Error

If we check our table you’ll see there were no results inserted….

SELECT * FROM TriggerTest

Empty Results

So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.

Do Errors in Triggers Prevent Data Being Saved?

What if instead of the ROLLBACK in our trigger we have a bug that causes an error…

ALTER TRIGGER TestInsertTrigger ON TriggerTest AFTER INSERT
AS
SELECT 1/0
GO

If we then run our insert again will it save?

BEGIN TRAN
INSERT INTO TriggerTest VALUES('test')
COMMIT

SSMS Trigger Dive By Zero Error

Empty Results

Nope again we didn’t make it to our commit and the transaction has been automatically rolled back.

How about if our insert wasn’t in an explicit transaction?

INSERT INTO TriggerTest VALUES('test')

Empty Results

Same error and still no records in TriggerTest table.

In short any rollback or run time error in a trigger will prevent the underlying data from being saved.

Do Triggers Fire at The End Of Transactions Or As Changes Are Made?

Given a transaction with multiple statements does the trigger fire as each statement is called or only when the transaction ends? Let’s check…

ALTER TRIGGER TestInsertTrigger ON TriggerTest AFTER INSERT
AS
PRINT 'IN TRIGGER'
GO
BEGIN TRAN
PRINT 'Before Inset 1'
INSERT INTO TriggerTest VALUES('One')
PRINT 'Before Insert 2'
INSERT INTO TriggerTest VALUES('Two')
COMMIT

Trigger Order Output

So, triggers fire on each statement as they are run inside the transaction.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating