我想要回滚外部事务而不是提交（内部）事务 [英] I want Rollback Outer Transaction instead off commited(inner)Transaction

问题描述

当回滚事务时，我想继续提交 嵌套事务并回滚最外层事务。怎么办???



例如



尝试

交易A

插入....

插入。 ...



交易B

插入....

提交B



插入....

投稿

CATCH

退回A



这里每当发生异常然后我只想回滚一个不是B的交易。

解决方案感谢

When rollback the transaction , i want to keep committing the nested transaction and rollback only outer most transaction. How to do???

e.g.

TRY
Transaction A
Insert ....
Insert ....

Transaction B
Insert ....
Commit B

Insert ....
Commit
CATCH
Roll back A

here whenever exception is occurred then I want to rollback only A transaction not B.
Solution Appreciated

推荐答案

你可以使用保存点功能，试试可以捕捉..

U can use Save Point functionality given for try can catch..

SET NOCOUNT ON
 
BEGIN TRAN
PRINT 'First Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)
 
INSERT INTO People VALUES ('Tom')
 
SAVE TRAN Savepoint1
PRINT 'Second Transaction: ' + CONVERT(VARCHAR,@@TRANCOUNT)
 
INSERT INTO People VALUES ('Dick')
 
ROLLBACK TRAN Savepoint1
PRINT 'Rollback: ' + CONVERT(VARCHAR,@@TRANCOUNT)
 
COMMIT TRAN
PRINT 'Complete: ' + CONVERT(VARCHAR,@@TRANCOUNT)
 
/* MESSAGES
 
First Transaction: 1
Second Transaction: 1
Rollback: 1
Complete: 0
 
*/

您可以考虑在事务B上使用变量表，回滚不会影响变量表，类似的东西：



声明@mytable table（....）

开始转发

插入A1 ...

insert @mytable ...

rollback

select * from @mytable

You may consider using a variable table on transaction B, rollback will not affect variable table, something like that :

declare @mytable table(....)
begin tran
insert A1...
insert @mytable...
rollback
select * from @mytable

检查以下代码可能会对你有所帮助



例如

声明@ A1Error int

声明@ A2Error int



声明@ B1Error int

声明@ B2Error int



Tran A

插入A1

SET @ A1Error = @@ ERROR

插入A2

SET @ A2Error = @@错误



Tran B

插入B1

SET @ B1Error = @@ ERROR

插入B2

SET @ B2Error = @@ ERROR



- 提交或回滚sql事务B

IF @ B1Error<> 0 OR @ B2Error<> 0

ROLLBACK交易

ELSE

COMMIT TRANSACTION



提交或回滚sql事务A

IF @ A1Error<> 0 OR @ A2Error<> 0

ROLLBACK交易

ELSE

COMMIT TRANSACTION

Check with the below code may be this will help you

e.g.
Declare @A1Error int
Declare @A2Error int

Declare @B1Error int
Declare @B2Error int

Tran A
Insert A1
SET @A1Error = @@ERROR
Insert A2
SET @A2Error = @@ERROR

Tran B
Insert B1
SET @B1Error = @@ERROR
Insert B2
SET @B2Error = @@ERROR

--Commit or Rollback sql transaction B
IF @B1Error <> 0 OR @B2Error <>0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Commit or Rollback sql transaction A
IF @A1Error <> 0 OR @A2Error <>0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

这篇关于我想要回滚外部事务而不是提交（内部）事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！