How SQL Triggers Work for Insert, Update, Delete and Truncate

Problem

I have been working with SQL Server triggers, but I am not sure how to tell what statement caused my trigger to execute so that I can determine what actions to take to make the necessary changes.

Solution

It is a well-known fact that triggers can be detrimental to performance of a SQL Server database and should be used sparingly. If a trigger is truly necessary then making it as efficient as possible is imperative. One way to be efficient is to skip sections of the trigger code if that code only needs to run when certain columns have been affected. It might also make sense to quickly exit the trigger using the RETURN command in the same situation.

Triggers can be set to run as a part of any combination of INSERT, UPDATE, and DELETE statements. Often the actions undertaken by the trigger only need to happen in certain scenarios where specific columns have been affected. This post will explain how to make such a determination by creating a table with a trigger and running through a series of scenarios.

This post assumes a basic understanding of what a trigger is and knowledge of the inserted and updated virtual tables. If those items are not yet understood, consider scrolling down to the next steps section of this post to find some links to trigger related posts that can be used to familiarize oneself with those concepts.

Using UPDATE and COLUMNS_UPDATED for SQL Server Triggers

SQL Server provides 2 functions to help determine which columns were involved in a statement that caused a trigger to fire. They are UPDATE and COLUMNS_UPDATED. UPDATE accepts a single parameter which is a column name for the underlying table. It will return a Boolean value depending on whether the column was updated or not. It can only check one column at a time, but can be called many times.

COLUMNS_UPDATED accepts no parameters and returns a binary value where each bit corresponds to a column in the table. A value of 1 indicates the column was affected and a value of 0 indicates that it was not affected. It can check many columns at once. A bitwise operator is generally used with COLUMNS_UPDATED.

Setup Environment to See When a SQL Server Trigger Fires

The scenarios depend on 2 tables. The following script will create the tables and trigger. Results will be where the actions of the trigger are tracked. The ThereIsATriggerOnThisTable will be the table with the trigger that is fired to test each scenario.

DROP TABLE IF EXISTS dbo.Results;
DROP TABLE IF EXISTS dbo.ThereIsATriggerOnThisTable;
 
CREATE TABLE dbo.Results (Results VARCHAR(4000));
CREATE TABLE dbo.ThereIsATriggerOnThisTable (
  col1 VARCHAR(100) PRIMARY KEY
, col2 VARCHAR(100) NOT NULL DEFAULT(NEWID())
, col3 VARCHAR(100) NULL);
GO
CREATE TRIGGER dbo.MyTrigger ON dbo.ThereIsATriggerOnThisTable AFTER INSERT,UPDATE,DELETE AS
BEGIN
  DECLARE @Message VARCHAR(4000);
  SET @Message = 'I am proof the trigger did execute for ' + CAST(@@ROWCOUNT AS VARCHAR(4)) + ' row(s).'
  INSERT INTO dbo.Results VALUES (@Message);
 
  IF UPDATE(col1) INSERT INTO dbo.Results VALUES ('UPDATED says I updated col1');
  IF UPDATE(col2) INSERT INTO dbo.Results VALUES ('UPDATED says I updated col2');  
  IF UPDATE(col3) INSERT INTO dbo.Results VALUES ('UPDATED says I updated col3');
 
       IF COLUMNS_UPDATED() & 7 = 7 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1, col2, AND col3');  
  ELSE IF COLUMNS_UPDATED() & 6 = 6 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col2 AND col3');
  ELSE IF COLUMNS_UPDATED() & 5 = 5 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1 AND col3');
  ELSE IF COLUMNS_UPDATED() & 4 = 4 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col3');
  ELSE IF COLUMNS_UPDATED() & 3 = 3 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1 AND col2');  
  ELSE IF COLUMNS_UPDATED() & 2 = 2 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col2');  
  ELSE IF COLUMNS_UPDATED() & 1 = 1 INSERT INTO dbo.Results VALUES ('COLUMNS_UPDATED says I updated col1');
 
END;

Scenario 1 – Test SQL Server Trigger for INSERT

This will insert 1 row into the target table. Only 1 column is referenced by the insert statement. Col2 will also have a value by virtue of its default. Col3 remains NULL.

-- clear out table
TRUNCATE TABLE dbo.Results;
-- issue an insert command
INSERT INTO dbo.ThereIsATriggerOnThisTable(col1) VALUES ('BEFORE');
-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

Perhaps surprisingly, both methods of testing showed all 3 columns as being updated even though only 1 was explicitly referenced by the statement and only 2 were populated with values. A trigger fired by an INSERT statement will always report that all columns were updated – even if they weren’t.

The results show that both UPDATED and COLUMNS_UPDATED report all 3 columns were updated. The row looks as expected with BEFORE, a GUID, and NULL values respectively.

Scenario 2 – Test SQL Server Trigger for UPDATE

This will update the 1 row in the target table. Only 1 column is referenced by the update statement.

-- clear out table
TRUNCATE TABLE dbo.Results;
-- issue an update command
UPDATE dbo.ThereIsATriggerOnThisTable SET col1 = 'AFTER';
-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

Nothing surprising here. Both methods accurately determined that only one column was part of the statement.

The results show that both UPDATED and COLUMNS_UPDATED report all that only col1 was updated.

Scenario 3 – Test SQL Server Trigger for UPDATE

This scenario will update the row again, but this time the statement references 2 columns. Also, both values are being set to the exact same value they already contain and won’t actually change them.

-- clear out table
TRUNCATE TABLE dbo.Results;
-- issue an update command
UPDATE dbo.ThereIsATriggerOnThisTable SET col1 = 'AFTER', col2 = col2;
-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

The important takeaway here is that even though nothing changed the trigger still executed and reported back that the columns were updated. This scenario is a common way for false positives to cause extra work in a trigger.

The results show that both UPDATED and COLUMNS_UPDATED report all that both col1 and col2 were updated even though both were technically unchanged.

Scenario 4 – Test SQL Server Trigger for UPDATE

This scenario will never update any rows as 1 can never equal 0.

-- clear out table
TRUNCATE TABLE dbo.Results;
-- issue an update command
UPDATE dbo.ThereIsATriggerOnThisTable SET col1 = 'AFTER' WHERE 1 = 0;
-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

Notice that even though 0 rows were affected the trigger still executed! This will hold true for an INSERT or DELETE statement also.

The results show that the trigger still executed even though no rows were affected by the statement. Also, both UPDATED and COLUMNS_UPDATED report all that only col1 was updated.

Scenario 5 – Test SQL Server Trigger for DELETE

This scenario will remove the row that is in the table.

-- clear out table
TRUNCATE TABLE dbo.Results;
-- issue a delete command
DELETE dbo.ThereIsATriggerOnThisTable;
-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

The trigger definitely executes, but any column checking returns false. This is the exact opposite of an INSERT where every column returns true. A trigger fired by a DELETE statement will always report that no columns were updated.

The trigger shows that it executed, but both methods of checking columns returned nothing.

Scenario 6 – Test SQL Server Trigger for TRUNCATE

This scenario will put the test row back then truncate the table rather than delete from it.

-- clear out table
TRUNCATE TABLE dbo.Results;
-- insert a record and then clear results table
INSERT INTO dbo.ThereIsATriggerOnThisTable(col1) VALUES ('BEFORE');
TRUNCATE TABLE dbo.Results;
-- issue a truncate command to remove the row we just inserted
TRUNCATE TABLE dbo.ThereIsATriggerOnThisTable;
-- see results
SELECT * FROM dbo.Results;
SELECT * FROM dbo.ThereIsATriggerOnThisTable;
GO

This scenario shows that truncate statements bypass trigger execution.

The row is gone from the target table, but the trigger did not execute at all as evidenced by the table dbo.Results being empty.

Dealing with false positives

These scenarios have shown that there are imperfections to both methods and can easily produce false positives. If false positives are a concern then the only fool-proof method to determine what was changed with an UPDATE statement would be to join the INSERTED and DELETED tables on the primary key (assuming that wasn’t changed) and compare the values of the column(s) in question.

Wrap up

Does the trigger need to continue to execute if there were no affected rows? If not, short circuit the trigger when @@ROWCOUNT = 0. Just be sure to do this first as @@ROWCOUNT will only return the number of rows affected by the calling statement as the first statement in the trigger. After that it will return the row count associated with the last statement executed within the trigger.

Does a section of the trigger, or even the entire trigger, only need to run when certain columns were affected? Use UPDATE, COLUMNS_UPDATED, or the INSERTED/DELETED tables to make that determination.

Next Steps

Here is some additional reading:

Resources

Community

Subscribe