I want Rollback Outer Transaction instead off commited(inner)Transaction

Question

0.00/5 (No votes)

See more:

, +

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

Posted 5-Oct-12 3:28am

ravijain03

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aadhar Joshi · Answer 1 · 2012-10-18T02:30:00

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

SQL

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
 
*/

JL. ROBERT · Answer 2 · 2013-11-27T04:45:00

Solution 3

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

Posted 27-Nov-13 4:45am

JL. ROBERT

Sagar Khairnar 55 · Answer 3 · 2012-10-18T02:47:00

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

I want Rollback Outer Transaction instead off commited(inner)Transaction

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

I want Rollback Outer Transaction instead off commited(inner)Transaction

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us