Tag Archives: DML

The Trigger Fired?!

Data manipulation language (DML) triggers are commonly used to enforce business rules on underlying data, responding to a data change event by initiating a follow-on action, such as modifying additional data, generating a notification, or performing virtually any other action. While the basic trigger functionality is well known, there are a few interesting nuances regarding when a trigger fires. Let’s take a closer look.

To get started, let’s create a test table with a basic DML trigger and see how it works.

-- set the sql execution context
USE dev;
GO

-- suppress record count display
SET NOCOUNT ON;

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

-- create the test table
CREATE TABLE dbo.tbl (
    ID INT IDENTITY NOT NULL
    , Col1 CHAR(1) NULL);
GO

-- create a trigger on the test table
CREATE TRIGGER dbo.trgTbl ON dbo.tbl
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    PRINT 'The trigger fired';
END
GO

Simple enough. With the above trigger we can now see whenever a DML statement hits the table. Let’s try the CRUD (CReate, Update, Delete) statements and confirm everything is working.

-- insert to the test table
INSERT INTO dbo.tbl (Col1) VALUES ('a'), ('b');

-- update the test table
UPDATE dbo.tbl SET Col1 = 'c' WHERE Col1 = 'a';

-- delete from the test table
DELETE FROM dbo.tbl;

With each of those statements we are returned confirmation that the trigger did, in fact, fire. Nothing interesting yet. But, what if we execute a non-update? And we can test the non-update in two types: a logical non-update and a physical non-update, which I’ll label types “a” and “b”, respectively.

-- update the test table with logical non-update (type a)
UPDATE dbo.tbl SET Col1 = Col1;

-- update the test table with physical non-update (type b)
UPDATE dbo.tbl SET Value = 'c' WHERE 0 = 1;

The result of the first statement execution is not particularly surprising. Though logically the SQL is not doing anything meaningful, that the trigger fires is intuitive as there are records affected. But what about the physical non-update (type b)? For the second statement, the WHERE clause ensures that there are no records in the result set. That fact might imply that the trigger should not fire because no records could be affected. However, as you will have seen from executing the statement, that is not the case. The trigger still fires even though nothing could have changed in the underlying data.

So, what’s the take away? Well, now that we’ve seen a trigger fire without underlying DML, we can take a simple step to prevent it (if appropriate based on the functional use-case). For example, the following will do the trick.

-- alter the trigger to ignore type b non-updates
ALTER TRIGGER dbo.trgTbl ON dbo.tbl
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;
    PRINT 'The trigger fired';
END
GO

By first checking if there are records to be updated, we can choose to exit the trigger immediately before incurring any additional overhead from potentially complex or unwanted SQL execution.

Finally, be sure to keep your workplace tidy!

-- clean up
IF OBJECT_ID(N'dbo.tbl', N'U') IS NOT NULL DROP TABLE dbo.tbl;

Advertisements

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.