December 12, 2007 at 3:21 am
I have a query that will update the table I want but I am unable to figure out how to reference the Inserted table in a trigger so it only updates the inserted rows. I have tested the query on the tables and it works fine for all of the tables. Here is what I have so-far for my trigger.
USE [TEST2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UpdateAddressNote]
ON [dbo].[PM10300]
AFTER INSERT
AS
SET NOCOUNT ON
begin
UPDATE PM10300
SET PM10300.NOTEINDX = PM20000.NOTEINDX
FROM PM10200 INNER JOIN
PM20000 ON PM10200.APTVCHNM = PM20000.VCHRNMBR INNER JOIN
PM10300 ON PM10200.VCHRNMBR = PM10300.PMNTNMBR
WHERE PM10300.ROW_ID = (SELECT ROW_ID FROM INSERTED)
end
The UPDATE statement works to update the whole PM10300 table in a query but in the trigger when I add the WHERE clause in the trigger the trigger does not update any rows.
Thank you for any insight into my issue.
December 12, 2007 at 3:38 am
Hi
i think you will have to use the inserted table in the join.
"Keep Trying"
December 12, 2007 at 7:47 am
You should incorporte the inserted table in your join.
UPDATE PM10300
SET PM10300.NOTEINDX = PM20000.NOTEINDX
FROM PM10200
INNER JOIN PM20000 ON PM10200.APTVCHNM = PM20000.VCHRNMBR
INNER JOIN PM10300 ON PM10200.VCHRNMBR = PM10300.PMNTNMBR
INNER JOIN INSERTED ON PM10300.ROW_ID = INSERTED.ROW_ID
or
UPDATE PM10300
SET PM10300.NOTEINDX = PM20000.NOTEINDX
FROM PM10200 ,PM10300, PM20000, INSERTED
WHERE PM10200.APTVCHNM = PM20000.VCHRNMBR AND
PM10200.VCHRNMBR = PM10300.PMNTNMBR AND
PM10300.ROW_ID = INSERTED.ROW_ID
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply