title: How to get Identity of new records INSERTED into table with INSTEAD OF trigger
body: <p>I am using an INSTEAD OF insert trigger on a table to set an incrementing version number on the row and also copy the row to a 2nd history/audit table.<br>
  The rows are inserted to both tables without a problem.<br>
  However, I am having trouble returning the new identity from the 1st table back to the user.  </p>
  
  <h2>Schema</h2>
  
  <pre><code>CREATE TABLE Table1
  (
     id INT IDENTITY(1,1) PRIMARY KEY,
     name VARCHAR(250) NOT NULL UNIQUE,
     rowVersion INT NOT NULL
  )
  
  CREATE TABLE Table1History
  (
     id INT NOT NULL,
     name VARCHAR(250) NOT NULL,
     rowVersion INT NOT NULL
  )   
  
  CREATE TRIGGER TRG_INS_Table1
  ON Table1
  INSTEAD OF INSERT
  AS
      DECLARE @OutputTbl TABLE (id INT, name VARCHAR(250))
  BEGIN
  --make the insert
  INSERT INTO Table1 (name, rowVersion)
      OUTPUT INSERTED.id, INSERTED.name INTO @OutputTbl(id, name)
      SELECT i.name, 1
      FROM INSERTED i
  --copy into history table
  INSERT INTO Table1History (id, name, rowVersion)
      SELECT t.ID, i.name, 1
      FROM INSERTED i
      JOIN @OutputTbl t on i.name = t.name
  END
  
  CREATE TRIGGER TRG_UPD_Table1
  ON Table1
  INSTEAD OF UPDATE
  AS
  BEGIN
  --make the update
  UPDATE Table1
      SET name = i.name,
          rowVersion = (SELECT d.rowVersion + 1 FROM DELETED d WHERE d.id = i.id)
      FROM INSERTED i
      WHERE Table1.id = i.id
  --copy into history table
  INSERT INTO Table1History (id, name, rowVersion)
      SELECT i.id ,i.name, (SELECT d.rowVersion + 1 FROM DELETED d WHERE d.id = i.id)
      FROM INSERTED i
  END
  </code></pre>
  
  <p>Joining on the <code>name</code> column in the insert trigger is not ideal, but it needs to handle multiple inserts at once.<br>
  eg <code>INSERT INTO Table1 (name) VALUES('xxx'),('yyy')</code></p>
  
  <h2>Attempted Solutions</h2>
  
  <p>When doing an insert, <code>SCOPE_IDENTITY</code> is <code>NULL</code>.  </p>
  
  <pre><code>INSERT INTO Table1(name)
  VALUES('xxx')
  SELECT SCOPE_IDENTITY() 
  
  or 
  
  INSERT INTO Table1(name)
  VALUES('xxx')
  RETURN SCOPE_IDENTITY()
  </code></pre>
  
  <p>I've also tried using OUTPUT - which returns <code>0</code>:</p>
  
  <pre><code>DECLARE @IdentityOutput TABLE (id INT)
  INSERT INTO Table1(name)
  OUTPUT INSERTED.id INTO @IdentityOutput
  VALUES('xxx')
  SELECT id FROM @IdentityOutput
  </code></pre>
  
  <p>The rows are inserted fine and have IDs, but I cannot access them unless I use the below - which seems hacky:</p>
  
  <pre><code>INSERT INTO Table1(name)
  VALUES('xxx')
  SELECT id from Table1 WHERE name = 'xxx' 
  </code></pre>
  
  <p><strong>What is the proper way to get the new ID??</strong></p>
  
  <hr>
  
  <h2>Solution</h2>
  
  <p>Impossible! You can't reliably return the identity when doing an INSERT on a table that has an INSTEAD OF trigger. <a href="https://stackoverflow.com/a/29116190/345659">Sidux's answer</a> below is a good workaround for my situation (replace INSTEAD OF trigger with AFTER trigger and added DEFAULT columns).</p>