Trigger Functions in SQL Server

By: Daniel Farina

Overview

In this chapter of the tutorial, I will show you the special functions that can be used inside triggers.

Special Functions for Triggers

Functions are meant to make coding easier. SQL Server includes a set of special functions that can only be used inside of triggers. Those functions allow us to get information that otherwise we wouldn't be able to get easily.

The UPDATE() Function for Triggers

Sometimes when working with triggers you may need to know which column was changed, either by an UPDATE or INSERT statement. The UPDATE() trigger function helps you identify modified columns. This function accepts only one parameter which is, of course the name of a column from the table or view associated with the trigger.

When using this function, a Boolean value (TRUE = 1; FALSE = 0) is returned as a result that can be used in conditional statements like IF or WHILE.

The next script will create the table that we will use to test this function.

CREATE TABLE TestColumns
(
   Column_1 INT PRIMARY KEY,
   Column_2 INT,
   Column_3 INT,
   Column_4 INT
)
GO

Let's create an AFTER UPDATE trigger that will show a message with the name of the column we are updating.

CREATE OR ALTER TRIGGER TR_TestColumns ON dbo.TestColumns
AFTER UPDATE 
AS
 
IF UPDATE(Column_2)
BEGIN
   PRINT 'Column_2 was updated'
END
 
IF UPDATE(Column_3)
BEGIN
   PRINT 'Column_3 was updated'
END
 
IF UPDATE(Column_4)
BEGIN
   PRINT 'Column_4 was updated'
END
GO

Now let's insert two rows.

INSERT INTO dbo.TestColumns (Column_1, Column_2, Column_3, Column_4)
VALUES (   1, 1, 1, 1  ), (   2, 2, 2, 2  )

Finally, the next script changes the value of Column_2 and Column_4 for one of the rows and Column_3 in the other row.

UPDATE dbo.TestColumns
SET Column_2 = 10, Column_4 = 11
WHERE Column_1 = 1;
GO
 
UPDATE dbo.TestColumns
SET Column_3 = 12
WHERE Column_1 = 2;
GO

The next image is a screen capture showing the execution of the updates on the previous script.

The COLUMNS_UPDATED() Function for Triggers

As we saw, the UPDATE() function for triggers is useful to know which columns were changed, but it falls short when you need to check for multiple columns. For that purpose the COLUMNS_UPDATED() function can be used. This function without parameters returns a VARBINARY stream that by using a bitmask allows you to test for multiple columns.

The size of this VARBINARY stream will depend on the number of columns present in the table or view. Since every column is represented by a bit, we can infer the size of the stream by dividing the number of columns by 8 and rounding up the result to the nearest integer. For example, if we have a table with 6 columns the COLUMNS_UPDATED() function will return a one byte sized VARBINARY stream, but if the table contains 20 columns the stream will be 3 bytes in size (20 divided by 8 equals 2.5 which we round up to 3).

The bytes of the VARBINARY stream are ordered from left to right, so if you have a table with 15 columns the first byte from the left will contain the bitmask value for columns 1 to 8 (the first eight columns). Meanwhile the second byte from the left will contain the bitmask value for columns 9 to 15.

Additionally, each byte of the VARBINARY stream is ordered from right to left, so the first bit from the right of the first byte starting from the left is the masked value for the first column. I know it sounds very confusing, so I created the graphic below to illustrate how each column in a table matches to the respective bit on the COLUMNS_UPDATED() function's bitmask for a two byte stream. The next image may help clarify this concept.

Now let's rewrite our previous trigger using COLUMNS_UPDATED() instead of the UPDATE() function. Notice that to check for columns updated we need to bitwise AND the VARBINARY stream returned by COLUMNS_UPDATED() function using a mask with the desired column.

 CREATE OR ALTER TRIGGER TR_TestColumns ON dbo.TestColumns
AFTER UPDATE 
AS
 
IF COLUMNS_UPDATED() & CAST(0x02 AS int) = 0x02
BEGIN
   PRINT 'Column_2 was updated'
END
 
IF COLUMNS_UPDATED() & CAST(0x04 AS int) = 0x04 
BEGIN
   PRINT 'Column_3 was updated'
END
 
IF COLUMNS_UPDATED() & CAST(0x08 AS int) = 0x08
BEGIN
   PRINT 'Column_4 was updated'
END
GO

The TRIGGER_NESTLEVEL() Function for Triggers

This function is used inside DML and DDL triggers to determine the level of nesting. It can be invoked without any parameter to obtain the nesting level relative to all the triggers in the nesting chain including itself (i.e. all the triggers in the call stack). If you specify the object id of a trigger, then this function will return the level of nesting of that particular trigger all of the three parameters accepted.

TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )

Below you will see a table with more description on each argument. Additionally the table describes each of the arguments of the TRIGGER_NESTLEVEL() function

Let's do some coding to see this function in action. For this purpose I will reuse the code from the chapter about nested triggers but with a few changes.

In the code section below you will see that inside the trigger TR_IUD_NestingTest I invoke the TRIGGER_NESTLEVEL function to obtain the nesting level of both TR_IUD_NestingTest and TR_IUD_NestingTestLog triggers, the number of AFTER triggers in the call stack and the trigger nest level.

ALTER TRIGGER [dbo].[TR_IUD_NestingTest] ON [dbo].[NestingTest]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @Operation VARCHAR(15)
PRINT 'BEGIN TR_IUD_NestingTest execution...'
IF EXISTS (SELECT 0 FROM inserted)
BEGIN
   IF EXISTS (SELECT 0 FROM deleted)
   BEGIN 
      SELECT @Operation = 'UPDATE'
   END ELSE
   BEGIN
      SELECT @Operation = 'INSERT'
   END
END ELSE 
BEGIN
   SELECT @Operation = 'DELETE'
END 
 
PRINT  'Nesting level for trigger TR_IUD_NestingTestLog: ' + CAST(TRIGGER_NESTLEVEL(OBJECT_ID('TR_IUD_NestingTestLog','TR' )) AS VARCHAR(2))
PRINT  'Nesting level for trigger TR_IUD_NestingTest: ' + CAST(TRIGGER_NESTLEVEL(OBJECT_ID('TR_IUD_NestingTest', 'TR'))  AS VARCHAR(2))
PRINT  'Number of AFTER Triggers on stack: ' + CAST(TRIGGER_NESTLEVEL(0,'AFTER','DML') AS VARCHAR(2))
 
PRINT  'Trigger Nest Level: ' + CAST(TRIGGER_NESTLEVEL()  AS VARCHAR(2))
INSERT INTO dbo.NestingTestLog
(
    NestingTestID,
    Test,
    Operation,
    OpDate
)
SELECT D.NestingTestID,
       D.Test,
       @Operation,
       GETDATE()
FROM Deleted D;
 
PRINT 'END TR_IUD_NestingTest execution...'
GO

Additionally I included the same TRIGGER_NESTLEVEL function calls in the code of TR_IUD_NestingTestLog trigger as you can see below.

ALTER TRIGGER [dbo].[TR_IUD_NestingTestLog] ON [dbo].[NestingTestLog]
FOR INSERT, UPDATE, DELETE
AS
 
PRINT 'BEGIN TR_IUD_NestingTestLog execution...'
 
PRINT  'Nesting level for trigger TR_IUD_NestingTestLog: ' + CAST(TRIGGER_NESTLEVEL(OBJECT_ID('TR_IUD_NestingTestLog','TR' )) AS VARCHAR(2))
PRINT  'Nesting level for trigger TR_IUD_NestingTest: ' + CAST(TRIGGER_NESTLEVEL(OBJECT_ID('TR_IUD_NestingTest', 'TR'))  AS VARCHAR(2))
PRINT  'Number of AFTER Triggers on stack: ' + CAST(TRIGGER_NESTLEVEL(0,'AFTER','DML') AS VARCHAR(2))
 
PRINT  'Trigger Nest Level: ' + CAST(TRIGGER_NESTLEVEL()  AS VARCHAR(2))
PRINT 'Here goes some processing stuff'
 
PRINT 'END TR_IUD_NestingTestLog execution...'
GO

Let's run the following statement to see how both triggers run.

INSERT INTO dbo.NestingTest(Test)
VALUES (2)

You can see the output of the triggers in the next screen capture.

The EVENTDATA() Function for Triggers

This function gives us information about the event that fired a DDL trigger. Of course, it only works when it's executed inside the context of a trigger, returning NULL otherwise. The return value is an XML stream, whose schema definition you can find in a file named events.xsd that comes with all versions of SQL Server and you can find in the following folder "C:\Program Files (x86)\Microsoft SQL Server\<VERSION>\Tools\Binn\schemas\sqlserver\2006\11\events" where <VERSION> means the SQL Server version number, like 150 for SQL Server 2019. Alternatively you can find it online at http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd.

In order to use this function you will need to search in the events.xsd file the definition of the event by looking for EVENT_INSTANCE_<event_type> type. To see the full list of event_type values take a look at this SQL Server's documentation page: https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events. For example, to extract the schema for the ALTER_TABLE event, search the schema for EVENT_INSTANCE_ALTER_TABLE. What you will see on the next image is a screen capture of the events.xsd file focused on the definition for the EVENT_INSTANCE_ALTER_TABLE type.

In the image you can see that the EVENT_INSTANCE_ALTER_TABLE type has a number of elements with its name and data type. Don't panic if you see types with strange names like SSWNAMEType, just search the events.xsd file for that type definition and you will see its basic data type, which in this case is a string with a maximum of 128 characters (remember that this is the definition of the SQL Server's sysname data type).

Note, we also saw an example on how to work with the EVENTDATA() function in the "SQL Server Database Trigger Example" section.

Additional Information

• How to find Updated Column in SQL Server Trigger
• SQL Server Trigger Columns Updated Function
• Script to check SQL Server connection pooling
• Using a DDL Trigger to Block Schema Changes in SQL Server
• Using the EventData() Function with DDL triggers in SQL Server 2005
• Keeping your SQL Server DDL Trigger Audit Table in Check