Identifying Modified Columns Puzzle

Your knowledge of bitwise operators can come in handy when you want to write a trigger to identify which columns an UPDATE statement modified. If you want to determine only whether a given column was updated, you can use the UPDATE() function. For example, if you want to see whether col3 was modified before you proceed with an activity, you simply use the following code:

IF UPDATE(col3)BEGIN  ...perform some activity...END

However, the UPDATE() function works on only one column. If you want to determine whether several columns were modified—say col3, col5, and col7—you can invoke the UPDATE() function several times:

IF UPDATE(col3) AND UPDATE(col5) AND UPDATE(col7)

Alternatively, you can use the COLUMNS_UPDATED() function. This function returns a varbinary value in which each bit represents a column; a bit is 1 if the column was modified and 0 if it wasn't. Note that the bytes in the return value of COLUMN_UPDATED() are organized from left to right: The leftmost byte represents the first 8 columns in the table, the second byte from the left represents the next 8 columns, and so on. To determine whether col3, col5, and col7 were updated, you need to extract the first byte from COLUMNS_UPDATED() and apply the AND operator to the first byte and an integer that has the third, fifth, and seventh bits turned on. You can use a bitwise OR operation to generate such an integer:

IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) & (4 | 16 | 64) = (4 | 16 | 64)

Determining whether col3, col6, and col11 were all updated is more difficult because these columns span multiple bytes. The third and sixth bits in the leftmost byte represent col3 and col6, and the third bit in the second byte from the left represents col11. In T-SQL, your IF statement would look like this:

IF SUBSTRING(COLUMNS_UPDATED(), 1, 1) & (4 | 32) = (4 | 32)-- third and sixth bits of first byteAND SUBSTRING(COLUMNS_UPDATED(), 2, 1) & 4 = 4-- third bit of second byte

Now that I've introduced the basics of bitwise operations and trigger functions, let's use the following puzzle to test your understanding. Say you need to write a trigger that prints a list of all the columns that were modified in a table. All you have is your knowledge of bitwise operators, the COLUMNS_UPDATED() function, and access to the system tables (preferably through the INFORMATION_SCHEMA views because Microsoft discourages direct system table access). In real life, you wouldn't print the list of columns but instead would act on the result; but printing the list is sufficient for this puzzle.

Run the code in Web Listing A to create a table called T1 that has 100 columns (key_col, col2, ... , col100), on which you can test your trigger. The code in Listing 2 uses a loop to construct and execute the CREATE TABLE statement dynamically, inserts a single row with a value of 1 into the column key_col, and inserts 0 in all other columns.

At this point, I advise you to make yourself a cup of coffee and prepare for a bit dizzying code. My approach to solving the puzzle follows:

IF (SUBSTRING(COLUMNS_UPDATED(),(@i - 1) / 8 + 1, 1))     & POWER(2, (@i - 1) % 8) = POWER(2, (@i - 1) % 8)

Web Listing B, which puts all these steps together, shows the complete trigger for the T1 table. To test the trigger, run the code in Web Listing B, then issue the following update:

UPDATE T1  SET col3 = 2, col87 = 2, col95 = 2WHERE key_col = 1

You should get the following result:

updated_column--------------col3col87col95