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

x
?
Solved

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

Posted on 2004-10-01
3
Medium Priority
?
358 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 70

Expert Comment

by:Scott Pletcher
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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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 70

Expert Comment

by:Scott Pletcher
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

972 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