Determining What Has Changed

for RuBoard

Inside a trigger, you can check which columns are being updated by a DML operation using the UPDATE() and COLUMNS_UPDATE() functions. The UPDATE() function returns TRUE or FALSE based on whether the value of a specified column is being set (regardless of whether it's actually changing). COLUMNS_UPDATED() returns a bitmap representing all the columns being set. Here's an example (Listing 8-1):

Listing 8-1 A trigger example that demonstrates UPDATE().

 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity,  Type int,  Onhand int ) GO CREATE TABLE ToyTypes (Type int identity,  MinOnhand int ) GO INSERT ToyTypes (MinOnhand) VALUES (10) INSERT ToyTypes (MinOnhand) VALUES (20) INSERT ToyTypes (MinOnhand) VALUES (15) INSERT ToyTypes (MinOnhand) VALUES (50) INSERT ToyInventory (Type, Onhand) VALUES (1, 50) INSERT ToyInventory (Type, Onhand) VALUES (2, 50) INSERT ToyInventory (Type, Onhand) VALUES (3, 50) INSERT ToyInventory (Type, Onhand) VALUES (4, 50) GO CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE AS DECLARE @rcnt int SET @rcnt=@@ROWCOUNT IF @rcnt=0 RETURN IF @rcnt > 1 BEGIN   RAISERROR('You may only change one item at a time',16,10)   ROLLBACK   RETURN END IF (UPDATE(Onhand)) BEGIN   IF EXISTS (SELECT * FROM ToyTypes t JOIN inserted i ON t.Type=i.Type       WHERE t.MinOnhand>i.Onhand) BEGIN        RAISERROR('You may not lower an item''s Onhand quantity below its Minimum Onhand quantity',16,10)     ROLLBACK     RETURN   END END GO UPDATE ToyInventory SET Onhand=49 WHERE Toy=4  -- Fails because of trigger GO DROP TABLE ToyInventory, ToyTypes GO 

This example not only shows how to use the UPDATE() function, it also shows how to code triggers so that they don't break when presented with multiple rows. In this case, we simply don't allow multiple row changes. We begin by checking @@ROWCOUNT to see if any rows have been changed at all. If none have, we return immediately.

Next, we check to see whether more than one row has been changed. If so, we display an error message, roll back the transaction, and exit. Notice that we store @@ROWCOUNT in a variable so that we can check it multiple times: once to see if any rows have been changed and once to see if too many have been changed.

If we get past both of these tests, we've got just one row to deal with. We begin validating the data modifications by seeing whether the Onhand column of the table has been changed. If it has, we check the MinOnhand column in the ToyTypes table to see whether the inventory is about to be reduced too far. If the current on-hand amount (as reported by the inserted table) is too low, we display an error message, roll back the transaction, and return. If not, we allow it to proceed.

We could just as easily have used the COLUMNS_UPDATED() function instead of UPDATE(). It has the advantage of being able to test for the modification of multiple columns at once. COLUMNS_UPDATED() returns a varbinary bitmap that indicates which columns have changed. Bits are ordered from left to right, with lower number columns being represented by the leftmost bits. Listing 8-2 presents the trigger rewritten to use COLUMNS_UPDATED():

Listing 8-2 A trigger example that demonstrates COLUMNS_UPDATED().

 CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE AS DECLARE @rcnt int SET @rcnt=@@ROWCOUNT IF @rcnt=0 RETURN IF @rcnt > 1 BEGIN   RAISERROR('You may only change one item at a time',16,10)   ROLLBACK   RETURN END IF ((  COLUMNS_UPDATED() & 4  )<>0) BEGIN   IF EXISTS (SELECT * FROM ToyTypes t JOIN inserted i ON t.Type=i.Type       WHERE t.MinOnhand>i.Onhand) BEGIN        RAISERROR('You may not lower an item''s Onhand quantity below its Mininum Onhand quantity',16,10)     ROLLBACK     RETURN   END END 

Notice the binary AND operation (signified by the & operator) that's used to determine whether the Onhand column is being modified. Onhand is the third column in the table, so we use a value of 4 (bits are numbered starting with 0, so the third bit corresponds to 2 raised to the power of 2, or 4; i.e., 2 = 1, 2 ¹ = 2, and 2 ² = 4). If we wanted to test for multiple columns, we could easily do that. For example, we could test whether the first and second columns were being changed by testing COLUMNS_UPDATED() against the value of 3 because 3 has its 0 and 1 bits turned on (2 = 1, 2 ¹ = 2, 1 + 2 = 3). Here's a version of the trigger that checks for changes to multiple columns (Listing 8-3):

Listing 8-3 You can check multiple columns with COLUMNS_UPDATED().

 CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE AS DECLARE @rcnt int SET @rcnt=@@ROWCOUNT IF @rcnt=0 RETURN IF @rcnt > 1 BEGIN   RAISERROR('You may only change one item at a time',16,10)   ROLLBACK   RETURN END -- Test for changes to columns 2 and 3 IF ((  COLUMNS_UPDATED() & 6  )<>0) BEGIN  -- Test for changes to columns 2 and 3   IF NOT EXISTS(SELECT * FROM ToyTypes t JOIN inserted i ON t.Type=i.Type) BEGIN     RAISERROR('Invalid Toy Type',16,10)     ROLLBACK     RETURN END   IF EXISTS (SELECT * FROM ToyTypes t JOIN inserted i ON t.Type=i.Type       WHERE t.MinOnhand>i.Onhand) BEGIN        RAISERROR('You may not lower an item''s Onhand quantity below its Mininum Onhand quantity',16,10)     ROLLBACK     RETURN   END END 

Here we test against a value of 6 because we want to know whether the second and third columns (the Type and Onhand columns respectively) have been changed. Because 2 ¹ = 2 and 2 ² = 4, we OR these values together to return an integer with its second and third bits (bit numbers 1 and 2) enabled.

In INSERT triggers, COLUMNS_UPDATED() indicates that all columns are being set because all columns either receive explicit values or implicit ones via default constraints and nullability. An example is presented in Listing 8-4:

Listing 8-4 INSERT operations enable all bits in COLUMNS_UPDATED().

 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity,  Type int NULL,  Onhand int DEFAULT 10 ) GO CREATE TRIGGER ToyInventory_INSERT ON ToyInventory AFTER INSERT AS IF @@ROWCOUNT=0 RETURN DECLARE @ChangedColumns varbinary(8000), @Size int, @i int SET @ChangedColumns=COLUMNS_UPDATED() SET @Size=DATALENGTH(@ChangedColumns)*8 SET @i=0 WHILE @i<@Size BEGIN   IF ((@ChangedColumns & POWER(2,@i))<>0)     PRINT 'Column '+CAST(@i AS varchar)+' changed'   SET @i=@i+1 END GO INSERT ToyInventory DEFAULT VALUES GO DROP TABLE ToyInventory GO 

(Results)

 Column 0 changed Column 1 changed Column 2 changed 

As you can see, even inserting DEFAULT VALUES into the table turns on the changed bit for all of its columns. Each of the three columns in ToyInventory has a different type of default value: Toy is an identity column, Type allows NULL values, and Onhand has a default constraint. As I said earlier, because each column in the table is required to receive a value of some type during an INSERT operation, COLUMNS_UPDATED() indicates that they've all been changed from within an INSERT trigger.

Note the loop we use to iterate through the changed column switches. It's worth discussing further. Here's the loop again:

 WHILE @i<@Size BEGIN   IF ((@ChangedColumns & POWER(2,@i))<>0)     PRINT 'Column '+CAST(@i AS varchar)+' changed'   SET @i=@i+1 END 

Because we previously saved the value of COLUMNS_UPDATED() in the @ChangedColumns variable, we can now loop through it, bit by bit, and determine which columns have changed. This technique would work in any trigger, not just INSERT triggers.

Note the use of POWER() to synthesize a binary value to check @ChangedColumns against. Because we're dealing with binary data and bit manipulation, we're obviously working with powers of 2. We begin by setting @Size to the actual number of bits returned by COLUMNS_UPDATED() (rounded up to the nearest byte), then we loop through these bits and check each one to see whether it has been set. For the ones that are enabled, we print a simple message.

What if we wanted to list the column names rather than just generic "Column n changed" messages? This wouldn't be terribly difficult. Here's the trigger modified to do just that (Listing 8-5):

Listing 8-5 It's easy to determine the names of the columns being changed.

 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity,  Type int NULL,  Onhand int DEFAULT 10 ) GO CREATE TRIGGER ToyInventory_INSERT ON ToyInventory AFTER INSERT AS IF @@ROWCOUNT=0 RETURN DECLARE @ChangedColumns varbinary(8000), @Size int, @i int, @colname sysname SET @ChangedColumns=COLUMNS_UPDATED() SET @Size=DATALENGTH(@ChangedColumns)*8 SET @i=0 WHILE @i<@Size BEGIN   IF ((@ChangedColumns & POWER(2,@i))<>0) BEGIN        SELECT @ColName=COLUMN_NAME FROM  INFORMATION_SCHEMA   .   COLUMNS  WHERE TABLE_NAME='ToyInventory' AND ORDINAL_POSITION-1=@i     PRINT 'Column '+@ColName+' changed'   END   SET @i=@i+1 END GO INSERT ToyInventory DEFAULT VALUES 

(Results)

 Column Toy changed Column Type changed Column Onhand changed 

Note the use of the INFORMATION_SCHEMA.COLUMNS view to retrieve each column name using its ordinal index. This is preferable to querying the syscolumns table directly, although you could retrieve the same information from it.

for RuBoard