How to get the ID of new INSERTED records in a table with an INSTEAD OF trigger

Question

I use the INSTEAD OF insert trigger in the table to set the incremental version number in the row, and also copy the row to the second history / audit table.
Rows are inserted into both tables without problems.
However, I had problems returning a new identifier from the 1st table back to the user.

Scheme

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 

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

Attempts to solve

When inserting SCOPE_IDENTITY is NULL .

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

I 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 

Lines are inserted with a penalty and have identifiers, but I cannot access them unless I use the following - which seems hacked:

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

What is the right way to get a new ID

---

Decision

Can not be! You cannot reliably return an identifier when executing INSERT in a table with an INSTEAD OF trigger. The Sidux answer below is a good workaround for my situation (replace the INSTEAD OF trigger with the AFTER trigger and the DEFAULT columns have been added).

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 the TRG_INS_Table1 trigger, you can just use the DEFAULT = 1 value for the column and it. Also, if you use the DATETIME column instead of rowversion, you can simply insert the state of the INSERTED table into the history using the value GETDATE (). In this case, you can order a Dtime DESC column, and you have a story.