我在表上使用 INSTEAD OF 插入触发器来设置该行上递增的版本号,并将该行复制到第二个历史记录/审核表。
这些行插入到两个表中都没有问题。
但是,我无法将第一个表中的新身份返回给用户。
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
加入name
插入触发器中的列并不理想,但它需要一次处理多个插入。
eg INSERT INTO Table1 (name) VALUES('xxx'),('yyy')
尝试的解决方案
进行插入操作时,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
:
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的正确方法是什么?
Solution
不可能的!在具有 INSTEAD OF 触发器的表上执行 INSERT 时,无法可靠地返回标识。西杜克斯的回答 https://stackoverflow.com/a/29116190/345659下面是适合我的情况的一个很好的解决方法(将 INSTEAD OF 触发器替换为 AFTER 触发器并添加 DEFAULT 列)。