I want to create a trigger that will send an e-mail when a purchase
order is approved on our system. When this occurs the value of the
approval flag changes from 2 to 1.

How can I create an update trigger that will only fire when the old
value of any value in the column was 2 and now becomes one? (I know
how I can use xp_sendmail to send the e-mail, just not sure about
monitoring for a specific value change.)

TIA

When performing an update the old value goes into the deleted table, the new
value into the inserted table
What you can do is take a look at the changes

by

select old.value, new.value from deleted old
join inserted new on new.key = old.key
where old.value = 2 and new.value = 1

If you want to perform the xp_sendmail on every item you want to retrieve a
cursor that loops through the results from your update statements,
If you are certain that only one record can be return by the update (no
batch updates) you can read the old.value and new.value into variables and
then perform the xp_sendmail statement

Good luck

--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT

http://www.dandyman.net

Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql


[quoted text, click to view]