如何为SQL的MACHINES表创建变更历史记录表以追踪数据变化？

这个需求太典型了，用数据库触发器就能完美解决——不用每次导入文本文件时手动写额外的记录逻辑，触发器会自动帮你把MACHINES表的每一次变更都记录到历史表里，省心又靠谱。咱们一步步来：

首先得建一个专门存变更历史的表，比如叫MACHINES_HISTORY。这个表需要包含原表的关键标识（比如MachineId），还要能记录变更前后的数值以及变更时间、类型这些核心信息。示例SQL如下（以SQL Server为例，其他数据库语法稍有差异，后面会补充说明）：

CREATE TABLE MACHINES_HISTORY (
    HistoryId INT IDENTITY(1,1) PRIMARY KEY, -- 自增主键，每条历史记录唯一标识
    MachineId INT, -- 和原表关联的机器ID，方便追溯对应机器的变更
    -- 记录核心字段的新旧值，如果你只关心RightCount，可以只保留这一对字段
    Old_MachineName VARCHAR(100),
    New_MachineName VARCHAR(100),
    Old_ProgramName VARCHAR(100),
    New_ProgramName VARCHAR(100),
    Old_RightCount INT,
    New_RightCount INT,
    Old_FinishingTime DATETIME,
    New_FinishingTime DATETIME,
    ChangeTimestamp DATETIME DEFAULT GETDATE(), -- 自动记录变更发生的时间
    ChangeType VARCHAR(20) DEFAULT 'UPDATE' -- 标记变更类型，这里默认是更新操作
);

接下来给MACHINES表加一个AFTER UPDATE触发器——每次你从xyz.txt导入数据更新MACHINES表时，这个触发器会自动触发，把更新前的旧值（数据库存在deleted临时表）和更新后的新值（存在inserted临时表）插入到历史表里。

示例触发器代码：

CREATE TRIGGER trg_MACHINES_TrackChanges
ON MACHINES
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回额外的受影响行数消息，不干扰你的导入逻辑

    -- 从临时表取新旧数据，插入历史表
    INSERT INTO MACHINES_HISTORY (
        MachineId,
        Old_MachineName,
        New_MachineName,
        Old_ProgramName,
        New_ProgramName,
        Old_RightCount,
        New_RightCount,
        Old_FinishingTime,
        New_FinishingTime
    )
    SELECT
        d.MachineId,
        d.MachineName,
        i.MachineName,
        d.ProgramName,
        i.ProgramName,
        d.RightCount,
        i.RightCount,
        d.FinishingTime,
        i.FinishingTime
    FROM deleted d
    INNER JOIN inserted i ON d.MachineId = i.MachineId; -- 确保关联的是同一行的新旧数据
END;

• 如果你的导入操作还会插入新机器（文本文件里有新的MachineId），可以再建一个AFTER INSERT触发器，把新插入的记录也存入历史表，只需要把ChangeType设为'INSERT'即可。
• 不同数据库的小差异：比如MySQL里自增用AUTO_INCREMENT，获取当前时间用NOW()；Oracle里用SYSTIMESTAMP，触发器语法也略有不同，但核心逻辑都是利用更新前后的临时表来记录变更。
• 历史表数据量可能会越来越大，建议定期清理或归档，比如只保留最近6个月的记录，或者把旧数据移到专门的归档表。

这样设置好之后，每次你从xyz.txt导入数据更新MACHINES表时，历史表都会自动新增一条记录，你随时可以查询历史表来追踪RightCount或其他字段的变化情况啦～

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