主页 / dba / 问题 / 34258
Accepted
JNK
Asked: 2013-02-07 09:20:20 +0800 CST

获取身份值以在 INSTEAD OF 触发器中用作 FK

  • 772

我有一系列可更新的视图，我们向最终用户公开，作为后端流程的接口。

其中一个视图引用了两个表，并且需要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并且基础表几乎是一成不变的。

sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    表：

    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;

    输出：

    输出

    • 8

相关问题