如何实现含Identity列的可维护INSTEAD OF触发器？

你遇到的核心问题是：遗留表带IDENTITY列，用INSTEAD OF INSERT触发器做数据校验时，直接SELECT *插入会触发IDENTITY列错误，写死列列表又怕后续新增列时没人更新触发器，导致数据丢值。下面给几个可行的解决思路，按维护友好度排序：

推荐方案：动态生成非IDENTITY列列表 #

利用SQL Server的系统视图，每次触发器执行时自动获取表的非IDENTITY列，构造动态SQL完成插入。新增列后完全不用修改触发器，自动适配。

代码示例：

CREATE TRIGGER ValidateLicense
ON MyHorribleLegacyTable
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    -- 执行校验逻辑
    IF UserDefinedFunction(inserted) = 1
        THROW 9283, 'The zinger is not configured to work with the wok', 16;
    IF EXISTS (SELECT * FROM inserted WHERE Name IN ('100001', 'None'))
        THROW 9284, 'Invalid Licence Number', 16;

    -- 动态获取所有非IDENTITY列的名称
    DECLARE @columns NVARCHAR(MAX), @insertSql NVARCHAR(MAX);
    SELECT @columns = STRING_AGG(QUOTENAME(c.name), ', ')
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE t.name = 'MyHorribleLegacyTable'
      AND c.is_identity = 0;

    -- 构造插入语句并执行
    SET @insertSql = N'INSERT INTO MyHorribleLegacyTable (' + @columns + N')
                       SELECT ' + @columns + N' FROM inserted;';
    EXEC sp_executesql @insertSql;
END

注意：STRING_AGG是SQL Server 2017及以上版本支持的函数，如果你的版本更低，可以用FOR XML PATH的方式拼接列名替换上述列获取逻辑。

备选方案：临时开启IDENTITY_INSERT #

这个方案能绕开列列表问题，但有明显风险，仅适合特殊场景：

CREATE TRIGGER ValidateLicense
ON MyHorribleLegacyTable
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    -- 校验逻辑不变
    IF UserDefinedFunction(inserted) = 1
        THROW 9283, 'The zinger is not configured to work with the wok', 16;
    IF EXISTS (SELECT * FROM inserted WHERE Name IN ('100001', 'None'))
        THROW 9284, 'Invalid Licence Number', 16;

    -- 临时开启IDENTITY_INSERT，允许插入IDENTITY列的值
    SET IDENTITY_INSERT MyHorribleLegacyTable ON;
    INSERT INTO MyHorribleLegacyTable SELECT * FROM inserted;
    SET IDENTITY_INSERT MyHorribleLegacyTable OFF;
END

风险提示：如果插入的数据集里带了IDENTITY列的值，会直接覆盖表的自增逻辑，容易导致主键冲突或自增序列混乱，除非能确保inserted里的IDENTITY列永远为NULL，否则不建议使用。

折中方案：绑定触发器到视图 #

先创建一个只包含非IDENTITY列的视图，把触发器绑到视图上，插入视图时就可以用SELECT *而不会触碰到IDENTITY列：

-- 创建视图，包含所有非IDENTITY列
CREATE VIEW vw_MyHorribleLegacyTable
AS
SELECT name, column2, column3, column4, ..., column224
FROM MyHorribleLegacyTable;

-- 在视图上创建INSTEAD OF触发器
CREATE TRIGGER ValidateLicense_vw
ON vw_MyHorribleLegacyTable
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    -- 校验逻辑
    IF UserDefinedFunction(inserted) = 1
        THROW 9283, 'The zinger is not configured to work with the wok', 16;
    IF EXISTS (SELECT * FROM inserted WHERE Name IN ('100001', 'None'))
        THROW 9284, 'Invalid Licence Number', 16;

    -- 插入原表，视图不含IDENTITY列，这里SELECT*无问题
    INSERT INTO MyHorribleLegacyTable SELECT * FROM inserted;
END

维护说明：后续新增列时，只需把新增列加到视图里即可，相比直接修改触发器的列列表，视图的修改更直观，也更不容易漏改。

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