Using Columns_Updated() in a Trigger

The columns_updated function gives you the ability to easily test to see if specific columns were modified with less code than you might otherwise need to use. In this article I'll demonstrate how to create a trigger that uses this function and point out some reasons why you may NOT want to use it! You may also want to look at another article I've posted Using Update() in Triggers.

NOTE: If you're not familiar with the bit operators AND, OR, and XOR, you will find it helpful to pause here and take a quick look at BOL or the links to MSDN I've provided here.

One thing to point out as we begin is that while you can use this function in an update, insert, or delete trigger, the information it returns is really only useful in a update trigger - since for either an insert all columns will be affected and for a delete no columns will be affected.

For the examples that follow I'll be using the customers table in Northwind. I also created the following table in Northwind, based on the structure of customers plus the rowid and dateentered columns. The history table isn't central to the article, it just gives us a place to view the results of different examples.

Columns_updated() returns a varbinary bitmask showing which columns were updated. That means for every column in the table, the bitmask will contain a 1 if it modified, a 0 if it was not. For example, if we did an update that modified both the companyname and the contactname in the customers table, this is what the bitmask would look like: 01100000000. The bitmask starts at the left most column, so the value for customerid will be 0 since it was not changed, followed by two 1's indicating that companyname and contactname were changed, followed by 0's for all remaining columns.

In this case the columns_updated() function would return the value 6 for this update - you have to translate that to the bitmask! To translate, we have to think about what a byte really is - eight bits, each set to either on or off. Each bit is raised to a power of 2. Here is how each bit is valued when it is set:

If we turn on bits 2 and 3, we add those values for those (2 and 4) to arrive at a total of 6. If we also updated the contacttitle column, bits 2, 3 and 4 would be set, changing the value returned by columns_updated() to 14. It's a little confusing at first! Let's work through an example, then we'll come back to the math. For this first example we only want to insert a row into the history table if ONLY the companyname and the contactname were modified:

You can see that if the update criteria is met, we insert the contents of the deleted table into the history table, preserving the "before" state of the record. Why are we using substring? Well, even though the columns_updated() function returns a single value, we can only do bit operations (AND, OR, XOR) on a single byte, and a byte can only address eight columns. Since the customers table has 11 columns, the function will return two bytes each time, forcing us to test each byte separately. To achieve our goal we need to have a bitmask of 6 for the first byte and a bitmask of 0 for the second byte.

Ok, what if you want to log any update that changes companyname and contactname, even if other columns are changed as well? Consider what the bitmask will look like if contacttitle is also modified (01110000000 = 14) or customerid (11100000000 = 7) - if both companyname and contactname are modified, the resulting value will ALWAYS be equal or greater than 6. Our trigger actually becomes simpler since we don't care about the bitmask in the second byte:

You could even test to see if ANY column was updated, by testing for a columns_updated() value greater than zero, although just having the update trigger fire should tell you that!

We can also do some different tests. Suppose you want to insert into the history table if either companyname or contactname was updated? If companyname only was updated the byte value would be 2 (01000000000) or if contact only was updated the value would be 4 (00100000000). Can you think of a test that would handle all the variations? The trick here is to use OR instead of AND.

If you're not used to bit operations, this may seem confusing! When using OR, the rule is that if either bit is true (or set), the result is true. Here is how the math works out:

The next example will insert a row if either companyname or contactname is updated, even if other columns are updated as well:

You could also encounter a scenario where you want to insert a row into the history table only if the companyname and contactname were NOT modified. You can use the exclusive OR (XOR) to help you easily test for this condition:

As you can see, there are a lot of ways you can use this function to simplify your code inside a trigger. In many cases I'm not sure it's worth the complexity. Determining the correct bitmask and operator to use can take quite a bit of trial and error. The biggest pitfall with this technique is that if the column order changes, the trigger will continue to function - just incorrectly. If you decide to use it, test it carefully to make sure your logic is sound and document INSIDE the trigger exactly which columns you are testing and why.

Got a question or a comment? It may take a day or two depending on my schedule, but I WILL reply!