Solved

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

Posted on 2004-10-01
3
340 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: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 69

Accepted Solution

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 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