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
AAA
aaaid   deletionstatecode    activityid

--------------------------------------
BBB
bbbid   phonecallid   statecode

Open in new window

sailing_12Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
CREATE TRIGGER aaa_trigger_1
ON aaa
AFTER UPDATE
AS
IF @@ROWCOUNT = 0
    RETURN
IF NOT UPDATE(deletionstatecode)
    RETURN
UPDATE bbb
SET bbb.statecode = 1
FROM bbb
INNER JOIN inserted i ON i.activityid = b.phonecallid
INNER JOIN deleted d ON i.activityid = d.activityid
WHERE i.deletionstatecode = 2
  AND (i.deletionstatecode <> d.deletionstatecode OR d.deletionstatecode IS NULL)
0
 
dportasCommented:
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.
0
 
Scott PletcherSenior DBACommented:
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).
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
EmesCommented:
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
0
 
sailing_12Author Commented:
Scott, on your first inner join, I see alias b on phonecallid - but where is this defined?
0
 
Scott PletcherSenior DBACommented:
Sorry, should be bbb.
0
 
Scott PletcherSenior DBACommented:
I.e.:

INNER JOIN inserted i ON i.activityid = bbb.phonecallid
0
 
sailing_12Author Commented:
OK thanks.
0
 
sailing_12Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.