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 Erland Sommarskog says are basically unfixed bugs). Syntax errors from dynamic SQL are also not caught and rolled back, 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
CATCH errors 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
ON by 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
ROLLBACK as shown.
For example, you can see in this fiddle that a second insert outside of the transaction is still committed, even though
BEGIN CATCH was used.
You only need to use
BEGIN CATCH and conditional
ROLLBACK; if handling the error.
SET XACT_ABORT must always be
ON if you have an explicit transaction, irrespective of whether there is a
CATCH, to ensure that rollback happens correctly.