XACT_ABORT setting And Try..Catch

Let's see this example which inserts duplicate Primary key value to student table:

You will find two rows inserted into student table and the transaction does not rollback the code because by default, SQL Server does not roll back a transaction that has an error. If you want the transaction to either complete entirely or fail entirely, you can use XACT_ABORT setting on your connection, as follows:

The above one solution to solve your problem but it's not the proper solution so to provide a more structured way of handling errors that is very similar to the error handling routines of other programming languages, you can now use a TRY. . .CATCH block.

Within the CATCH block, you can commit or roll back the current transaction.

Note:

1. A RAISERROR executed in the TRY block immediately passes control to the CATCH block without returning an error message to the application.

2. A RAISERROR executed in the CATCH block closes the transaction and returns control to the calling application with the specified error message.


Let's see this example:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Last tip you can also use @@ERROR to handle the errors but this old way used with SQL Server 2000 and you can use it.