In a previous post we discussed how to work with batches where we created a table named Accounting.BankAccounts and inserted two records. When the two records were inserted we received the following message.
This happened because while the two statements were in the same batch they were two separate auto commit transactions. However, what if we wanted to make sure that both statements either had to be successful or neither statement would commit to the database. An example of this would be if we needed to transfer $200 from the Jack account to the Diane account.
This code would run successfully and we would still receive the message the each statement was successful.
However, what if we made an error in one of the statements. For example, in the first update statement we accidentally typed 2/0 instead of 200 which would give a divide by zero run-time error.
Notice that the first update statement failed, but the second update statement still ran successfully. This would lead to inconsistent data in our tables where 200 was added to the Diane account without removing it from the Jack account. So to ensure that either both statements are successful or neither statements run, we will create an Explicit Transactions by using the Begin Transaction and Commit Transaction statements.
However, when we run this code we still get the same message.
This is because we would need to add error handling to capture the error and rollback the entire transaction. We introduce error handling by using the TRY/CATCH statements. We use the Begin Try and End Try statements around the code we would like to try and run. If there are not any errors the code will run successfully. If there are errors, our code will be sent immediately to the Begin Catch and End Catch block of code to be handled appropriately. Notice we have also indented the code to make it easier to see where the error handling and transaction statements end and begin. (For those familiar with .NET language, SQL does not include a FINALLY block with its error handling.)
When we TRY to run this code an error is caught and thrown into the catch section to be handled. In the CATCH section we used the Rollback Transaction statement to undo the entire transaction. The following message shows that not only did the first UPDATE statement not run, neither did the second statement in the transaction.
By using transactions we ensure that our data stays consistent. Another way of turning on transactions is by using the SET IMPLICT_TRANSACTION ON statement. This would implicitly turn on transactions as soon as the first statement runs without having to use the Begin Transaction statement. However, it will not finish until an explicit Commit Transaction or Rollback Transaction is reached. For better control and readability, it is still best practice to use Explicit Transactions with the Begin Transaction statement.
Finally, if we did not want to include the error handling but still use the functionality of the transactions. We could use the SET XACT_ABORT ON statement. This statement would terminate the transaction once the error was reached and cancel out the rest of the code. However, we would not be able to add any other error handling functionality without using the TRY/CATCH error handling blocks.