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

Question

我有一个包含 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

Answer 1

如果您的 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