Click here to Skip to main content
11,567,814 members (39,875 online)
Rate this: bad
good
Please Sign up or sign in to vote.
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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
 
*/
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  

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

  Print Answers RSS


Advertise | Privacy | Mobile
Web04 | 2.8.150624.2 | Last Updated 27 Nov 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100