SQL Server-使用视图代替触发器时获取插入的记录标识值 [英] SQL Server - Get Inserted Record Identity Value when Using a View's Instead Of Trigger

问题描述

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

For several tables that have identity fields, we are implementing a Row Level Security scheme using Views and Instead Of triggers on those views. Here is a simplified example structure:

-- 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而不是触发器之前，我使用了：

If I want to insert a record and get its identity, before implementing the RLS Instead Of trigger, I used:

DECLARE @ItemId int;

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

SELECT @ItemId = SCOPE_IDENTITY();

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

With the trigger, SCOPE_IDENTITY() no longer works - it returns NULL. I've seen suggestions for using the OUTPUT clause to get the identity back, but I can't seem to get it to work the way I need it to. If I put the OUTPUT clause on the view insert, nothing is ever entered into it.

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

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

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

If I put the OUTPUT clause in the trigger on the INSERT statement, the trigger returns the table (I can view it from SQL Management Studio). I can't seem to capture it in the calling code; either by using an OUTPUT clause on that call or using a 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（）函数。由于该操作在当前范围内不起作用，因此存在一个问题，即并发用户同时插入并将其弄乱。如果整个操作都封装在一个事务中，那么是否可以防止并发问题？

The only thing I can think of is to use the IDENT_CURRENT() function. Since that doesn't operate in the current scope, there's an issue of concurrent users inserting at the same time and messing it up. If the entire operation is wrapped in a transaction, would that prevent the concurrency issue?

BEGIN TRANSACTION

DECLARE @ItemId int;

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

SELECT @ItemId = IDENT_CURRENT('tblItem');

COMMIT TRANSACTION

有人对如何做得更好有任何建议？

Does anyone have any suggestions on how to do this better?

我知道外面的人会读到这句话，并说触发器是邪恶的，不要使用它们！我很感谢您的信念，但请不要提出这种建议。

I know people out there who will read this and say "Triggers are EVIL, don't use them!" While I appreciate your convictions, please don't offer that "suggestion".

推荐答案

在这种情况下，请您尝试使用@@ identity ？您提到了scope_Identity（）和identity_current（），但没有提到@@ identity。

Have you in this case tried @@identity? You mentioned both scope_Identity() and identity_current() but not @@identity.

这篇关于SQL Server-使用视图代替触发器时获取插入的记录标识值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！