Discussion Locked

SQL Server Trigger Behavior and Rollback

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

When an insert operation occurs on a SQL Server table, an insert trigger is fired. Within the trigger a cursor is opened and data is retrieved row by row from the inserted table. The retrieved rows are permutated and inserted into a couple of other tables.

The question is: Should the global variable @@error be checked after each insert operation within the trigger, and if an insert operation within the trigger fails, should the trigger issue a rollback (if rollback is desired) or should the cursor be closed and simply return from the trigger?

In summary, does the insert error within the trigger automatically trickle up out of the trigger and rollback the transaction under which the insert trigger was fired, or does the trigger need to take some explicit action to initiate (or suppress) a rollback?

6 replies

by wjlarue

Reading your question several times, I identified three points that I?ll try to answer for you and hopefully this will give you the insight that your looking to achieve.

1. What happens when you use BEGIN TRANSACTION within a trigger?
2. What happens if you issue a ROLLBACK within a trigger?
3. What happens to the cursor?

Now, to answer question 1 we must remember that a trigger is already part of a transaction. If you, lets say, issue an INSERT into a table with a trigger, that trigger becomes part of the INSERT transaction, which means that if your trigger fails then it rolls up to the INSERT. This behavior is caused by SQL Server?s default transaction mode, which is autocommit. Normally, you can use BEGIN TRANSACTION to start an explicit transaction, but since a trigger always operates as if there were an outstanding transaction in effect, using BEGIN TRANSACTION actually starts a nested transaction.

This leads us to question number 2. A ROLLBACK TRANSACTION issued in the trigger always rolls back past any BEGIN TRANSACTION statements issued by the trigger itself and will roll back to the outermost BEGIN TRANSACTION (in our example it rolls up to the implied BEGIN TRANSACTION of the INSERT command). But some interesting points to note are: 1). All data modifications up to that point are rolled backed, 2). The trigger continues to execute any remaining statements and if any of the statements modify data, they are not rolled back. (But nested triggers will not fire), and 3). No statements after the trigger (the remainder of the batch) are executed.

(Continued in following comments)

by wjlarue

This comment continues the answer.

But what happens to the cursor? A ROLLBACK TRANSACTION in a trigger closes and de-allocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. As a note, cursors are governed by three rules when a ROLLBACK is issued:
1. With CURSOR_CLOSE_ON_COMMIT set ON, ROLLBACK closes but will not de-allocate anyopen cursors (this is in compliance with SQL-92).
2. With CURSOR_CLOSE_ON_COMMIT set OFF, ROLLBACK does not affect any open synchronous STATIC or INSENSITIVE cursors or asynchronous STATIC cursors that have been fully populated. Any other type of cursor is closed but not de-allocated (the default setting).
3. An error that terminates a batch and generates an internal rollback de-allocates all cursors that were declared in the batch containing the error statement. All cursors are de-allocatedregardless of their type or SQL setting. A deadlock error is an example of this type of error. A ROLLBACK statement issued in a trigger will generate this type of error (hence why it will close all cursors as stated above).

If you have further questions or comments on my answer, go ahead and post a comment to the answer (I believe we're allowed 7 comments per) and I will try to address them for you.

I hope this helps.

Jeff LaRue
Raging Technologies

by wjlarue

Sorry about the delay. For some reason I didn?t get a notice the question had been updated. Maybe the following information will help you determine if you need to check @@ERROR.

I have been frustrated with trigger behavior in past because I, likemany others, expected too much from them. If we examine the behavior within the context of data integrity the behavior does make sense. A real mess could be created if the trigger were to only complete some of its statements, the reason for the trigger being part of the originating transaction.

Now issuing a ROLLBACK TRANSACTION in a trigger does roll out of a trigger, but we can exercise some control of what happens by using SAVE TRANSACTION points:
(This is a tested and working example)
CREATE TRIGGER MyTestTrigger ON MyTable FOR UPDATE AS
INSERT INTO Test2Table
SELECT * FROM inserted
SAVE TRANSACTION MySavePoint
INSERT INTO Test3Table
SELECT * FROM inserted
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION MySavePointEND
INSERT INTO Test4Table
SELECT * FROM inserted

This example shows that if an error occurs on the INSERT into Test3Table fails the transaction rolls back to the save point and the INSERT into Test4Table is still completed. Just to note - Microsoft suggest using transaction savepoints in situations were errors are unlikely or infrequent.

Jeff LaRue
Raging Technologies

by bob_riedel

Do I need to even bother testing @@error after each insert?

It would appear from the last answer that if one of the insert operations within the trigger fails, that control is never returned to the trigger anyway, and that the rollback is automatic.

Suppose, however, that instead of an automatic rollback, the programmer wanted to control the conditions under which the rollback occured, or take some other action other than rollback? How much control can be excercised over the behavior of the trigger?

This discussion is closed to new replies.