SQL Server - Get Inserted Record Identity Value when Using a View's Instead Of Trigger

Question

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

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

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

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

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".

Answer 1

You could try SET CONTEXT_INFO from the trigger to be read by CONTEXT_INFO() in the client.

We use it the other way to pass info into the trigger but would work in reverse.

Answer 2

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