SQL SERVER - What is transaction and What type of block of code you will use control transaction error and T-SQL Error

Before read this article i would like to say it best  to know for any any sql server user specially for sql server dba and developer.

This is very important is learn how to working transaction and how to use try and catch block in sql server within the transaction and how to get the run time error.

1. What is Transaction?

A transaction is a batch of statements that are treated as a single event and Any changes made to the database by a transaction are guaranteed either to go to completion or to have no effect at all.

2. What type of block of code you will use control transaction error and T-SQL Error?

To control transaction errors I will prefer use of begin tran commit tran, and rollback tran method and to control the T-SQL error I will use try and catch method.

Example:-(Must run this example)

USE AdventureWorks;

GO

BEGIN TRANSACTION; -- opening new transaction here

BEGIN TRY  -- try stmt started
 -- we are here writing error code as Generate a constraint violation error.

    DELETE FROM Production.Product

    WHERE ProductID = 677;

END TRY

BEGIN CATCH -- catch stmt strated

    SELECT

         ERROR_NUMBER() AS ErrorNumber

        ,ERROR_SEVERITY() AS ErrorSeverity       

        ,ERROR_LINE() AS ErrorLine
        ,ERROR_STATE() AS ErrorState

  ,ERROR_PROCEDURE() AS ErrorProcedure

        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0

        ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0

    COMMIT TRANSACTION;

GO

OutPut:-