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 (1,1) not null, 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!