Trigger... or not trigger ?...

[yakata]

Hi there

I'm using a trigger to detect changes into a value in a table, let's say 'Status'.
My trigger gives 'if updated(Status)...'

The crazy (not mine) application that is changing the Status is sending first of all a value of '0'
then a status of '1', sometimes a hundred of times, and at last a status of '2'.
My problem is :
I'm only interested into REEL changes (from 0 to 1 and to 2), not interested to be reminded that the status value is '1' every 10 seconds...
How do I filter (trigger side) the new value against the old one ?
I'm using reference from the -updated- system table...

Where did i go wrong ?(

TIA for a reply

 

[nigelrivett]

You need to use the PK to match the old and new values

if updated(status)
insert audittbl
select i.*
from inserted i
join deleted d
on i.pk = d.pk
where i.status <> d.status

see

http://www.nigelrivett.net/Triggers_1_A_beginners_guide.html

http://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.