How to correctly enforce referential integrity in a update/insert trigger?

Question

Database: SQL Server, 2017.

I need to enforce certain business rules in a trigger. Now, before you tell me to use a foreign key, please know that the rules are too complicated to be enforced by foreign keys. With that out of the way, let me get to the meat of my question:

Is this the correct way to rollback/cancel the insert/update in a trigger if a business rule is violated:

CREATE TRIGGER [dbo].[MyTestTrigger]
   ON  [dbo].[MyTestTable]
   AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF TRIGGER_NESTLEVEL(OBJECT_ID('[dbo].[MyTestTrigger]')) > 1
    BEGIN
        RETURN;
    END

    if (condition_is_true) begin
         ;throw 51000, 'Bad data Message.',1;  
    end
end

I know my question seems simple, but I stumbled on this "treatise" on error handling in SQL Server. After skimming through it, I came away with the impression that error handing is extremely complicated. One thing I am confused about is, do I need to explicitly call the rollback statement before/after the throw statement? Or will the throw itself rollback the update/insert. From my testing, the throw itself rolls back the update/insert. However, in Microsoft's own documentation on triggers, the rollback statement is included:

USE AdventureWorks2022;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  

Is the inclusion of rollback really necessary?

Answer 1

No, absolutely not.

Firstly, THROW always aborts the batch anyway, so the next statement is never executed, unless you have a BEGIN CATCH.

And XACT_ABORT is always ON in triggers, so a rollback happens anyway.

Even if you use RAISERROR which doesn't necessarily abort, if you ROLLBACK in a trigger then you get a spurious error:

Msg 3609 Level 16 State 1 Line 1
The transaction ended in the trigger. The batch has been aborted.

Some clients don't handle this properly, so you end up seeing only that error and not the actual error. So just use THROW by itself.

---

While Erland's "treatise" on error handling is excellent, it is widely misunderstood. It is about error handling ie dealing with an error, not throwing one.

In the majority of cases, you don't want to handle the error in SQL. You want the client to handle it, and for that you don't need any complex code, just THROW and XACT_ABORT ON.

Even BEGIN CATCH is poor: it doesn't catch all errors, and it can't handle multiple errors together as mentioned. If you need logging then use XEvents not some roll-your-own logger.

---

; is a statement terminator, not a beginning-ator. Terminate all your statements, or be terminated.