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:
- Data changes made in the current transaction including those made in the trigger are rolled back.
- Data changes made in the current transaction except those made in the trigger are rolled back.
- Remaining statements in the batch after the one that fired the trigger are still executed.
- Remaining statements in the trigger after the rollback statement are still executed.
- 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.