开发者

SQL Server: rollback after insert that contains an output inserted fails mysteriously

A rollback after an insert that contains an output statement fails with "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." If the output statement is removed, then it works. Is there an explanation for this behavior?

Example:

create table test(i integer primary key)
go
begin transaction
  insert into test (i) values (1)
  insert into test (i) output inserted.i values (1)
go
rollback -- Fails 
go

begin transaction
  insert into test (i) values (1)
  insert into test (i) values (1)
go
rollbac开发者_JS百科k -- Works
go


I don't know why this happens. It looks like SET XACT_ABORT ON is being set implicitly

As a workaround on SQL Server 2005 SP3 we can do this if it's blocking you

create table test(i integer primary key)
go
DECLARE @foo TABLE (i int)
begin TRANSACTION
  insert into test (i) values (1)
  insert into test (i) output inserted.i INTO @foo values (1)
GO
rollback --OK
GO

Edit: It could be that the OUTPUT clause is undefined


FYI this works fine in SQL 2008, so it must have been corrected at some point.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜