如何使用INSTEAD OF触发器获取插入表中新记录的身份标识

Question

如何使用INSTEAD OF触发器获取插入表中新记录的身份标识

7

我正在使用一个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

在插入触发器中，使用“name”列进行连接并非理想选择，但需要处理多个插入操作。
例如：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？

解决方案

不可能！在对具有INSTEAD OF触发器的表进行INSERT时，无法可靠地返回标识符。以下Sidux的回答是我情况下的一个好的解决方法（将INSTEAD OF触发器替换为AFTER触发器并添加默认列）。

- JumpingJezza

您的示例INSERT语句出现错误：无法将NULL值插入到列'rowVersion'中。 - Felix Pamittan

@wewesthemenace 修复了插入触发器的脚本 - 谢谢！在将代码转换为这个简化版本时，我错过了它。 - JumpingJezza

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dmitrij Kultasev · Accepted Answer

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

基本上，您不需要TRG_INS_Table1触发器，只需使用列的DEFAULT值= 1即可。此外，如果您使用DATETIME列而不是rowversion，则可以使用INSERTED表的状态和GETDATE（）值将其插入历史记录中。在这种情况下，您可以按Dtime列DESC排序并查看历史记录。