Using IF UPDATE clause in trigger

[jfcox]

I'm trying to build and audit table and record only those column values that have changed during a form update. To do this, I check the column with a trigger using the IF UPDATE clause. For example:

If update (PRACNAME) select @PRACNAME = PRACNAME from deleted

From there I write the value of @PRACNAME into my audit table.

I would expect that if PRACNAME was NOT changed from it's previous value (i.e. updated value = deleted value), it would be skipped by the if statement. What is happening however, is that the data for every column gets written to the table every time whether it was changed or not.

I've tried doing this other ways and it works but it's clunky. It would be much better if I could get If Update to work. Any thoughts out there?

Jim

 

[fluteplr]

I had nothing to do with the if update statement. Apparently thne program you are using to do the update in the first place is updating all the fields each time it changes one.

If update just looks to see if a value has been applied to a field not if that value is different from the old value.

What you could do is the following.

if update(pracname)

insert into audittable
select deleted.pracname, .. other fields,
from inserted, deleted where
inserted.primarykey = deleted.primarykey and
inserted.pracname <> deleted.pracname

Also your code does not take into account a situation where more than one record is being updated at the same time. The suggested code will.