dcsimg

Using Update() in Triggers

March 8, 2001

In this article I'm going to discuss how to use if update() in your update triggers to simplify your code. They are easy to use and I think you'll quickly see the value it offers!

For those of you who aren't familiar with triggers, the trigger has access to two "logical" tables called inserted and deleted that only exist during the time the trigger is executing. These two tables have the EXACT same structure as the table for which the trigger fired - the inserted table has the "after" values and the deleted table has the "before" values. An update trigger will fire each time any row (or rows!) is updated - a key point to remember is that the logical tables hold as many rows as were affected by the transaction, not one row at a time!

Here is the code for a basic update trigger (I'm using the Pubs database) that logs the primary key of each row when it's changed. For these examples the log table is just to have something to help us illustrate the trigger, it's not really important to our discussion.

use pubs
go

--create the log table
create table log_authors (rowid int identity not null,
(1,1) entrydate datetime default getdate(), au_id varchar(11))
go

--create the trigger
create trigger u_authors on authors for update
as
Insert into log_authors (au_id) select au_id from inserted
go

--this will cause one row to be added to the log table
update authors set address='123' where au_id='172-32-1176'
go

Pretty straight forward. We're grabbing the au_id value(s) from the inserted table and inserting into log_authors. Now that you've got that working, you might find that you only care about logging when certain columns are updated. Since we're working with the authors table, let's say you want to log only when the address column is updated. Taking the code from above one step further, here is my first attempt.

alter trigger u_authors on authors for update
as
Insert into log_authors (au_id ) select I.au_id from inserted I inner join deleted D on I.Au_ID = D.Au_ID where I.Address <> D.Address
go

--this will add cause one row to be added to the log table
update authors set address='456' where au_id='172-32-1176'
go

I prefer to use alter once I've created an object, but since there are no permissions assigned to triggers you could just as easily use drop trigger followed by create trigger. This trigger is a little more complicated, since I need to compare the "after" value with the "before" value. To do so I need to join the two tables together using the primary key, which also means I need to fully qualify the name of the field I'm going to insert into the log table. In this example it doesn't matter whether I use the inserted au_id or the deleted au_id value since they will be the same, but in other situations you might want to log the "before" value (you're keeping an audit trail is one reason you would do this).

The code above works, just not the way you would expect. No logging will occur for any row where either the inserted or deleted value is null. Since nulls propagate, the where clause will never be true. This is the first place where the update() syntax comes in handy! Instead of adding a bunch of additional checks to the where clause to check for nulls, you can do this:

alter trigger u_authors on authors for update
as
if update(address)
Insert into log_authors (au_id ) select I.au_id from inserted I inner join deleted D on I.Au_ID = D.Au_ID
go

Using the update (column name) syntax, it's easy to see that we only want to execute the insert if the if statement is true - in this instance if the address column has been updated. You can also use multiple update()'s in your trigger. Here is an example of a trigger that will insert a row into the log table if the address OR city OR state column is updated.

alter trigger u_authors on authors for update
as
if update(address) or update(city) or update(state)
Insert into log_authors (au_id ) select I.au_id from inserted I inner join deleted D on I.Au_ID = D.Au_ID

One thing I haven't mentioned so far is the difference between a column being updated and a column being changed. An update occurs when you "set" a column to a value, even if it's the same value that already existed! I think many people mistake update triggers for "change" triggers. If you want to execute code only if something has changed, then you have to do the comparison in the where clause. 

I've posted a related article Using Columns_Update() in a Trigger that you may also find interesting!

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers