如何为含IDENTITY列的表的INSTEAD OF触发器处理IDENTITY_INSERT？

首先得明确一个核心点：直到SQL Server 2024版本，微软都没有提供官方的方式直接查询某张表的IDENTITY_INSERT当前状态——不管是系统视图还是内置函数，都查不到这个信息。所以我们得换个思路来解决你的场景，而不是执着于检测状态。

先说说你原来用的try-catch方案为什么存在性能浪费：大多数时候IDENTITY_INSERT是关闭的，每次正常插入都要先触发一次插入失败，再进入catch分支执行正确的插入，错误捕获本身就有额外开销；而且用户如果不小心传了Id值，还会被静默替换成自动生成的，很容易造成数据误解。

下面给你两个更合理的方案：

方案1：根据插入数据的Id值判断逻辑（无需调用方配合） #

这个思路是：用户开启IDENTITY_INSERT的时候，肯定是要主动传入自定义的Id值；没开启的时候，一般不会传Id（或者传了也会报错）。我们可以在触发器里先检查inserted表中的Id是否有非NULL值，再决定插入方式：

CREATE TRIGGER [dbo].[trTestTable_ioi] ON [dbo].[TestTable] INSTEAD OF INSERT 
AS 
BEGIN
    SET NOCOUNT ON;

    -- 先判断有没有用户手动指定的Id
    IF EXISTS(SELECT 1 FROM inserted WHERE [Id] IS NOT NULL)
    BEGIN
        -- 尝试插入带Id的记录，只捕获IDENTITY_INSERT未开启的特定错误（错误号544）
        BEGIN TRY
            INSERT INTO [TestTable]([Id],[ExampleField])
            SELECT [Id], [ExampleField] FROM [inserted];
        END TRY
        BEGIN CATCH
            -- 只处理IDENTITY_INSERT未开启的情况，其他错误直接抛出
            IF ERROR_NUMBER() = 544
            BEGIN
                RAISERROR('请先开启IDENTITY_INSERT再插入自定义Id值', 16, 1);
                RETURN;
            END
            THROW; -- 其他错误原样抛出
        END CATCH
    END
    ELSE
    BEGIN
        -- 没有指定Id，直接走自动生成IDENTITY的插入逻辑
        INSERT INTO [TestTable]([ExampleField])
        SELECT [ExampleField] FROM [inserted];
    END
END

这个方案的好处是：

• 绝大多数正常插入场景（不传Id）直接走else分支，完全避免了错误捕获的开销
• 只有当用户确实传了Id值时，才进入try块，而且捕获错误后会明确提示用户，不会静默替换数据，更符合预期

方案2：会话上下文标记（性能最优，但需要调用方配合） #

如果你能控制插入数据的调用代码，可以让调用方在开启IDENTITY_INSERT时，设置一个会话级的标记，触发器里读取这个标记来判断逻辑，完全不用try-catch：

比如调用方的代码：

-- 开启IDENTITY_INSERT时设置会话标记
SET IDENTITY_INSERT [TestTable] ON;
SET CONTEXT_INFO 0x01; -- 用一个二进制值标记当前会话是要插入自定义Id
INSERT INTO [TestTable]([Id],[ExampleField]) VALUES(100, 0);
SET CONTEXT_INFO 0x00; -- 重置标记
SET IDENTITY_INSERT [TestTable] OFF;

然后触发器的逻辑可以改成这样：

CREATE TRIGGER [dbo].[trTestTable_ioi] ON [dbo].[TestTable] INSTEAD OF INSERT 
AS 
BEGIN
    SET NOCOUNT ON;

    -- 读取会话上下文的标记
    DECLARE @IsIdentityInsertOn BIT = CASE WHEN CONTEXT_INFO() = 0x01 THEN 1 ELSE 0 END;

    IF @IsIdentityInsertOn = 1
    BEGIN
        -- 标记为开启状态，直接插入带Id的记录
        INSERT INTO [TestTable]([Id],[ExampleField])
        SELECT [Id], [ExampleField] FROM [inserted];
    END
    ELSE
    BEGIN
        -- 未开启的情况下，如果用户传了Id就报错
        IF EXISTS(SELECT 1 FROM inserted WHERE [Id] IS NOT NULL)
        BEGIN
            RAISERROR('未开启IDENTITY_INSERT，不能插入自定义Id值', 16, 1);
            RETURN;
        END
        -- 正常插入，自动生成Id
        INSERT INTO [TestTable]([ExampleField])
        SELECT [ExampleField] FROM [inserted];
    END
END

这个方案完全绕开了错误捕获，性能是最好的，但前提是你能控制所有插入操作的调用方，让他们配合设置会话标记。

内容的提问来源于stack exchange，提问作者Interminable