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 Instead 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');

如果我在INSERT语句的触发器中放置OUTPUT子句，则触发器会返回表格（可以从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

有没有人对如何更好地完成这个任务有建议？

我知道有些人会说：“触发器是邪恶的，不要使用它们！”虽然我欣赏你的信念，但请不要提出这种“建议”。

- CuppM

Answer 1

你可以尝试在触发器中使用SET CONTEXT_INFO，然后在客户端中使用CONTEXT_INFO()来读取它。

我们通常是将信息传递到触发器中，但反过来也同样适用。

- gbn

---

请查看我关于CONTEXT_INFO()使用的相关问题：https://dev59.com/IUnSa4cB1Zd3GeqPSfS8 - Rob Garrison

@Rob：我已经添加了一个答案到这个问题。 - gbn

Answer 2

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

- HLGEM

---

@@IDENTITY比IDENT_CURRENT()更好吗？据我所知，虽然两者都不限于调用代码的范围，但@@IDENTITY是最后插入的标识值，无论在哪里。因此，如果我在表上有一个审计触发器，它会将记录插入到审计表中，@@IDENTITY可以返回该行的标识（如果我理解正确的话）。这就是为什么我认为IDENT_CURRENT()更好，因为它至少将“范围”限制在特定的表中。 - CuppM

@CuppM：@@IDENTITY是按会话而非作用域计算的。IDENT_CURRENT()也不是，它可以由任何会话/作用域调用。 - gbn

@gbn：啊，那么使用审计触发器，@@IDENTITY会返回审计记录的标识符吗？ - CuppM

@CuppM：理论上来说，我想说我的CONTEXT_INFO想法更好，但我不认为我能做到... :-( - gbn

请记住，如果您一次插入多条记录，则这些内容都不起作用。我认为触发器（在使用时）应该始终编写（并测试）以与集合一起使用。 - Rob Garrison