Click here to Skip to main content
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 4: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)



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 27 Nov 2013
Copyright © CodeProject, 1999-2014
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