Tag Archives: trigger

Decoupling a Trigger

Table-level triggers are generally used to enforce business rules based on data manipulation language (DML) statements. Among the reasons they are so good at this function is that they are implicitly wrapped into the DML transaction. Simply put: if the trigger fails the DML fails.

In many cases, that is the desired behavior. However, what if you have an optional business rule? In other words, you’d like to take secondary action on certain data events, but, if the secondary action were to fail, you still want to keep the data change. A silly example of this could be if you wanted to send an email anytime someone named “Bob” registered at your website. While the email is desired, if it could not be sent for some reason, you’d still want “Bob” to be able to register. A trigger is a good candidate for this requirement because we want to be directly tied to the DML event of “Bob” registering… but we somehow need to decouple the trigger action from the DML transaction.

One way to do this is to use an execution context command that allows you to specify that an error should not abort the transaction. This setting is defaulted to “off” in normal query execution… but with triggers the default is “on”. Thus, simply setting the configuration within the trigger will do the job.

One caveat, however: even with the setting “off”, if an error within the trigger is sufficiently severe, the DML transaction will fail and rollback. Nonetheless, more innocuous errors can occur without causing DML failure.

Let’s take a look at this behavior in action.

-- if the example table exists, drop it
IF OBJECT_ID(N'tbl', N'U') IS NOT NULL DROP TABLE tbl;
GO

-- create the example table
CREATE TABLE tbl (
    ID INT IDENTITY(1,1) NOT NULL
    , Value VARCHAR(20) NOT NULL
    , CONSTRAINT PK_tbl PRIMARY KEY CLUSTERED (ID)
    );
GO

-- create a trigger on the example table
CREATE TRIGGER itrgTbl
ON tbl
FOR INSERT
AS
BEGIN
    -- turn off transaction aborting
    SET XACT_ABORT OFF;
    BEGIN TRY
        PRINT 'TRY';
        -- cause a failure
        DECLARE @i INT = 1/0;
    END TRY
    BEGIN CATCH
        PRINT 'CATCH!';
        -- capture the error values
        DECLARE
            @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
            , @ErrorSeverity INT = ERROR_SEVERITY()
            , @ErrorState INT = ERROR_STATE()
            ;
        -- raise the error
        IF @ErrorState > 0
            RAISERROR (
                @ErrorMessage
                , @ErrorSeverity
                , @ErrorState
                );
    END CATCH
END
GO

-- test the trigger by inserting a record
INSERT INTO tbl (Value) VALUES ('Hello, world.');

-- check the table contents to determine if the insert was successful
SELECT * FROM tbl;

When executing the code above you will see that the divide by zero error is raised, but the record was committed to the table anyway. Try commenting out the SET XACT_ABORT OFF command and running it again. Without that command the division error is escalated to a trigger execution error and the DML is blocked with message 3616:
Msg 3616, Level 16, State 1, Line 2
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

If you need to isolate your trigger code even more robustly from the DML event, you can also go fully asynchronous by using the trigger to send a message to the service broker. But more on that in a future post!

For more info on XACT_ABORT, read all about it on technet.