Knowledge Base

FIX: Rollback in Trigger Does Not Terminate Batch

Article ID: 154002

Article Last Modified on 3/16/2005

APPLIES TO

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q154002

SYMPTOMS

If a ROLLBACK TRAN statement executes within a trigger, the rest of the batch is not aborted.

WORKAROUND

To work around this problem, you can use the SET option XACT_ABORT to force the rest of the batch to abort when there is a rollback. Note, however, that this will abort the batch even for a rollback not happening within a trigger.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The problem happens only with the ROLLBACK TRAN statement. A rollback that happens automatically because an error with severity level 19 or greater arises will terminate the batch as expected.

The following script demonstrates the problem.

   create table t1(c1 int)
   go
   create trigger tr1 on t1 for insert as
   rollback tran
   go
   begin tran
   insert t1 values(100)
   print 'We should not get here...'
   if @@trancount <> 0
   commit tran
   go

Additional query words: sp1

Keywords: kbbug kbfix kbnetwork KB154002