如何使用INSTEADOF触发器将新记录的标识插入表中

我在表上使用INSTEAD OF插入触发器来设置该行的递增版本号，并将该行复制到第二个历史/审计表
行被插入到两个表中没有问题
但是，我在将第一个表中的新标识返回给用户时遇到了问题。

架构

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

在insert触发器中连接name列并不理想，但它需要同时处理多个insert
例如INSERT INTO Table1 (name) VALUES('xxx'),('yyy')

尝试的解决方案

执行插入操作时，SCOPE_IDENTITY就是NULL。

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

我还尝试过使用OUTPUT，它返回0:

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

这些行插入得很好，并且有ID，但除非我使用以下内容，否则我无法访问它们——这似乎很糟糕：

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

获取新ID的正确方法是什么

---

解决方案

不可能！对具有INSTEADOF触发器的表执行INSERT操作时，无法可靠地返回标识。Sidux下面的答案是一个很好的解决方法（用AFTER触发器替换INSTEAD OF触发器并添加DEFAULT列）。

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