Sample Header Ad - 728x90

Getting Identity values to use as FK in an INSTEAD OF trigger

7 votes
1 answer
2578 views
I have a series of updateable views we are exposing to end users as the interface for a back end process. One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and INSERTs. The structure of the tables is (greatly simplified): Claim (DataRowID bigint IDENTITY PRIMARY KEY ,) ClaimExtended (ClaimDataRowID bigint FOREIGN KEY references dbo.Claim(DataRowID) NOT NULL ,) My original plan was to do this in the trigger like so: CREATE TRIGGER [dbo].[MyTrigger] ON [dbo].[MyView] INSTEAD OF INSERT AS DECLARE @IDLink TABLE (RowID int ,ClaimDataRowID bigint) DECLARE @Inserted TABLE (RowID int identity (1,1) NOT NULL ,) INSERT INTO @Inserted () SELECT () FROM Inserted INSERT INTO Claim () OUTPUT I.RowID ,inserted.ClaimDataRowID INTO @IDLink (RowID, ClaimDataRowID) SELECT () FROM @Inserted I INSERT INTO ClaimExtended (ClaimDataRowID, ) SELECT C.ClaimDataRowID, FROM @Inserted I INNER JOIN @IDLink C ON C.RowID = I.RowID The OUTPUT clause here is not working, however (Multi-part identifier I.RowID could not be bound) I'm assuming because I can't reference the source table in an INSERT OUTPUT clause. What other method could I use here besides making the view a table? For other reasons this needs to be a VIEW and the underlying tables are pretty much set in stone.
Asked by JNK (18074 rep)
Feb 6, 2013, 05:20 PM
Last activity: Feb 23, 2016, 06:45 AM