MySQL BEFORE DELETE触发器：如何生成带修订版的删除历史记录

你的问题核心在于触发器逻辑错误：

• 原触发器用INSERT ... SELECT从主表取数据，但主表的OLD.Revision已经在历史表中存在对应主键记录（来自之前的INSERT/UPDATE操作），触发了ON DUPLICATE KEY UPDATE，导致更新旧记录而非新增。
• 完全没必要查询主表，BEFORE DELETE触发器里的OLD对象已经包含了要删除的元件的所有数据。

正确的触发器写法 #

方案1：显式指定字段（推荐，逻辑更清晰） #

CREATE TRIGGER Altium.Discrete_bd BEFORE DELETE ON `Discrete` 
FOR EACH ROW 
INSERT INTO Altium.Discrete_History (
    Action,
    Update,
    ComponentId,
    Revision,
    PartName, -- 替换成你主表的实际字段
    Param1,
    Param2,
    -- 继续列出主表的其他所有字段
)
VALUES (
    'Delete',
    NOW(),
    OLD.ComponentId,
    -- 获取该元件历史表中的最大修订版，加1作为删除动作的修订号
    (SELECT COALESCE(MAX(Revision), 0) + 1 FROM Altium.Discrete_History WHERE ComponentId = OLD.ComponentId),
    OLD.PartName,
    OLD.Param1,
    OLD.Param2,
    -- 对应OLD的其他字段值
);

方案2：用SELECT构造新记录（适合字段较多的场景） #

CREATE TRIGGER Altium.Discrete_bd BEFORE DELETE ON `Discrete` 
FOR EACH ROW 
INSERT INTO Altium.Discrete_History 
SELECT
    'Delete' AS Action,
    NOW() AS Update,
    OLD.ComponentId,
    (SELECT COALESCE(MAX(Revision), 0) + 1 FROM Altium.Discrete_History WHERE ComponentId = OLD.ComponentId) AS Revision,
    OLD.PartName,
    OLD.Param1,
    OLD.Param2,
    -- 继续列出主表其他字段，从OLD取值
    OLD.OtherField1,
    OLD.OtherField2

关键说明 #

1. 直接用OLD对象：BEFORE DELETE阶段，要删除的行还未从主表移除，OLD包含了该行的完整数据，无需额外查询主表。
2. 生成唯一修订号：通过子查询获取该元件在历史表中的最大Revision，加1后作为删除动作的修订号，确保主键(ComponentId, Revision)不冲突，从而新增独立的删除记录。
3. 移除ON DUPLICATE KEY UPDATE：我们的目标是新增记录而非更新旧记录，这个子句完全不需要。

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