November 6, 2006 at 3:46 pm
I have a Members table ( MemberNbr, Status1, Status2 ) from which I need to capture changes to Status1 and/or Status2.
I believe a trigger for the Update event would work, but I need to capture CHANGES, not just updates. Several member records could be updated simultaneously.
In addition to the IF Update(Status1) or IF Update(Status2) statement in the trigger, how can I evaluate changes to either (or both) of those columns, especially if multiple records may be updated at the same time?
November 6, 2006 at 3:57 pm
You may join tables inserted and deleted :
FROM inserted i
INNER JOIN deleted d ON i.MemberNbr = d.MemberNbr and (i.Status1 <> d.Status1 OR i.Status2 <> d.Status2 )
_____________
Code for TallyGenerator
November 6, 2006 at 3:58 pm
Inside your trigger, you will have two tables: inserted and deleted. The deleted table has the old values and the inserted has the new values. So, you can evaluate the changes using these tables.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
November 6, 2006 at 3:59 pm
Do you need to check for inserts also?
create trigger trg_members_upd ON members
AFTER INSERT
AS
declare @memberNbr int, @status1 int, @status2 int
set @memberNbr = 0
WHILE exists (select * from inserted where memberNbr > @memberNbr)
BEGIN
SELECT @memberNbr = d.memberNbr,
@status1 = d.status
@status2 = i.status
FROM deleted d INNER JOIN inserted on d.memberNbr = i.membernbr
IF @status1 @status2
record info
END
November 6, 2006 at 4:02 pm
Sorry about the double post, stupid firewall.
I like Sergiy's additional where comment i.status d.status
November 6, 2006 at 4:06 pm
I think Sergiy's solution will fit the bill. Thank very much!
November 6, 2006 at 9:44 pm
You can delete it >> Edit post, then delete at the bottom right.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply