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 PletcherConnect With a Mentor Senior 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.