|
Featured Database Articles
Posted Mar 12, 2001
Using Columns_Updated() 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!
MS SQL Archives
|
|
» 