Sailing_12
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.