Stop using ROLLBACK in triggers; THROW an error

A problem I've seen raised on numerous occasions is users that are getting a non-descript error when they are doing some kind of DML/DDL operation, and they don't know why they are getting the error, and what is causing it. That error is:

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

So, for the "unsuspecting" user that's performing an INSERT or maybe an ALTER statement, all they know is that the statement failed in a trigger. There are no details what what trigger caused the error, and no details of why that operation isn't allowed. This kind of error is generated whenROLLBACK is used inside a trigger and then the trigger exits; this means that the transaction is rolled back and therefore the outer scope can only tell you that the transasction has been aborted. This information is not helpful to your users, developers, and even your DBAs.

We can replicate this kind of behaviour with an overly simplified example. Let's take the below table, where there's a trigger that does not allow the column I to have a value of 0 (yes, this should be a CHECK CONSTRAINT in real life):

DROP TABLE IF EXISTS dbo.SomeTable;
GO
CREATE TABLE dbo.SomeTable (SomeID int IDENTITY (1,1),
                            I int NOT NULL);
GO

CREATE TRIGGER dbo.SomeTrigger ON dbo.SomeTable
AFTER INSERT, UPDATE AS
BEGIN
    IF EXISTS (SELECT 1
               FROM inserted i
               WHERE i.I = 0)
        ROLLBACK;
END;

Now we can do a few of test INSERTs into the new table:

INSERT INTO dbo.SomeTable (I)
VALUES(1);
GO
INSERT INTO dbo.SomeTable (I)
VALUES(0);
GO
INSERT INTO dbo.SomeTable (I)
VALUES(2),(0),(3);
GO
UPDATE dbo.SomeTable
SET I = I - 1;

This will result in the first query working, but the rest failing with the error at the start of this article. As a result, only the one row will be present in the table, with a value of 1 for I, and the user INSERT/UPDATEing the data has no idea why.

Instead, when you do need such logic to be inside a trigger, then you should be using THROW and provide a descriptive error. Also, ideally, document the error numbers that are generated so that your application developers know that if they get an error number like 78625, that it's a bespoke one instead a specific trigger, or you might use the same error number in multiple triggers that have the same logic so that yhour developers know it's the same problem across several areas. When you THROW an error, the aborting of the transaction is automatically handled in the outer scope; there is no need to ROLLBACK outside. You can also still using things like XACT_ABORT, with a transaction, to cause earlier successful tranasctions to rollback, and put thigns in a TRY...CATCH in case you need specific logic to certain failures.

I, personally, am quote verbose with my errors in triggers, when I have to use them. You don't need to be, but I often find denoting the trigger name in the error is extremely helpful. Something like this would be much better for you, and your other users:

CREATE OR ALTER TRIGGER dbo.SomeTrigger ON dbo.SomeTable
AFTER INSERT, UPDATE AS
BEGIN
    IF EXISTS (SELECT 1
               FROM inserted i
               WHERE i.I = 0) BEGIN
        DECLARE @TriggerName sysname,
                @TableName sysname;
        SELECT @TriggerName = tr.name,
               @TableName = t.name
        FROM sys.tables t
             JOIN sys.triggers tr ON t.object_id = tr.parent_id
        WHERE tr.object_id = @@PROCID;
        DECLARE @ErrorMessage nvarchar(2000) = FORMATMESSAGE(N'An error occurred in the trigger ''%s'' on the object ''%s''. The value of the column ''I'' cannot be 0.', @TriggerName, @TableName);

        THROW 78625, @ErrorMessage, 16;
    END;
END;

I also added a some logic to get the trigger and table's name and used FORMATMESSAGE to put them into the error. This just helps users know where the problem actually occurred and, if you do need to copy the logic elsewhere, it's transferable.

Now, if we try to INSERT/UPDATE a row so that I has the value 0 we get a meaningful error:

Msg 78625, Level 16, State 16, Procedure SomeTrigger, Line 17 [Batch Start Line 47]
An error occurred in the trigger 'SomeTrigger' on the object 'SomeTable'. The value of the column 'I' cannot be 0.

As I mentioned earlier, as well, this works without issues with other statements, and (importantly) things like XACT_ABORT. Let's take the following batches for example:

INSERT INTO dbo.SomeTable (I)
VALUES (4);

INSERT INTO dbo.SomeTable (I)
VALUES (0);

INSERT INTO dbo.SomeTable (I)
VALUES (5);
GO
SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT INTO dbo.SomeTable (I)
VALUES (6);

INSERT INTO dbo.SomeTable (I)
VALUES (0);

INSERT INTO dbo.SomeTable (I)
VALUES (7);
COMMIT;

After running the batches we have one additional row inserted, the row with a value of 4 for the column I. The INSERTs for the values 5 and 7 were never run, as the batch was aborted due to the THROW, and the value 6 was rolled back due to being inside a transaction and XACT_ABORT being enabled.

So, please, get into the habbit of THROWing your errors in your TRIGGERs, and make those errors meaningful. Your users, developers, DBA, and future you will be very grateful.