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
The structure of the tables is (greatly simplified):
Claim (DataRowID bigint IDENTITY PRIMARY KEY ,<bunch of claim data>) ClaimExtended (ClaimDataRowID bigint FOREIGN KEY references dbo.Claim(DataRowID) NOT NULL ,<bunch of other claim data>)
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 ,<all the columns from the view>) INSERT INTO @Inserted (<View columns>) SELECT (<View columns>) FROM Inserted INSERT INTO Claim (<Columns>) OUTPUT I.RowID ,inserted.ClaimDataRowID INTO @IDLink (RowID, ClaimDataRowID) SELECT (<Columns>) FROM @Inserted I INSERT INTO ClaimExtended (ClaimDataRowID, <Columns>) SELECT C.ClaimDataRowID, <Columns> FROM @Inserted I INNER JOIN @IDLink C ON C.RowID = I.RowID
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
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.