MySQL触发器多次触发：如何实现单次操作仅保留最后一条日志

针对你遇到的触发器过度触发、需要保留单次操作最后一条日志的问题，以下是几个无需应用层（JS/PHP）介入的纯SQL方案：

方案1：利用事务ID区分单次操作 #

同一批次的参数更新（DELETE+INSERT）必然在同一个数据库事务中，通过给日志表添加事务ID字段，触发器可以在插入新日志前清理同事务、同product_id的旧日志，最终仅保留该事务的最后一条记录。

步骤1：给日志表添加事务ID字段 #

根据你使用的数据库选择对应语法：

• PostgreSQL：

ALTER TABLE product_log ADD COLUMN tx_id bigint;

• MySQL：

ALTER TABLE product_log ADD COLUMN tx_id varchar(64);

步骤2：编写触发器函数 #

PostgreSQL版本：

CREATE OR REPLACE FUNCTION log_product_change()
RETURNS TRIGGER AS $$
BEGIN
    -- 清理同product_id、同事务的旧日志
    DELETE FROM product_log
    WHERE product_id = NEW.product_id
      AND tx_id = txid_current();
    
    -- 插入新日志，绑定当前事务ID
    INSERT INTO product_log (product_id, change_content, tx_id, created_at)
    VALUES (NEW.product_id, '参数更新', txid_current(), NOW());
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

MySQL版本：

DELIMITER //
CREATE TRIGGER log_product_change
AFTER INSERT OR DELETE ON product_params
FOR EACH ROW
BEGIN
    -- 清理同product_id、同会话事务的旧日志（用连接ID+事务隔离级别标识）
    DELETE FROM product_log
    WHERE product_id = NEW.product_id
      AND tx_id = CONCAT(CONNECTION_ID(), '-', @@tx_isolation);
    
    -- 插入新日志
    INSERT INTO product_log (product_id, change_content, tx_id, created_at)
    VALUES (NEW.product_id, '参数更新', CONCAT(CONNECTION_ID(), '-', @@tx_isolation), NOW());
END //
DELIMITER ;

步骤3：绑定触发器到参数表 #

-- PostgreSQL
CREATE TRIGGER trigger_product_param_change
AFTER INSERT OR DELETE ON product_params
FOR EACH ROW EXECUTE FUNCTION log_product_change();

-- MySQL
-- 创建触发器时已完成绑定，无需额外操作

方案2：使用MERGE语句实现“插入或更新” #

如果你的数据库支持MERGE（如PostgreSQL 15+、MySQL 8+），可以直接在触发器中用MERGE替代INSERT，每次触发时更新同product_id的最新日志记录，而非插入新行：

PostgreSQL版本： #

CREATE OR REPLACE FUNCTION log_product_change()
RETURNS TRIGGER AS $$
BEGIN
    MERGE INTO product_log AS target
    USING (SELECT NEW.product_id AS product_id, '参数更新' AS change_content, NOW() AS created_at) AS source
    ON target.product_id = source.product_id
    WHEN MATCHED THEN
        UPDATE SET change_content = source.change_content, created_at = source.created_at
    WHEN NOT MATCHED THEN
        INSERT (product_id, change_content, created_at)
        VALUES (source.product_id, source.change_content, source.created_at);
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

这个方案无需额外字段，但注意：如果同一product_id有跨事务的频繁更新，会覆盖之前的日志——适合你只关心最新一次操作日志的场景。

为什么lag()函数不适用？ #

lag()是窗口函数，需要基于完整的结果集进行计算，而触发器的上下文是单条触发记录，无法直接在INSERT语句中用它关联同product_id的历史日志。即使嵌套查询实现，也会因多次触发带来性能损耗，且时间差判断存在误判风险（比如不同事务的操作刚好时间间隔极短）。

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