ROLLBACK TRANSACTION

Last week (on Day 13), you learned how ROLLBACK TRANSACTION reverses any work since the last BEGIN TRAN statement. As you learned then, when SQL Server executes ROLLBACK TRANSACTION, it reverses all the work since the outermost BEGIN TRAN statement. If there was no explicit BEGIN TRAN statement, ROLLBACK TRANSACTION reverses all the work since the data modification statement started.

Let’s look at our trigger again:

create   trigger   tr3
on   t  for  insert,   update
as
if     update(c2)
begin
      print  “testing  c2”
    if  exists  (select  *  from  t  where  c2  <  0)
   begin
      raiserror (“c2 must be positive or zero”, 16, 10)
      rollback  transaction
      return
   end
end
return

You’ve already seen how ROLLBACK TRANSACTION works within the trigger. It reversed the transaction, backing out the invalid data. What if there is a larger transaction taking place? Consider this example, where three inserts are bundled into a transaction:

/*   careful  -  you  need  to  code  this  better  */
select   *  from   t
begin       transaction
     insert     t  (c2,  c3)  values  (101, “B1”)
     insert     t  (c2,  c3)  values  (-10, “B1”)
     insert     t  (c2,  c3) values   (103, “B1”)
commit      transaction
select  *  from   t

c1             c2              c3
-----------    -----------     ----
2              25              rstu
4              80             qqq
5              88             qqq
1              10             aaa
3              20             aaa
testing    c2
testing    c2
Msg   50000,  Level  16,  State  10
c2   must  be   positive  or  zero
testing   c2
Msg   3902,  Level  16,  State  1
The commit transaction request has no corresponding BEGIN TRANSACTION.
c1             c2          c3
-----------    -----------  ----
2               25          rstu
4               80          qqq
5               88          qqq
1               10          aaa
3               20          aaa
8               103         B1

Your intention in grouping these three inserts into a single transaction is to guarantee that all the “B1” rows will be inserted, or that none will be. Clearly, this failed to happen. One of the rows (the last row in the list) did make it into the table.

We’ll step through this closely. The trigger fires three times, once per INSERT statement (see the debugging output, testing c2). The second time, it finds an invalid row (c2 = -10), rolls back the transaction, and returns error message 50000.

It’s the ROLLBACK that causes a problem. On Day 13, you learned about @@trancount, the global variable that tracks your transaction nesting level. ROLLBACK statements set @@trancount to zero, ending the transaction, but processing did not stop. The next statement ran in its own transaction context because it was initiated with @@trancount at 0, and that’s the problem.

---

Note:  I am running version 6.5 of Microsoft SQL Server, with no service packs installed. At the time of this writing, two service packs have been released. I don’t believe the service packs address this problem.

The reason I mention my version is this: Previous versions of SQL Server treated a transaction rollback differently. In version 4.21, ROLLBACK TRANSACTION in a trigger immediately halted trigger processing as well as batch processing. The problem described here simply never came up because subsequent statements in the batch were not executed.

In version 6.0, the trigger continued processing after ROLLBACK TRANSACTION until it reached a RETURN statement, but processing in the batch was aborted after that return. Again, the problem we are describing did not arise because later statements weren’t executed.

With the release of 6.5, ROLLBACK TRANSACTION in a trigger does not cause the trigger or the batch to stop processing. That is left to you instead. Is this a bug that is corrected in a service pack? Or a new feature? Word on the newsgroups is that Microsoft considers this a bug, but I haven’t heard that yet from a Microsoft representative. Until that happens, your code needs to deal with this problem straight on.

---

Regardless of how errors are being handled, if you roll back from a trigger, you must raise an error before you return to the batch. Triggers do not raise an error automatically. If you don’t raise an error yourself, the user gets no warning that the execution failed. As far as he is concerned, the operation succeeded and the data modifications are in the table.

As with other potential errors, you need to check for trigger errors in your batch. Here’s the batch, corrected to check for an error after each data modification statement:

/*  careful  -  you  need  to  code  this  better  */
begin      transaction
     insert   t  (c2, c3)  values  (101,  “B1”)
    if @@error  <>  0  goto error_return
     insert   t  (c2, c3)  values  (-10, “B1”)
    if @@error  <>  0  goto error_return
     insert   t  (c2, c3)  values  (103, “B1”)
    if @@error  <>  0  goto error_return
commit    transaction
return
error_return:
if @@trancount  <>  0
            rollback   tran
return

After each statement, SQL Server checks for an error. If one is encountered, it checks to see whether a transaction is still current, rolling it back if necessary. (There are any number of errors that could occur without rolling back the transaction.)

This is just one possible way of coding the solution. (Many people avoid GOTO statements, but I consider this an appropriate use of GOTO.) Just make certain that your solution allows the transaction to handle errors that roll back the transaction as well as those that do not.