Solved

Trigger to update value in one table from one or more rows in another table

Posted on 2004-10-01
3
338 Views
Last Modified: 2008-01-16
I have two triggers on two related tables.  The first trigger works fine (including it here just in case).  This trigger updates the REQUESTED DATE on the PO line items when the REQUESTED DATE on the PO header is changed.

CREATE TRIGGER Dvx_SyncReqDate ON POP10100
FOR INSERT, UPDATE
AS
UPDATE POP10110
SET POP10110.REQDATE = POP10100.REQDATE
FROM         POP10100 INNER JOIN
                      POP10110 ON POP10100.PONUMBER = POP10110.PONUMBER
WHERE POP10110. PONUMBER =
  (select PONUMBER from inserted where POP10110.PONUMBER = inserted.PONUMBER)


This next trigger changes the ETA DATE on the associated Service Call, REQSTDBY,  (if there is one, otherwise it should not fire) to put it in sync with the REQUESTED DATE on the PO.  This one works fine unless there are multiple rows in the POP10110 table, but give the error "a save operation on table POP_PO failed accessing SQL data" when ther are multiple rows.  This error is returned by the application.  In query analyzer, I get this msg:

"Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."


CREATE TRIGGER Dvx_SyncPOReqDate_SvcETADate ON POP10110
FOR INSERT, UPDATE
AS
UPDATE SVC00200
SET SVC00200.ETADTE = POP10110.REQDATE
FROM         POP10110 INNER JOIN
                      SVC00200 ON POP10110.REQSTDBY = SVC00200.CALLNBR
WHERE REQSTDBY<>''
AND POP10110.PONUMBER =
  (select PONUMBER from inserted where POP10110.PONUMBER = inserted.PONUMBER)

0
Comment
Question by:datavox
  • 3
3 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12203782
I think you can simplify and shorten the first trigger to code below (and probably improve its performance, at least for larger tables):

CREATE TRIGGER Dvx_SyncReqDate ON POP10100
FOR INSERT, UPDATE
AS
UPDATE POP10110
SET POP10110.REQDATE = INSERTED.REQDATE
FROM POP10110 INNER JOIN
     INSERTED ON POP10110.PONUMBER = INSERTED.PONUMBER


Will comment on the second trigger in a sec ...

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 12203819
CREATE TRIGGER Dvx_SyncPOReqDate_SvcETADate ON POP10110
FOR INSERT, UPDATE
AS
UPDATE SVC00200
SET SVC00200.ETADTE = INSERTED.REQDATE
FROM SVC00200 INNER JOIN
     INSERTED ON SVC00200.CALLNBR = INSERTED.REQSTDBY
WHERE INSERTED.REQSTDBY <> ''


I think the last part of the WHERE on the original 2nd trigger is redundant and can simply be removed.

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12203836
That is, this clause:

>>
AND POP10110.PONUMBER =
  (select PONUMBER from inserted where POP10110.PONUMBER = inserted.PONUMBER)
<<

is not needed, because the trigger is firing after the INSERT/UPDATE, so the PONUMBER on POP10110 (the trigger table) will *always* match the one on INSERTED because they're the same row; the INSERTED table contains, as you probably know, the row(s) that were just inserted/updated on the main/trigger table.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now