The Hidden Effect of Rolling Back Transaction from Triggers

Yes, you can rollback a transaction from a trigger. But just because you can doesn”t mean you should. In the contrary – if you do not catch exceptions on the SQL Server side, using a ROLLBACK statement inside triggers put you in serious risk.

Understanding T-SQL Error Handling

If you asked me what part of SQL Server is the toughest to understand, I would say error handling. Things like memo (the structure used by the optimizer) or latches (some kind of light-weight locks) are way easier, because they applied to some rules.

But error handling seems to be totally messed up. For example, the first thing you probably learned about exception handling was that there are different types of errors:

1. With statement terminating errors execution resumes at the next statement.

2. When a scope terminating error occurs, execution resumes at the next statement after the one that called the procedure that was terminated.

Great, but why missing or superfluous parameter to stored procedure with parameters is a statement terminating errors, while passing any parameter to a parameterless stored procedure is a scope terminating one?

Errors and Transactions

To add more confusion, most errors are statement terminating errors, which means that a statement that fails even when enclosed in a transaction does not automatically rolled the transaction back, only the statement itself. Fortunately, XACT_ABORT session settings can be used to convert statement terminating errors into batch terminating errors. Those will abort the whole batch and roll back any transaction enclosing the statement that failed.

Now let me show you triggers can silently messed up your data.

Triggers abort the whole batch

To see why you must be extra careful when using a ROLLBACK statement inside triggers, create this simple table and an even simpler trigger:

USE tempdb;
GO

CREATE TABLE tab1 (col1 int);
GO

CREATE TRIGGER trT1
ON tab1
AFTER INSERT
AS
    IF EXISTS 
        (SELECT *
        FROM inserted
        WHERE col1=5)
    BEGIN
    RAISERROR (''Value 5 is not allowed'', 16, 1);
    ROLLBACK TRANSACTION;
    END
GO

 

First, we will try insert three rows into this table:

INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (5);
INSERT INTO tab1 VALUES (10);
GO

 

While the second row was inserted, the transaction ended in the trigger and the whole batch has been aborted. This is why only the first row is in the table:

SELECT * FROM tab1;
GO
------
col1
1

 

OK, maybe that should happened. So, let me truncate this table and we will go to a second part of this demo:

TRUNCATE TABLE tab1;
GO

 

Now, we will insert those three rows inside an explicitly started and committed transaction:

BEGIN TRAN
INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (5);
INSERT INTO tab1 VALUES (10);
COMMIT

 

Please note that the error message is exactly the same as previously (because the whole batch was aborted, the COMMIT statement was not executed, so there error 3902 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION was not thrown):

(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure trT1, Line 10
Value 5 is not allowed
Msg 3609, Level 16, State 1, Line 3
The transaction ended in the trigger. The batch has been aborted.

But this time no rows was inserted:

SELECT * FROM tab1;
GO
------

 

However, if all of those statement are executed in separate batches, only the two first inserts will be rollback, the last one will stay and the additional error 3609 will be thrown:

BEGIN TRAN
GO
INSERT INTO tab1 VALUES (1);
GO
INSERT INTO tab1 VALUES (5);
GO
INSERT INTO tab1 VALUES (10);
GO
COMMIT
------
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure trT1, Line 11
Value 5 is not allowed
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

(1 row(s) affected)
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

 

So, this way the last row will be inserted even if the whole transaction is rolled back:

SELECT * FROM tab1;
GO
------
col1
10

 

In other words, by executing exactly the same insert statements, we inserted the first row, then no rows and finally only the last row.

Lessons learned:

1. Statement level error (the most common ones) do not rollback transaction.

2. If you rollback a transaction from a trigger the whole batch will be automatically aborted. That is the case even if you do not throw any error from a trigger.

3. If your application sends queries in separate batches, the trigger effect on explicitly started transaction is questionable, to say the least.

Hope this help

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in General, SQL Server, T-SQL and tagged , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Comments are closed.