Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL update trigger based on value

Posted on 2010-11-09
9
Medium Priority
?
323 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:sailing_12
9 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1600 total points
ID: 34097736
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
 
LVL 22

Expert Comment

by:dportas
ID: 34098053
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 34098135
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 14

Expert Comment

by:Emes
ID: 34098412
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
 

Author Comment

by:sailing_12
ID: 34102700
Scott, on your first inner join, I see alias b on phonecallid - but where is this defined?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 34106392
Sorry, should be bbb.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 34106394
I.e.:

INNER JOIN inserted i ON i.activityid = bbb.phonecallid
0
 

Author Comment

by:sailing_12
ID: 34106564
OK thanks.
0
 

Author Closing Comment

by:sailing_12
ID: 34106569
Thanks.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question