sql-server - 如何处理具有IDENTITY列的表上的INSTEAD OF触发器的IDENTITY_INSERT？

我有一个带有IDENTITY列的表，并且在此表上有一个INSTEAD OF触发器。如果打开了IDENTITY_INSERT，我想插入为IDENTITY列手动指定的值。

有什么办法可以适当地应对这种情况，例如检测IDENTITY_INSERT的值？从读取结果来看，过去似乎无法检测到特定表的IDENTITY_INSERT当前值。我不知道新版本的SQL Server是否可行。

表创建SQL：

CREATE TABLE [TestTable]
(
    [Id]            INTEGER IDENTITY    NOT NULL    PRIMARY KEY,
    [ExampleField]  BIT                 NOT NULL    DEFAULT(1)
)

这是我目前正在尝试的方法，但是似乎很浪费，因为IDENTITY_INSERT可能在大多数时间都处于关闭状态，这意味着它在第一次插入尝试时总是会失败，这在性能上是浪费的。 。

CREATE TRIGGER [dbo].[trTestTable_ioi] ON [dbo].[TestTable] INSTEAD OF INSERT
AS
BEGIN
    BEGIN TRY
        INSERT INTO [TestTable]([Id],[ExampleField])
        SELECT [Id], [ExampleField]
        FROM [inserted]
    END TRY
    BEGIN CATCH
        INSERT INTO [TestTable]([ExampleField])
        SELECT [ExampleField]
        FROM [inserted]
    END CATCH
END

最佳答案

如果IDENTITY种子和增量使得生成的值始终为非零（正种子和增量或负种子和增量），则可以在虚拟inserted表中检查非零值，并使用该值存在时的价值。这取决于我的观察，使用INSTEAD OF触发器和IDENTITY_INSERT OFF，标识值为零。但是，我找不到这种行为的专门记录，因此您应该在自己的环境中进行审核，并自行承担风险。

例：

CREATE TRIGGER [dbo].[trTestTable_ioi] ON [dbo].[TestTable] INSTEAD OF INSERT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM inserted WHERE ID <> 0)
BEGIN
    --IDENTITY_INSERT is ON
    INSERT INTO [TestTable]([Id],[ExampleField])
    SELECT [Id], [ExampleField]
    FROM [inserted];
END
ELSE
BEGIN
    --IDENTITY_INSERT is OFF
    INSERT INTO [TestTable]([ExampleField])
    SELECT [ExampleField]
    FROM [inserted];
END;
GO

SET IDENTITY_INSERT dbo.TestTable OFF;
GO
--identity value auto-assigned
INSERT INTO TestTable VALUES(1);
GO

SET IDENTITY_INSERT dbo.TestTable ON;
GO
--explict identity value specified
INSERT INTO TestTable(ID, ExampleField) VALUES(11, 1);
GO

SET IDENTITY_INSERT dbo.TestTable OFF;
GO
--fails as expected because identity value cannot be specified with IDENTITY_INSERT OFF
INSERT INTO TestTable(ID, ExampleField) VALUES(11, 1);
GO

SET IDENTITY_INSERT dbo.TestTable ON;
GO
--fails as expected because identity value must be specified with IDENTITY_INSERT ON
INSERT INTO TestTable VALUES(1);
GO