Does a transaction require a try catch?

Question

I am a c# developer learning more TSQL. I wrote a script like this:

begin transaction
--Insert into several tables
end transaction

But I was told that was not a good idea and to use something like this:

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

I don't see why the second example is more correct. Would the first one not work the same way? It seems the first one would either update all tables, or not at all? I don't see why checking the @@TRANCOUNT is necessary before the commit.

Answer 1

Only Open a Transaction once you are inside the try block and just before the actual statement, And commit it straightaway, do not wait for your control to go to the end of the batch to commit your transactions.

Once you are in Try Block and you have opened a transaction, If something goes wrong The control will jump to CATCH block, Simply rollback your transaction there and do other error handling as required.

I have added a little check before actually rolling back the transaction checking for any open transaction using @@ROWCOUNT function, It doesnt really make much sence in this scenario. It is more useful when you are doing some validations checks in your try block before you open a transaction like checking param values and other stuff and raising error in try block if any of the validation checks fail, In that case control will jump to catch block without even opening a transaction there you can check for any open transaction and rollback if there are any open ones. In your case as it is, you really dont need to check for any open transaction as you will not entre the catch block unless something goes wrong inside your transaction.

BEGIN TRY

  BEGIN TRANSACTION 
     -- Multiple Inserts
    INSERT INTO....
    INSERT INTO.... 
    INSERT INTO.... 
 COMMIT TRANSACTION 
    PRINT 'Rows inserted successfully...'

END TRY

BEGIN CATCH 
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION 
      PRINT 'Error detected, all changes reversed'
   END 
    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
END CATCH