Hugo Kornelis (4/19/2013)
Christian Buettner-167247 (4/19/2013)
Hi,There is one subtle issue with the explanation (and also the MS documentation).
It is not the ROLLBACK that is causing the abort of the batch. Instead, the batch is aborted if there is no transaction alive after the end of the trigger (at least that's what I can see from my tests).
For example, adding a BEGIN TRAN right after the ROLLBACK TRAN ensures that the batch is not aborted.
Yes, you are correct. The batch is aborted because no transaction was open when the trigger finished executing. If you change the ROLLBACK in the trigger to COMMIT (though why on earth you'd want to???), the batch still aborts.
More precisely, as you can see from Erland Sommerskag's article that Hugo linked in the explanation, the batch aborts when it exits a trigger context with @@TRANCOUNT = 0. ROLLBACK rolls back everything to the outer transaction, so @@TRANCOUNT = 0 after the ROLLBACK in the trigger executes. As Christian pointed out, a BEGIN TRAN at the end of the trigger will set @@TRANCOUNT = 1 so the batch won't abort.
There is another way to execute a ROLLBACK in a trigger while still exiting the trigger context with @@TRANCOUNT > 0 so that the rest of the batch doesn't about. That is to wrap the whole batch in a transaction, create a savepoint before the statement that fires the trigger and execute a ROLLBACK <savepoint> in the trigger. This is probably a matter of esoteric interest, though, since the need to use the same savepoint name as in the trigger in every bit of code that fires the trigger probably makes it impractical for operational use. This modification of Hugo's code demonstrates that it works, though:
-- NOTE - remove the lower case 'x' from each CREATE and DROP statement - my company blocks internet traffic that includes T-SQL DDL constructs, so I have to stick an extra letter in there to post code samples with DDL.
CxREATE TABLE dbo.Test
(PrimKey int NOT NULL,
ValueCol int NOT NULL,
PRIMARY KEY (PrimKey)
);
go
CxREATE TRIGGER TestTrig
ON dbo.Test
AFTER INSERT
AS
IF EXISTS(SELECT *
FROM inserted
WHERE ValueCol < 0)
BEGIN;
RAISERROR ('Negative values are not allowed!', 16, 1);
ROLLBACK TRAN SAVEPOINT;
END;
go
BEGIN TRANSACTION -- This is the "wrapper" transaction.
-- Insert attempt #1
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (1, 1)
-- Insert attempt #2
SAVE TRANSACTION SAVEPOINT -- The savepoint name must be the same as the name used in the ROLLBACK in the trigger.
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (2, -2)
-- Insert attempt #3
INSERT INTO dbo.Test (PrimKey, ValueCol)
VALUES (3, 3), (4, 4)
COMMIT -- Committing the "wrapper" transaction.
go
-- Predict the output of this query:
SELECT COUNT(*) FROM dbo.Test; -- Note that both Insert attempt #1 and Insert attempt #3 now execute,
-- while Insert #2 is rolled back and the error message returned.
go
DxROP TRIGGER TestTrig;
go
DxROP TABLE dbo.Test;
go
Jason Wolfkill