多表更新触发字段变更记录写入审计日志单列表的实现方案咨询

你原有触发器的核心问题是没有逐字段判断变更，且将多个字段的变更值塞入同一行记录，无法实现每个变更字段生成独立审计日志的要求，具体实现逻辑如下：

1. 调整审计表结构（必须步骤） #

你当前的审计表缺少被修改字段名的存储字段，无法区分变更对应的字段，需先补充字段：

ALTER TABLE audit_logs 
-- 新增存储变更字段名的字段
ADD COLUMN changed_column VARCHAR(64) NOT NULL COMMENT '被修改的字段名',
-- 可选：补充变更时间字段方便溯源
ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '变更发生时间';

2. 单表触发器编写示例（以employee表为例） #

给每张需要审计的表单独编写UPDATE触发器，逐字段判断是否发生变更，每个变更字段单独插入一条审计记录：

-- 先修改语句分隔符，避免触发器内的分号提前结束语句
DELIMITER //
CREATE TRIGGER employee_audit_update_trigger
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
    -- 校验firstname是否变更，<=>是安全等于运算符，兼容NULL值比较场景
    IF NOT (OLD.firstname <=> NEW.firstname) THEN
        INSERT INTO audit_logs (employee_id, table_name, changed_column, old_value, new_value)
        VALUES (OLD.id, 'employee', 'firstname', OLD.firstname, NEW.firstname);
    END IF;
    -- 校验lastname是否变更
    IF NOT (OLD.lastname <=> NEW.lastname) THEN
        INSERT INTO audit_logs (employee_id, table_name, changed_column, old_value, new_value)
        VALUES (OLD.id, 'employee', 'lastname', OLD.lastname, NEW.lastname);
    END IF;
    -- employee表下其他需要审计的字段，都按照上面的格式补充IF判断逻辑即可
END //
-- 改回默认语句分隔符
DELIMITER ;

3. 其余5张关联表的适配规则 #

• 每张表的触发器命名建议遵循{表名}_audit_update_trigger的规则，避免冲突
• 触发器中ON后的表名替换为对应的关联表名
• 逐字段添加该表需要审计的字段的IF判断逻辑，如果关联表存储的员工ID字段不是id，替换为对应字段即可（比如关联表的员工ID字段是emp_id，就写OLD.emp_id）
• 如果需要同时审计新增、删除操作，新增对应类型的触发器即可：新增操作只有new_value，删除操作只有old_value

4. 注意事项 #

• 使用<=>安全等于运算符可以兼容字段值为NULL的场景，避免NULL值比较时逻辑失效
• 触发器使用BEFORE触发时机，避免主表数据更新失败时审计表误写入无效记录
• 建议给审计表的employee_id、table_name、updated_at字段加索引，提升后续日志查询效率

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