Results 1 to 5 of 5

Thread: ROLLBACK tran?

  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: ROLLBACK tran?

    Let's say I have
    Code:
    	BEGIN TRAN
    
    	/* statement 1 */
    	INSERT INTO myTable ...
    	
    	SET @retval= @@IDENTITY
    
    	/* statement 2*/
    	INSERT INTO myOtherTable ...
    
    	IF @@ERROR <> 0
    		ROLLBACK TRAN
    
    	COMMIT TRAN
    My question: What is the point of having the "if @@error rollback tran" statement? Let's say "statement1" above fails; wouldn't it just generate a SqlException in my .NET layer, anyway, and not continue processing the rest of the sproc? This way I woudln't even need that "if @@error rollback" statement

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you using 2005? If so I would strongly recommend avoiding @@error and using TRY...CATCH.

    Now - I would suggest that this is the sort of question you can answer more quickly yourself than post up here....

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Okay, I looked into the docs, and I pretty much understand it all (concept is very similar to OOP style programming) However one thing I don't understand is below in red
    Code:
    USE AdventureWorks;
    GO
    BEGIN TRANSACTION;
    
    BEGIN TRY
        -- Generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() as ErrorState,
            ERROR_PROCEDURE() as ErrorProcedure,
            ERROR_LINE() as ErrorLine,
            ERROR_MESSAGE() as ErrorMessage;
    
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    
    END CATCH;
    
    
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;
    
    GO
    Why is it necessary to check @@TRANCOUNT? Why can't get just do "COMMIT TRAN" and "ROLLBACK TRAN" without checking the count?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your commit should most likely be in the TRY.

    Belt and braces. Check out XACT_STATE in help too.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Because your CATCH construct does not exit the execution. This means that you don't know whether it is successful or not after END CATCH. If you just COMMIT after END CATCH, and the exception actually occurred, you'll get the following error:
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

    However, if you re-write your IF @@TRANCOUNT construct inside the CATCH block like this one below, you can just put COMMIT:

    BEGIN CATCH
    ...
    IF @@TRANCOUNT > 0 begin
    ROLLBACK TRANSACTION
    return
    end
    END CATCH;
    COMMIT TRAN
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •