asked on
SQL update trigger based on value
I need a trigger to do the following:
when deletionstatecode is set to 2 in table 'aaa', update statecode value to 1 in table 'bbb' where aaa.activityid = bbb.phonecallid
when deletionstatecode is set to 2 in table 'aaa', update statecode value to 1 in table 'bbb' where aaa.activityid = bbb.phonecallid
AAA
aaaid deletionstatecode activityid
--------------------------------------
BBB
bbbid phonecallid statecode
Microsoft SQL Server 2005Microsoft SQL Server
Last Comment
Sailing_12
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Do it in a stored procedure, not a trigger. Even better, consider changing the design so that there isn't a dependency between columns in two base tables. Maybe you could derive the same information in a view or query instead.
A trigger is safer than a stored proc. There is no guarantee that a stored proc will be used for all UPDATEs (and/or INSERTs).
CREATE TRIGGER TRIG2 ON AAA
For update
as
update BBB
set statecode = 1
FROM inserted i
INNER JOIN bbb ON
BBB.phonecallid = i.activityid
and i.deletionstatecode = 2
For update
as
update BBB
set statecode = 1
FROM inserted i
INNER JOIN bbb ON
BBB.phonecallid = i.activityid
and i.deletionstatecode = 2
ASKER
Scott, on your first inner join, I see alias b on phonecallid - but where is this defined?
Sorry, should be bbb.
I.e.:
INNER JOIN inserted i ON i.activityid = bbb.phonecallid
INNER JOIN inserted i ON i.activityid = bbb.phonecallid
ASKER
OK thanks.
ASKER
Thanks.