datavox
asked on
Trigger to update value in one table from one or more rows in another table
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_SvcETADa
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)
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_SvcETADa
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
>>
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.
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 ...