Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Testing for an Updated Column in a Trigger

By Andy Warren,

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.

CREATE TABLE [Customers_History] (
[RowId] int identity (1,1) not null,
[DateEntered] datetime not null default getdate(),
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]


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:
Bit 1 2 3 4 5 6 7 8
Value 1 2 4 8 16 32 64 128


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:

create trigger upd_Customers on customers for update as
--do a logical AND to see if only our two columns were updated
if (substring(columns_updated(),1,1) & 6 ) =6 and (substring(columns_updated(),2,1)=0)
INSERT INTO Customers_History ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])
select [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] from deleted
go
--this should put one row into the history table
update customers set companyname='Test Company #1', contactname='Test Contact #1' where customerid='whitc'
go

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:

ALTER trigger upd_Customers on customers for update as

if (substring(columns_updated(),1,1) & 6) >= 6

INSERT INTO Customers_History ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

select [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] from deleted

go

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!

ALTER trigger upd_Customers on customers for update as

if (substring(columns_updated(),1,1)) > 0

INSERT INTO Customers_History ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

select [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] from deleted

go

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.

ALTER trigger upd_Customers on customers for update as

if (substring(columns_updated(),1,1)|6 ) = 6

INSERT INTO Customers_History ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

select [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] from deleted
go

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:

01000000000

Companyname was updated

00100000000

Contactname was updated

01100000000

Our bitmask, 6

01100000000

Our bitmask, 6

________

________

01100000000

Returns 6

01100000000

Returns 6

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

ALTER trigger upd_Customers on customers for update as
if (substring(columns_updated(),1,1)|6 ) >= 6

INSERT INTO Customers_History ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

select [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] from deleted
go

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:

ALTER trigger upd_Customers on customers for update as

if (substring(columns_updated(),1,1)^6 ) > 0

INSERT INTO Customers_History ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

select [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] from deleted
go

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!

Total article views: 17844 | Views in the last 30 days: 15
 
Related Articles
FORUM

updating a column

updating a column

FORUM

Customize SQL Query

Customize

FORUM

Updating varchar(max) column

Updating varchar(max) column

FORUM

updating a column

updating a column

ARTICLE

Customer Database Update A Practical Solution

If you deliver applications to a customer, then you have struggled with the challenges of updating s...

Tags
sql server 7    
triggers    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones