About XACT_ABORT()

What makes this difficult is that most errors do not stop processing , so when you have a group of modification statements running in a batch without any error handling, they keep running.

XACT_ABORT is a SET options that ends the batch on a transaction. The way this works is that when running, if an erroroccurs, the batch stops and the transaction is stopped. It is effective, but gives you no realcontrol over what happens in an error. Since the batch ends immediately, to know whatstatement you are executing you need to print messages constantly. Hence this method ismore used for system tasks, but it is an effective tool for dealing with rolling back atransaction on an error.

The SET XACT_ABORT option is set to OFF by default, therefore you must enable the option when you want to ensure that SQL Server rolls back a failed transaction.

Here is an example,

What's wrong with this?

Here we should add SET XACT_ABORT() ON after SET NOCOUNT ON. As we discussed earlier, SET XACT_ABORT() ON changes statement terminating errors into batch-terminating errors. It will cause the transaction to roll back after the error in the UPDATE statement.