title: SQL Server - Get Inserted Record Identity Value when Using a View's Instead Of Trigger
body: <p>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: </p>
  
  <pre><code>-- 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
  </code></pre>
  
  <p>If I want to insert a record and get its identity, before implementing the RLS Instead Of trigger, I used:</p>
  
  <pre><code>DECLARE @ItemId int;
  
  INSERT INTO tblItem (Name)
  VALUES ('MyName');
  
  SELECT @ItemId = SCOPE_IDENTITY();
  </code></pre>
  
  <p>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.  </p>
  
  <pre><code>-- Nothing is added to @ItemIds
  DECLARE @ItemIds TABLE (ItemId int);
  
  INSERT INTO vwItem (Name)
  OUTPUT INSERTED.ItemId INTO @ItemIds
  VALUES ('MyName');
  </code></pre>
  
  <p>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 ().</p>
  
  <pre><code>-- 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');
  </code></pre>
  
  <p>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?</p>
  
  <pre><code>BEGIN TRANSACTION
  
  DECLARE @ItemId int;
  
  INSERT INTO tblItem (Name)
  VALUES ('MyName');
  
  SELECT @ItemId = IDENT_CURRENT('tblItem');
  
  COMMIT TRANSACTION
  </code></pre>
  
  <p>Does anyone have any suggestions on how to do this better?</p>
  
  <p>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".</p>