How to get Identity of new records INSERTED into table with INSTEAD OF trigger

Question

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.
The rows are inserted to both tables without a problem.
However, I am having trouble returning the new identity from the 1st table back to the user.

Schema

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

Joining on the name column in the insert trigger is not ideal, but it needs to handle multiple inserts at once.
eg INSERT INTO Table1 (name) VALUES('xxx'),('yyy')

Attempted Solutions

When doing an insert, SCOPE_IDENTITY is NULL.

INSERT INTO Table1(name)
VALUES('xxx')
SELECT SCOPE_IDENTITY() 

or 

INSERT INTO Table1(name)
VALUES('xxx')
RETURN SCOPE_IDENTITY()

I've also tried using OUTPUT - which returns 0:

DECLARE @IdentityOutput TABLE (id INT)
INSERT INTO Table1(name)
OUTPUT INSERTED.id INTO @IdentityOutput
VALUES('xxx')
SELECT id FROM @IdentityOutput

The rows are inserted fine and have IDs, but I cannot access them unless I use the below - which seems hacky:

INSERT INTO Table1(name)
VALUES('xxx')
SELECT id from Table1 WHERE name = 'xxx' 

What is the proper way to get the new ID??

---

Solution

Impossible! You can't reliably return the identity when doing an INSERT on a table that has an INSTEAD OF trigger. Sidux's answer below is a good workaround for my situation (replace INSTEAD OF trigger with AFTER trigger and added DEFAULT columns).

Leavy

Your sample INSERT statement produces an error: Cannot insert the value NULL into column 'rowVersion'. – Goodrum

@wewesthemenace Fixed script for the Insert trigger - thanks! I missed it converting the code to this simplified version – Leavy

Answer 1

CREATE TABLE Table1
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   name VARCHAR(250) NOT NULL UNIQUE,
   rowVersion INT NOT NULL
)
GO
CREATE TABLE Table1History
(
   id INT NOT NULL,
   name VARCHAR(250) NOT NULL,
   rowVersion INT NOT NULL
)   
GO
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)
    SELECT i.name, 1
    FROM INSERTED i
END
GO
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
END
GO
CREATE TRIGGER TRG_AFT_INS_Table1
ON Table1
AFTER INSERT, UPDATE
AS

BEGIN

INSERT INTO Table1History (id, name, rowVersion)

    SELECT i.ID, i.name, i.rowversion
    FROM INSERTED i

END
GO

INSERT INTO Table1 (name) VALUES('xxx'),('yyy')

SELECT * FROM Table1History
-----------------------------------------------
id  name    rowVersion
2   yyy 1
1   xxx 1
-----------------------------------------------

UPDATE Table1 SET name = 'xxx1' WHERE id  = 1;

SELECT * FROM Table1History
-----------------------------------------------
id  name    rowVersion
2   yyy 1
1   xxx 1
1   xxx1    2
-----------------------------------------------

Basically you do not need TRG_INS_Table1 trigger, you can just use DEFAULT value = 1 for column and that's it. Also if you use DATETIME column instead of rowversion, you can just insert the state of INSERTED table to the history with the GETDATE() value. In that case you can order by Dtime column DESC and you have history.

Leandra

this doesn't return the ID? – Leavy

The table1History has Id value. What's wrong with it? – Leandra

How do I find the specific row I just inserted? There could be multiple users inserting into the table at the same time. I can't just do a select top 1 – Leavy

You can't access the rowversion in the INSERTED table because it is not inserted - it is automatically generated (see the INSERT/UPDATE triggers). That is why I had to use the INSTEAD OF triggers. – Leavy

isn't it what you are expected? If not then please explain with the expected result examples – Leandra

I have a working set of triggers that insert/update correctly. The history table is populated just fine already thank you. I am asking how to return the ID of the row I just inserted into Table1. – Leavy

You CAN NOT simply return it in instead of trigger as it is not generated yet. You can find the value in the similar way you are doing (joining it by name or some other tricks). However after insert trigger returns the ID column. – Leandra

However an AFTER INSERT trigger will not generate the rowversion, so is of no use. – Leavy

Your TRG_UPD_Table1 is generating rowversion. So instead of 2 triggers, you have 3. My suggested trigger is AFTER INSERT, UPDATE so it will be fired after INSERT or UPDATE. So when it is inserted, then the value 1 is taken (basically can be done with default cosntraint and there is no need for instead of insert trigger) and the generated value is taken after instead update trigger is fired. – Leandra

Definitely not going to use Datetime for a rowversion column again after the last project which was writing rows at the exact same time (exact to the precision of the column anyway) – Leavy

Use Timestamp column then. It will be unique for 100% – Leandra

Timestamp which interestingly has been renamed to rowversion lol. I did consider this type of column originally but went with the more user friendly int rowversion. If I had foreseen the identity headaches down the track... – Leavy

Replacing the insert trigger with the default value for the column and the after trigger seem to work fine. I'll do some more testing tomorrow – Leavy