Tag Archives: deleted

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