What is the point of TRY CATCH block when XACT_ABORT is turned ON?
Code sample with XACT_ABORT_ON:
//do multiple lines of sql here
IF (@@TRANCOUNT > 0) ROLLBACK;
//may be print/log/throw error
Since XACT ABORT is ON, any error will automatically rollback the transaction. So what purpose does the TRY CATCH block serve?
You are right that it is not necessary to catch errors you are not intending on handling.
SET XACT_ABORT ON;ensures a rollback in all circumstances (except for a couple of very weird edge cases of uncatchable errors, which https://www.sommarskog.se/error_handling/Part2.html#uncatchable ). Syntax errors are also not caught, however that is just one more good reason to use a good IDE, proper version control and avoid dynamic SQL.
In my opinion, it is only necessary to
CATCHerrors if you intend on dealing with them. Erland's articles are generally misunderstood, they are intended for handling errors, not just catching and re-throwing.
SET XACT_ABORT ON;is always necessary, in order to correctly roll back transactions.
And in triggers, you must never explicitly roll back. If you do, you will get a spurious error # 3609
The transaction ended in the trigger. The batch has been aborted.And
ONby default in triggers.
But there are sometimes circumstances when you do actually want to catch and handle errors within your SQL code. For this you must use
BEGIN CATCH, and you also must use a conditional
For example, you can see https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=12bf2ce747394da1a15bd632759fae3c that a second insert outside of the transaction is still committed, even though
BEGIN CATCHwas used.
TL;DR; You only need to use
BEGIN CATCHand conditional
ROLLBACK;if handling the error.
SET XACT_ABORTmust be