CODEWITHSUNDEEP
sqltransactionstry-catch
Jt2ouan
Jt2ouan

Reputation: 1962

Transactions and Rollbacks in SQL

I'm looking for some clarification in regards to Transactions against the DB.

I have a sproc which is called from a .NET application. The sproc is wrapped in a Try- Catch with a begin transaction and commit/rollback. Inside the try- catch is up to four possible other sprocs that are called which do the updates/inserts to tables. I believe those sprocs are also wrapped in try-catch with a begin transaction and commit/rollback.

If any one of those four sprocs catch an error on insert/update I want to rollback everything but since all the sprocs are wrapped individually in their own try-catch with a begin tran and commit/rollback tran wouldn't essentially once one sproc completes the call it is committed but if the second one fails the first one doesn't roll back. Is this the way it would work? Main sproc that is called looks something like this:

BEGIN TRY
BEGIN TRANSACTION


    EXEC dbo.test_call1 @AccountName

    EXEC dbo.test_call2 @accountNbr,@person

    EXEC dbo.test_call3 @person,@order

    EXEC dbo.test_call4 @order,@quantity



COMMIT TRANSACTION  
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

test_call1 looks similar to this:

BEGIN TRY
BEGIN TRANSACTION


    BEGIN
      INSERT INTO dbo.Account
       (AccountName,AccountNumber,Source)
       VALUES
       (@AccountName,123456,'online' )


        END
 COMMIT TRANSACTION 
 END TRY

BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH

END

Upvotes: 2

Views: 274

Answers (1)

Dave Zych
Dave Zych

Reputation: 21897

(Assuming this is SQL Server)

The inner transactions are essentially ignored and the action of the outer transaction is the result of the entire transaction. From MSDN:

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

Upvotes: 2

Related Questions