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 byte AND 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)
- Calculate the number of columns in T1 by querying the INFORMATION_SCHEMA.COLUMNS view, and store the result in the variable @num_cols.
- Use a counter loop that repeats as many times as there are columns in the table, from 1 to the value of @num_cols (100 in this case).
- Use the SUBSTRING() function to extract the appropriate byte from the COLUMNS_UPDATED() function. This step is tricky. The first byte is the appropriate byte for the first eight columns, the second for columns 9 to 16, and so on according to the mathematical formula (col_number - 1) / 8 + 1. Note that an integer division is performed here, meaning that the result value is rounded down to the nearest integer. For example, for column 2 you get (2 - 1) / 8 + 1 = 1 / 8 + 1 = 0 + 1 = 1; for column 10 you get (10 - 1) / 8 + 1 = 9 / 8 + 1 = 1 + 1 = 2. The bit that represents column 2 is stored in the first byte, and the bit that represents column 10 is stored in the second byte.
- You calculate the respective position of the bit that represents the column handled in the current iteration of the loop from the beginning of the byte by using the mathematical formula (col_number - 1) % 8. So, for example, for column 2 you get (2-1) % 8 = 1 % 8 = 1; for column 10 you get (10-1) % 8 = 9 % 8 = 1. Both columns 2 and 10 are represented by the second bit (bit 1) in their byte.
- Use the bitwise AND operation between the byte you extracted from the COLUMNS_UPDATED() function in step 3 and the value representing the bit position you calculated in step 4. In T-SQL, the operation looks like this:
- If the column you're working with was updated, insert its ordinal position in the table (represented by the loop's counter) into a table variable (or a temporary table if you're working with SQL Server 7.0).
- After the loop, join the table variable to the INFORMATION_SCHEMA.COLUMNS view based on the ordinal position of the columns, and return the column names, sorted by the ordinal position.
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 = 2 WHERE key_col = 1
You should get the following result:
updated_column -------------- col3 col87 col95