4

I was reading the examples on MSDN for TRY-CATCH blocks.

The code listing for example B is as follows:

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

What I don't understand is why they would bother to check the @@TRANCOUNT in the CATCH block. Is it really possible for the transaction count to be < 1 in this code snippet, or is the guard clause just an example of the principle that examples should be exemplary?

2 Answers 2

4

Is it really possible for the transaction count to be < 1 in this code snippet?

The table might have a DELETE trigger that rolls back the transaction and throws an error.

4
  • Yes, that is a possibility in practice, but if that information isn't in the topic then it's a stretch to assume that's why it was included. I think the defensive practice of being consistent and properly able to handle all potential outcomes consistently is more important than any specific technical reason. (But, to play devil's advocate, when it comes down to it, if the trigger already raised an exception, for the most part who cares if you (might) then get a second exception about rolling back a transaction that doesn't exist because it was already rolled back?) May 5, 2016 at 23:01
  • That's crafty. I don't interact with code with triggers very often so I forget about the side effects they can bring.
    – Erik
    May 5, 2016 at 23:02
  • 1
    @AaronBertrand I don't disagree. This example shows that the potential outcomes might not always be obvious though and so supports the practice. May 5, 2016 at 23:16
  • 2
    Certainly can't argue that. But I can also see that adding a check might be considered superfluous. I worked with a SQL developer several years back who wrote code like this: BEGIN TRANSACTION; IF @@ERROR <> 0 BEGIN ... do stuff and then GOTO ... END; IF @@TRANCOUNT <> 1 BEGIN ... do stuff and then GOTO ... END; That was awfully hard to read, when every single statement had error handling, full logging, etc. I would hope that if BEGIN TRANSACTION fails there is a more serious problem at play than typical (and expected) exceptions. May 5, 2016 at 23:18
4

Its a defensive programming practice to only do an action (rollback) if it's possible to do.

3
  • 1
    So you're saying that it can't happen in this example but it is a best practice so it was included in the example.
    – Erik
    May 5, 2016 at 22:50
  • 1
    @Erik Yes, I use the same strategy in all the code I write, in answers, in blog posts, in presentations, etc. Always use a semi-colon (even though it's not discontinued yet), always use YYYYMMDD (even when I know that my language settings etc. would allow mm/dd/yyyy), always prefix nvarchar string literals with N (even when I know the literals won't contain Unicode), always spell out day, minute, etc. even when I know the shorthands aren't confusing), always prefix CTEs with ; (even when I know the code will likely be copied with the previous, properly terminated statement), etc. May 5, 2016 at 22:59
  • 1
    And Erik, when @AaronB says "always" it's for good reason!
    – Hannah Vernon
    May 5, 2016 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.