MSSQL使用Insert触发器跟踪数据新旧值报4104错误如何解决

• 你在VALUES子句中直接引用原表[Tool Management]的字段，但未在查询中关联该表，数据库无法定位对应字段的数据源，因此抛出多部分标识符无法绑定的错误。
• INSERT类型的触发器中，系统只会生成inserted临时表存储新插入的记录，没有存储旧记录的deleted表，Insert场景下不存在旧值，所有旧值字段直接填NULL即可。
• 原写法用变量存储inserted表的数据，仅支持单次插入1条记录的场景，批量插入时会丢失数据，推荐直接使用INSERT ... SELECT语法适配批量操作。

CREATE TRIGGER ToolHistory_Insert ON [Tool Management]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO PINQC.dbo.Tool_Management_History(
        Old_Table_ID, 
        Old_Type, New_Type, 
        Old_Material, New_Material, 
        Old_Material_Description, New_Material_Description, 
        Old_Tool_Code, New_Tool_Code, 
        Old_Tool_Description, New_Tool_Description, 
        Old_Tool_Life, New_Tool_Life, 
        Changed_By, Changed_At, Change_Type
    )
    SELECT 
        SL_NO AS Old_Table_ID,
        NULL AS Old_Type, [Type] AS New_Type,
        NULL AS Old_Material, Material AS New_Material,
        NULL AS Old_Material_Description, Material_Description AS New_Material_Description,
        NULL AS Old_Tool_Code, Tool_Code AS New_Tool_Code,
        NULL AS Old_Tool_Description, Tool_Description AS New_Tool_Description,
        NULL AS Old_Tool_Life, Tool_Life AS New_Tool_Life,
        CURRENT_USER, GETDATE(), 'Inserted'
    FROM inserted;
END

• 写UPDATE触发器时，同时关联inserted和deleted表，deleted表存修改前的旧值，inserted表存修改后的新值，对应写入历史表的新旧字段即可。
• 写DELETE触发器时，从deleted表取旧值，所有新值字段填NULL，Change_Type标记为Deleted即可。

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