Solved

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

Posted on 2004-10-01
3
336 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
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…

746 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

16 Experts available now in Live!

Get 1:1 Help Now