From the BlogSubscribe Now

Example of Transactions in SQL Server

Here is a quick example of a transaction in SQL Server. This is done so that if there is an error, it will undo any changes that it made.
You’ll notice that I’m setting XACT_ABORT to ‘ON’. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

There is also a Try-Catch block, when an error occurs within the try block, it will automatically move to the catch block and execute whatever is in that block. In this case, I’m checking the @@TRANCOUNT, which increments when you begin a transaction (signaling that a change is taking place). If the @@TRANCOUNT is greater than one, it means that the statement has at least tried to execute the statement in the TRY block, so I’m rolling back the transaction if this occurs.

Speak Your Mind


19 − 16 =