Problem
You have a requirement to identify which columns were changed in a SQL Server table after an UPDATE statement execution, regardless of where the UPDATE was done. You know you can use a trigger to identify when an UPDATE was done, but how can you identify which columns where updated. Keep reading this tip and you will find the answer.
Solution
In the case of SQL Server DML triggers there are two virtual tables during the execution of the trigger that holds the data being affected by the trigger execution. Those tables are named inserted and deleted and have the same table structure as their base table. Something to keep in mind is that the inserted and deleted tables are not always available together (i.e. you can have the inserted table, but not the deleted table or vice versa). You will find more information about these tables in the following tip.
Additionally, for the inserted and deleted tables with DML triggers there are two functions available that you can call inside the trigger code. These functions are UPDATE() and COLUMNS_UPDATED() which we will cover below.
SQL Server UPDATE() Function for Triggers
This function is available to use on triggers that fire in response to INSERT or UPDATE events. It receives a column name as a parameter, which of course must be a column present on the underlying table or view. This function returns TRUE or 1 in case the column given was updated, otherwise it returns FALSE or 0.
Let’s see an example. First you have to create the test table by running the script on the next code section.
CREATE TABLE TestColumns
(
Column_1 INT PRIMARY KEY,
Column_2 INT,
Column_3 INT,
Column_4 INT
)
GO
Additionally, we have to insert some test data. One single row is enough.
INSERT INTO dbo.TestColumns ( Column_1, Column_2, Column_3, Column_4 )
VALUES ( 0, 0, 0, 0 )
GO
Now it’s time to create an AFTER UPDATE trigger that will show a message with the name of the column we are updating.
CREATE TRIGGER TR_TestColumns ON dbo.TestColumns
AFTER UPDATE
AS
IF UPDATE(Column_1)
BEGIN
;THROW 51000, 'You can''t update the primary key', 1;
END
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
As you may see in the code above, there is one IF block for each column. Notice that I have also added an IF block for the Column_1 column which is the primary key of the table. What do you think will happen when we try to update that column? Wait and see.
To test the UPDATE() function let’s run the following UPDATE statements.
UPDATE dbo.TestColumns SET Column_1 = 2
WHERE Column_1 = 0
GO
UPDATE dbo.TestColumns SET Column_2 = 2
WHERE Column_1 = 0
GO
UPDATE dbo.TestColumns SET Column_3 = 2
WHERE Column_1 = 0
GO
UPDATE dbo.TestColumns SET Column_4 = 2
WHERE Column_1 = 0
GO
On the next screen capture below, we can see the results of running the queries.
As you can see, even when we tried to update Column_1 which is the primary key, our message was printed to the screen. This is not a bug; it is a marked as “by design” by Microsoft.
SQL Server COLUMNS_UPDATED() Function for Triggers
This function is used to know the inserted or updated columns of a table or view. It returns a VARBINARY stream that by using a bitmask allows you to test for multiple columns. The difference between the UPDATE() and COLUMNS_UPDATED() functions is that the UPDATE() function allows you to test for a single column meanwhile the COLUMNS_UPDATED() function let us test for updates on many columns.
The COLUMNS_UPDATED() function returns one or more bytes depending on the number of columns of the base table or view to conform a bitmask, where each bit represents a column; and is set to 1 if the column is being updated or inserted and 0 otherwise. As you may know, each byte has 8 bits so the length of the VARBINARY stream in bytes is the smallest integer greater than, or equal to the number of columns divided by 8. For example if you have a table with 25 columns and you want to know the length of the VARBINARY stream returned by the COLUMNS_UPDATED() function we divide 25 over 8 which is 3.125. Finally the smallest integer greater than or equal to 3.125 is 4.
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.
Now that we know how to read the output data of the COLUMNS_UPDATED() function let’s see a few examples.
Let’s change the trigger we created early to use the COLUMNS_UPDATED() instead of the UPDATE() function.
ALTER TRIGGER TR_TestColumns ON dbo.TestColumns
AFTER UPDATE
AS
IF COLUMNS_UPDATED() = 0x01
BEGIN
;THROW 51000, 'You can''t update the primary key', 1;
END
IF COLUMNS_UPDATED() = 0x02
BEGIN
PRINT 'Column_2 was updated'
END
IF COLUMNS_UPDATED() = 0x04
BEGIN
PRINT 'Column_3 was updated'
END
IF COLUMNS_UPDATED() = 0x08
BEGIN
PRINT 'Column_4 was updated'
END
GO
In the previous code you may see that I compare the COLUMNS_UPDATED() function to see if its result is equal to hexadecimal numbers 0x1, 0x02, 0x04 and 0x08. This numbers represents columns 1 to 4 in the bitmask. A very common mistake when people start using this function is that they confuse the hexadecimal number that turns on the column bit on the bitmask with the column number in hexadecimal. I created the next graphic to make this easier to understand.
Now let’s see how our testing UPDATE statements behave when we changed the UPDATE() with the COLUMNS_UPDATED() function.
UPDATE dbo.TestColumns SET Column_1 = 2
WHERE Column_1 = 0
GO
UPDATE dbo.TestColumns SET Column_2 = 2
WHERE Column_1 = 0
GO
UPDATE dbo.TestColumns SET Column_3 = 2
WHERE Column_1 = 0
GO
UPDATE dbo.TestColumns SET Column_4 = 2
WHERE Column_1 = 0
GO
The next screen capture shows the results of executing the updates above.
As you can see on the image above, the results obtained from running those UPDATE statements using the COLUMNS_UPDATED() function in the trigger’s code are the same as using the UPDATE() function, which is the expected behavior.
By now we have used the COLUMNS_UPDATED() function as if it were the UPDATE() function. In the next example we are going to check for updates on more than one column. To do so we are going to alter the trigger we created earlier. The next code will print a message if columns 2 and 4 are included in the updated columns and fail otherwise. You will see that we have to introduce the & bitwise operator (Bitwise Operators (Transact-SQL)) which is a logical AND to test for specific sets of columns in the bitmask.
ALTER TRIGGER TR_TestColumns ON dbo.TestColumns
AFTER UPDATE
AS
IF COLUMNS_UPDATED() & CAST(0x01 AS int) = 0x01
BEGIN
;THROW 51000, 'You can''t update the primary key', 1;
END
IF COLUMNS_UPDATED() & CAST(0x0A AS int) = 0x0A
BEGIN
PRINT 'Column_4 and Column_2 were updated'
END ELSE
BEGIN
;THROW 51000, 'You tried to update a forbidden column', 1;
END
In the previous code with the usage of the & operator I did a logical AND between the COLUMNS_UPDATED() function and a cast to integer expression over a hexadecimal number. This is because the bitwise operator when the left operand is of varbinary data type needs that the right operand to be of integer type. There is no need to use hexadecimal numbers in this case, but I decided to use them instead of decimal numbers because I think that it is easier and more intuitive when working with bitmasks.
On the next image there is a graphical representation of a byte with a mask on for columns 2 and 4. That mask represents the binary number 1010 which in decimal notation is 10, and in hexadecimal notation is A.
The next test script attempts to update our test table. First, we try to update only Column_2 which according to our trigger definition it will fail. Then we try to update Column_2, Column_3 and Column_4. Since our trigger requires that Column_2 and Column_4 be updated we can assume that this query will run successfully because there is no restriction in our trigger on updating Column_3. Finally, the last query attempts to update Column_2 and Column_4, which of course our trigger won’t deny.
PRINT 'UPDATE Column_2: '
UPDATE dbo.TestColumns
SET Column_2 = 2
WHERE Column_1 = 0
GO
PRINT 'UPDATE Column_2, Column_3 and Column_4: '
UPDATE dbo.TestColumns
SET Column_2 = 2, Column_3 = 2, Column_4 = 2
WHERE Column_1 = 0
GO
PRINT 'UPDATE Column_2, Column_3 and Column_4: '
UPDATE dbo.TestColumns
SET Column_2 = 2, Column_4 = 2
WHERE Column_1 = 0
GO
On the next screen capture we can see the results of executing the script above.
As a final example, let’s see an example on how to deal with a table that has more than eight columns. To do so, we have to drop and re-create our sample table with 14 columns as follows.
DROP TABLE TestColumns
CREATE TABLE TestColumns (
Column_1 INT PRIMARY KEY,
Column_2 INT NULL ,
Column_3 INT NULL ,
Column_4 INT NULL ,
Column_5 INT NULL ,
Column_6 INT NULL ,
Column_7 INT NULL ,
Column_8 INT NULL ,
Column_9 INT NULL ,
Column_10 INT NULL ,
Column_11 INT NULL ,
Column_12 INT NULL ,
Column_13 INT NULL ,
Column_14 INT NULL
)
GO
INSERT INTO dbo.TestColumns ( Column_1, Column_2, Column_3, Column_4, Column_5, Column_6, Column_7,
Column_8, Column_9, Column_10, Column_11, Column_12, Column_13, Column_14 )
VALUES ( 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0 )
The next trigger will print a message if columns Column_4 and Column_7 are updated and if columns Column_9 and Column_12 are updated.
CREATE TRIGGER TR_TestColumns ON dbo.TestColumns
AFTER UPDATE
AS
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & CAST( 0x48 AS INT) = 0x48
BEGIN
PRINT 'Column_4 and Column_7 were updated' ;
END
IF SUBSTRING(COLUMNS_UPDATED(),2,1) & CAST( 0x09 AS INT) = 0x09
BEGIN
PRINT 'Column_9 and Column_12 were updated' ;
END
GO
In the next image you will see the bitmask and its numeric value in both hexadecimal and decimal formats.
Let’s run the next two update statements and see what happens.
UPDATE dbo.TestColumns
SET Column_4 = 2, Column_7 = 2
WHERE Column_1 = 0
UPDATE dbo.TestColumns
SET Column_9 = 2, Column_12 = 2
WHERE Column_1 = 0
As you can see in the next screen capture the trigger worked as expected.
Next Steps
- If you wonder what else you can do with bitwise operators, then this tip is right for you: SQL Server Bitwise operators to store multiple values in one column.
- For an example of an INSTEAD OF trigger you can check out this tip: Using INSTEAD OF triggers in SQL Server for DML operations.
- In case you don’t know why I used a THROW statement in the trigger I suggest you to read the next tip: SQL Server THROW statement to raise an exception.
- You can see a simpler example of a trigger in the next tip: SQL Server Trigger Example.
- Take a look at my previous tip SQL Server Referential Integrity Across Databases Using Triggers.
- Stay tuned to SQL Server Triggers Tips Category for more tips and tricks.
