如何使用INSTEAD OF触发器获取插入到表中的新记录的标识 [英] How to get Identity of new records INSERTED into table with INSTEAD OF trigger

问题描述

我在表上使用INSTEAD OF插入触发器来设置行的递增版本号，还将行复制到第二个历史记录/审计表中。

将行插入到两个表中没问题。

但是，我无法从第一个表中将新身份返回给用户。

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.

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 列并不理想，但它需要一次处理多个插入。 br>
例如插入表1（名称）VALUES（'xxx'），（'yyy'）

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

进行插入操作时， SCOPE_IDENTITY 为 NULL 。

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

我也尝试使用OUTPUT-返回 0 ：

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

行插入得很好并且有ID，但是除非使用下面的ID，否则我将无法访问它们-这似乎很hacky：

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' 

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

不可能！在具有INSTEAD OF触发器的表上执行INSERT时，您将无法可靠地返回身份。 Sidux的答案是一种适合我的情况的解决方法（将INSTEAD OF触发器替换为AFTER触发器并添加DEFAULT列）。 p>

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

推荐答案

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，则只需将插入表的状态插入带有GETDATE（）值的历史记录中即可。在这种情况下，您可以通过Dtime列DESC进行订购，并且您具有历史记录。

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.

这篇关于如何使用INSTEAD OF触发器获取插入到表中的新记录的标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！