Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how the fundamental information of how to create a table.
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.

670 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