SQL Tigger and Inserted table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » SQL Tigger and Inserted table

SQL Tigger and Inserted table

Rate Topic

Display Mode

Topic Options

Author

Message

Jon Kauffman-486833

Posted Wednesday, December 12, 2007 3:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:09 PM
Points: 2, Visits: 28

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.

Post #432206

ChiragNS

Posted Wednesday, December 12, 2007 3:38 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

Hi

i think you will have to use the inserted table in the join.

"Keep Trying"

Post #432215

Adam Haines

Posted Wednesday, December 12, 2007 7:47 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:18 PM
Points: 2,278, Visits: 2,999

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

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

My blog: http://jahaines.blogspot.com

Post #432309

« Prev Topic | Next Topic »

Permissions