SQL Server - 使用视图而不是触发器时获取插入的记录标识值

Question

对于几个具有标识字段的表，我们正在使用视图和这些视图上的触发器来实现行级安全方案。下面是一个简化的示例结构：

-- Table
CREATE TABLE tblItem (
    ItemId int identity(1,1) primary key,
    Name varchar(20)
)
go

-- View
CREATE VIEW vwItem 
AS
    SELECT *
    FROM tblItem
    -- RLS Filtering Condition
go

-- Instead Of Insert Trigger
CREATE TRIGGER IO_vwItem_Insert ON vwItem
INSTEAD OF INSERT
AS BEGIN
    -- RLS Security Checks on inserted Table

    -- Insert Records Into Table
    INSERT INTO tblItem (Name)
    SELECT Name
    FROM inserted;
END
go

如果我想插入一条记录并获取其标识，在实现 RLS Replace Of 触发器之前，我使用：

DECLARE @ItemId int;

INSERT INTO tblItem (Name)
VALUES ('MyName');

SELECT @ItemId = SCOPE_IDENTITY();

使用该触发器，SCOPE_IDENTITY() 不再起作用 - 它返回 NULL。我已经看到了使用 OUTPUT 子句来恢复身份的建议，但我似乎无法让它按照我需要的方式工作。如果我将 OUTPUT 子句放在视图插入中，则不会向其中输入任何内容。

-- Nothing is added to @ItemIds
DECLARE @ItemIds TABLE (ItemId int);

INSERT INTO vwItem (Name)
OUTPUT INSERTED.ItemId INTO @ItemIds
VALUES ('MyName');

如果我将 OUTPUT 子句放在 INSERT 语句的触发器中，触发器将返回该表（我可以从 SQL Management Studio 查看它）。我似乎无法在调用代码中捕获它；通过在该调用上使用 OUTPUT 子句或使用 SELECT * FROM ()。

-- Modified Instead Of Insert Trigger w/ Output
CREATE TRIGGER IO_vwItem_Insert ON vwItem
INSTEAD OF INSERT
AS BEGIN
    -- RLS Security Checks on inserted Table

    -- Insert Records Into Table
    INSERT INTO tblItem (Name)
    OUTPUT INSERTED.ItemId
    SELECT Name
    FROM inserted;
END
go

-- Calling Code
INSERT INTO vwItem (Name)
VALUES ('MyName');

我唯一能想到的是使用 IDENT_CURRENT() 函数。由于这在当前范围内不起作用，因此存在并发用户同时插入并将其搞乱的问题。如果整个操作都包含在事务中，是否可以防止并发问题？

BEGIN TRANSACTION

DECLARE @ItemId int;

INSERT INTO tblItem (Name)
VALUES ('MyName');

SELECT @ItemId = IDENT_CURRENT('tblItem');

COMMIT TRANSACTION

有人对如何做得更好有什么建议吗？

我知道有人会读到这篇文章并说“触发器是邪恶的，不要使用它们！”虽然我很欣赏你的信念，但请不要提供这个“建议”。

Answer 1

您可以尝试 SET CONTEXT_INFO触发器由 CONTEXT_INFO() 在客户端中。

我们以另一种方式使用它来将信息传递到触发器中，但会反向工作。

Answer 2

在这种情况下，您尝试过@@identity 吗？您提到了scope_Identity() 和identity_current()，但没有提到@@identity。