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 INSERT
s.
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
Last activity: Feb 23, 2016, 06:45 AM