Do I need to set XACT_ABORT to ON in every trigger?

This was an old SQL Server 2008 Express database (five of them actually) that I just migrated to SQL Server 2019 Express. Everything seemed to be working fine until my crew got in and we were getting an error everywhere. Turns out we had RAISEERROR in the triggers, and even though my compatibility appears to be set to 2008 (100), we were still getting the error. So I upgraded to THROW. Now everything appears to work fine, but as I’m not a DBA, I’m worried that my upgrade my corrupt some data or leave orphans. Here’s an example of one of the triggers:

USE [toddAPB]
GO
/****** Object:  Trigger [dbo].[T_tSaleLineItem_ITrig]    Script Date: 5/18/2021 1:32:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_tSaleLineItem_ITrig] ON [dbo].[tSaleLineItem] FOR INSERT AS
SET NOCOUNT ON
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tProduct' */
IF (SELECT COUNT(*) FROM inserted) !=
   (SELECT COUNT(*) FROM tProduct, inserted WHERE (tProduct.RecNumP = inserted.RecNumP))
    BEGIN
        ;THROW 44447, 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tProduct''.',1;
        ROLLBACK TRANSACTION
    END

/* * PREVENT INSERTS IF NO MATCHING KEY IN 'tSale' */
IF (SELECT COUNT(*) FROM inserted) !=
   (SELECT COUNT(*) FROM tSale, inserted WHERE (tSale.RecNumS = inserted.RecNumS))
    BEGIN
        ;THROW 44447, 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tSale''.',1;
        ROLLBACK TRANSACTION
    END

Do I need to put SET XACT_ABORT ON on every trigger (hundreds of them)? Should I? Do I still need ROLLBACK TRANSACTION after every THROW?

Before I adjusted to THROW, I was getting the “syntax error near 44447” error. the line previously looked more like this:

RAISERROR 44447 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tProduct''.'

Thanks for the help.

Answer

Well really you should be solving this issue with a Foreign Key Constraint (thanks Charlieface), however if thats not practical read on…

SET XACT_ABORT ON is the default for a trigger.

OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.

You should also be using proper joins rather than implicit joins. I have laid it out how I would write a trigger, with correctly terminated statements.

ALTER TRIGGER [dbo].[T_tSaleLineItem_ITrig]
ON [dbo].[tSaleLineItem]
FOR INSERT AS
BEGIN
    SET NOCOUNT ON;

    /* PREVENT INSERTS IF NO MATCHING KEY IN 'tProduct' */
    IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tProduct P INNER JOIN inserted I ON P.RecNumP = I.RecNumP)
    BEGIN
        THROW 44447, 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tProduct''.', 1;
    END;

    /* PREVENT INSERTS IF NO MATCHING KEY IN 'tSale' */
    IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tSale S INNER JOIN inserted I ON S.RecNumS = I.RecNumS)
    BEGIN
        THROW 44447, 'The record can''t be added or changed. Referential integrity rules require a related record in table ''tSale''.', 1;
    END;
END;

You could of course continue to use RAISERROR but it appears the syntax has changed since you originally wrote it – so you would have to correct that. Since you are having to modify them all anyway, moving to THROW seems appropriate.

I find your integrity check interesting, I would have written it as follows due to how I approach the logic, but I don’t think its any better.

IF EXISTS (
    SELECT 1
    FROM Inserted I
    WHERE NOT EXISTS (SELECT 1 FROM tProduct P WHERE P.RecNumP = I.RecNumP)
)