ID Number: Q78981
1.11
OS/2
buglist1.11
Summary:
Problem ID: PRSQL9112006
SYMPTOMS
When sending Transact-SQL statements within a batch, all statements
following an insert, update, or delete that causes a rollback are
ignored.
CAUSE
This SQL Server problem occurs when a trigger causes a rollback
within a Transact-SQL batch. If a statement within a batch causes a
rollback, and the entire batch is not part of a user-defined
transaction, all statements in the batch following the statement
that caused the rollback will be ignored. However, all statements
preceding the statement that caused the rollback will be committed
as expected.
WORKAROUND
The following are two methods to work around this problem:
1. Make the entire batch a user-defined transaction. In this case,
if a trigger forces a rollback to occur within the batch, the
entire batch will be rolled back. The batch could then be
modified and rerun. For example:
begin tran
insert into test_table values (12,"test",$35.38)
insert into test_table values (45,"hello",32.83)
insert into test_table values (35,"goodbye",83.38)
commit tran
go
2. Process each insert, update, or delete statement as an
individual batch. In this case, if a trigger forces a rollback,
only the statement that caused the rollback will be affected.
For example:
insert into test_table values (12,"test",$35.38)
go
insert into test_table values (45,"hello",$32.83)
go
insert into test_table values (35,"goodbye",$83.38)
go
STATUS
Microsoft has confirmed this to be a problem in SQL Server version
1.11. We are researching this problem and will post new information
here as it becomes available.