THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

What happens when you perform a rollback inside a trigger?

A coworker who is studying for the MCTS exam for 2005 brought this question up and wanted help understanding why the answers in the study materials were correct.  After a few minutes of discussion, I decided this was one of those topics that was best demonstrated completely rather than attempting to actually explain it, so I set about creating some demonstrations to help him see what actually would happen.  Without posting copyright materials directly, basically the answers choices were:

  1. Data changes made in the current transaction including those made in the trigger are rolled back.
  2. Data changes made in the current transaction except those made in the trigger are rolled back.
  3. Remaining statements in the batch after the one that fired the trigger are still executed.
  4. Remaining statements in the trigger after the rollback statement are still executed.
  5. Nested triggers located after the rollback statement will execute as normal.

Now the correct answers in the above are 1 and 4, but try to explain why to someone who has little experience with triggers, and you will begin to understand the situation.  So figuring it was easier to just demonstrate this, I went to creating some demo code:

CREATE TABLE triggertest
(rowid INT IDENTITY PRIMARY KEY, data VARCHAR(20), lastchanged datetime DEFAULT(GETDATE()), deleteflag bit NOT NULL DEFAULT(0))
INSERT INTO triggertest VALUES ('firstrow', DEFAULT, DEFAULT)
INSERT INTO triggertest VALUES ('secondrow', DEFAULT, DEFAULT)
INSERT INTO triggertest VALUES ('thirdrow', DEFAULT, DEFAULT)
GO

CREATE TABLE triggertest2
(rowid INT IDENTITY PRIMARY KEY, data VARCHAR(20), lastchanged datetime DEFAULT(GETDATE()), deleteflag bit NOT NULL DEFAULT(0))
INSERT INTO triggertest2 VALUES ('firstrow', DEFAULT, DEFAULT)
INSERT INTO triggertest2 VALUES ('secondrow', DEFAULT, DEFAULT)
INSERT INTO triggertest2 VALUES ('thirdrow', DEFAULT, DEFAULT)
GO

CREATE TABLE triggertestarchive
(rowid INT IDENTITY PRIMARY KEY, oldrowid INT, data VARCHAR(20), lastchanged datetime DEFAULT(GETDATE()), deleteflag bit NOT NULL DEFAULT(0))
GO

SELECT * FROM triggertest
SELECT * FROM triggertest2
SELECT * FROM triggertestarchive
GO

CREATE TRIGGER test_update
ON triggertest
FOR UPDATE
AS
BEGIN

DECLARE
@rows INT
SELECT
@rows = COUNT(*) FROM inserted
PRINT 'Rows before Rollback = ' + CAST(@rows AS VARCHAR)

UPDATE triggertest
SET lastchanged = GETDATE()
FROM triggertest
JOIN inserted ON triggertest.rowid = inserted.rowid

IF @@trancount > 0
BEGIN
   ROLLBACK
END

SELECT
@rows = COUNT(*) FROM inserted
PRINT 'Rows after Rollback = ' + CAST(@rows AS VARCHAR)

DELETE triggertest2
FROM triggertest2
JOIN inserted ON triggertest2.rowid = inserted.rowid

END
GO

CREATE TRIGGER test2_delete
ON triggertest2
FOR DELETE
AS 
BEGIN 

INSERT INTO
triggertestarchive
SELECT rowid, data, lastchanged, deleteflag
FROM deleted

END
GO

UPDATE triggertest
SET data = 'fourthrow'
WHERE rowid = 2
SELECT * FROM triggertest
GO

SELECT * FROM triggertest
SELECT * FROM triggertest2
SELECT * FROM triggertestarchive
GO

DROP TABLE triggertest
DROP TABLE triggertest2
DROP TABLE triggertestarchive

If you run the above code, the output from SQL Server will be:

Rows before Rollback = 1

Rows after Rollback = 0

Msg 3609, Level 16, State 1, Line 3
The transaction ended in the trigger. The batch has been aborted.

This essentially demonstrates the two correct answers from above, the SELECT COUNT(*) and PRINT statements after the rollback in the trigger continued to execute, but the modifications made before and inside of the trigger have been rolled back, which prevented the nested trigger from inserting the row into the triggertestarchive table.

Now for demonstration purposes earlier today, I ran the above code twice, once with the ROLLBACK commented out so that the execution completed normally, and then after dropping the tables, I reran it with the ROLLBACK in place to demonstrate how the ROLLBACK affects the changes being made and the firing of the nested trigger.

Hopefully this helps someone else in understanding this concept.

Published Thursday, April 30, 2009 10:51 PM by Jonathan Kehayias
Filed under:

Comments

 

Andy White said:

Jonathan,

Thanks for the post.

Is it possible for a Rollback in a trigger to only roll back the inner most transaction? A rollback in a trigger will roll back all the way to the outer most tran. I realize there are tran names and save points but I am looking for something more smarter.

like ROLLBACK @@innermosttran

Thanks!

May 20, 2009 11:31 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement