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

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

Solution 2

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

Comments

[ZygD]

Good thinking. The usual scoping "problem" might be a help in this case.

[CuppM]

How would @@IDENTITY be better than IDENT_CURRENT()? From what I understand, while neither are limited to the scope of the calling code, @@IDENTITY is the last identity value inserted no matter where. So if I have an audit trigger on the table which inserts a record into an audit table, @@IDENTITY could return that row's identity (if I understand correctly). That's why I figured IDENT_CURRENT() was better because it at least limited the "scope" to the specific table.

[ZygD]

@CuppM: @@IDENTITY is per session, not per scope. IDENT_CURRENT() is neither and could by any session/scope

[CuppM]

@gbn: Ah. So with the audit trigger, would @@IDENTITY return the audit record's identity?

[ZygD]

@CuppM: In theory. I'd like to say my CONTEXT_INFO idea is better but don't think I can... :-(

[dburges]

If you are calling from outside the trigger, indeed @@identity would return the worng thing if you also have an audit trigger, but I think inthis context, you would get the correct value as the other trigger hasn't run yet as you can only have one instead of trigger. But you would have to test to see. Never tried to do this, so I suggested it just as a thing to try not sure that it would work. Of course you could forbid after triggers on the table and put all the logic of them into the instead of trigger.

[Rob Garrison]

See my related question about CONTEXT_INFO() use: stackoverflow.com/questions/1616229/contextinfo-and-convert

[ZygD]

@Rob: I added an answer to this

[Rob Garrison]

Remember that none of this works if you insert more than one record at a time. I believe triggers (when used) should always be written (and tested) to work with sets.