獲取身份值以在 INSTEAD OF 觸發器中用作 FK

我有一系列可更新的視圖，我們向最終使用者公開，作為後端流程的介面。

其中一個視圖引用了兩個表，並且需要INSTEAD OF觸發器UPDATE和INSERTs。

表格的結構是（大大簡化）：

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>)

我最初的計劃是在觸發器中這樣做：

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子句不起作用，但是（Multi-part identifier I.RowID could not be bound）我假設是因為我無法在INSERT OUTPUT子句中引用源表。

除了使視圖成為表格之外，我還可以在這裡使用什麼其他方法？由於其他原因，這需要是 aVIEW並且基礎表幾乎是一成不變的。

表：

CREATE TABLE dbo.Claim
(
   DataRowID   bigint IDENTITY NOT NULL,
   ClaimColumn integer NOT NULL,

   CONSTRAINT PK_Claim
       PRIMARY KEY CLUSTERED (DataRowID)
);
GO
CREATE TABLE dbo.ClaimExtended
(
   ClaimDataRowID  bigint NOT NULL,
   ExtendedColumn  integer NOT NULL,

   CONSTRAINT PK_ClaimExtended
       PRIMARY KEY CLUSTERED (ClaimDataRowID),

   CONSTRAINT FK_ClaimExtended_Claim
       FOREIGN KEY (ClaimDataRowID)
       REFERENCES dbo.Claim (DataRowID)
);

看法：

CREATE VIEW dbo.MyView
WITH SCHEMABINDING
AS
   SELECT
       c.DataRowID,
       c.ClaimColumn,
       ce.ExtendedColumn
   FROM dbo.Claim AS c
   JOIN dbo.ClaimExtended AS ce ON
       ce.ClaimDataRowID = c.DataRowID;

代替觸發器：

CREATE TRIGGER trgMyView_IOI
ON dbo.MyView
INSTEAD OF INSERT
AS
BEGIN
   SET NOCOUNT ON;
   SET ROWCOUNT 0;

   DECLARE @ExtendedRows AS TABLE
   (
       ClaimDataRowID  bigint PRIMARY KEY, 
       ExtendedColumn  integer NOT NULL
   );

   MERGE dbo.Claim AS c
   USING INSERTED AS ins ON 1 = 0
   WHEN NOT MATCHED THEN 
       INSERT (ClaimColumn)
       VALUES (ins.ClaimColumn)
   OUTPUT
       INSERTED.DataRowID,
       ins.ExtendedColumn
   INTO @ExtendedRows 
       (
           ClaimDataRowID,
           ExtendedColumn
       );

   INSERT dbo.ClaimExtended
       (ClaimDataRowID, ExtendedColumn)
   SELECT
       er.ClaimDataRowID,
       er.ExtendedColumn
   FROM @ExtendedRows AS er;
END;

範例用法：

INSERT dbo.MyView
   (ClaimColumn, ExtendedColumn)
VALUES
   (1000, 2000),
   (1001, 2001);
GO
SELECT
   mv.DataRowID,
   mv.ClaimColumn,
   mv.ExtendedColumn
FROM dbo.MyView AS mv;

輸出：

引用自：https://dba.stackexchange.com/questions/34258