MySQL触发器实现操作日志自动记录

结果与目标

通过建立关联的触发器机制，当主表 emp 发生变动时，系统自动向 emp_log 表追加一条包含操作类型、当前用户、时间及变更前数据的记录，无需应用层干预。

准备条件

需要准备两个表结构：用于承载业务数据的员工表（emp），以及专门用于存储审计轨迹的操作日志表（emp_log）。

第1步：构建数据表结构

目的：建立原始业务表及专用的审计日志表。
入口：MySQL 命令行或管理工具（如 Navicat、DBeaver）。
参数说明：emp_log 中的字段通常与主表对应，以便保存旧数据快照。

-- 1. 创建员工业务表
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
    `emp_id` int(11) AUTO_INCREMENT COMMENT '员工id',
    `emp_name` char(50) NOT NULL DEFAULT '' COMMENT '员工姓名',
    `birth` date COMMENT '出生日期',
    `salary` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '工资',
    `comm` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '奖金',
    `phone` char(20) NOT NULL DEFAULT '' COMMENT '电话',
    `addr` varchar(200) NOT NULL DEFAULT '' COMMENT '地址',
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP comment '插入记录的时间',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '最后更新记录的时间',
    PRIMARY KEY (`emp_id`),
    KEY `idx_empname` (`emp_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '员工信息数据表';

-- 2. 创建操作日志表
DROP TABLE IF EXISTS `emp_log`;
CREATE TABLE `emp_log` (
    `emplog_id` int(11) AUTO_INCREMENT COMMENT '操作日志id',
    `operate_type` char(20) COMMENT '操作类型：insert、delete、update',
    `operate_user` char(50) COMMENT '执行操作的用户名称',
    `operate_time` datetime COMMENT '操作时间',
    -- 以下字段主要用于保存操作前的数据快照
    `emp_id` int(11),
    `emp_name` char(50),
    `birth` date,
    `salary` decimal(10,2),
    `comm` decimal(10,2),
    `phone` char(20),
    `addr` varchar(200),
    PRIMARY KEY (`emplog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '员工操作日志信息表';

真实操作顺序

MySQL 触发器在处理过程中默认使用分号作为结束符，但这会与 SQL 语句体内的逻辑产生冲突。因此，在编写触发器时，必须使用 DELIMITER // 临时改变语句结束标记。

第1步：定义数据新增时的触发器

目的：捕获插入的数据行并记录日志。
参数：使用 NEW.column_name 引用新插入的值。

DELIMITER //
CREATE TRIGGER trigger_after_insert_emp
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
    INSERT INTO emp_log(
        operate_type, operate_user, operate_time,
        emp_id, emp_name, birth, salary, comm, phone, addr
    ) VALUES (
        'INSERT', USER(), NOW(),
        NEW.emp_id, NEW.emp_name, NEW.birth, NEW.salary, NEW.comm, NEW.phone, NEW.addr
    );
END //
DELIMITER ;

第2步：定义数据更新时的触发器

目的：记录更新行为及更新前的旧数据状态。
参数：使用 OLD.column_name 引用更新前的值，这对于审计非常重要。

DELIMITER //
CREATE TRIGGER trigger_after_update_emp
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
    INSERT INTO emp_log(
        operate_type, operate_user, operate_time,
        emp_id, emp_name, birth, salary, comm, phone, addr
    ) VALUES (
        'UPDATE', USER(), NOW(),
        OLD.emp_id, OLD.emp_name, OLD.birth, OLD.salary, OLD.comm, OLD.phone, OLD.addr
    );
END //
DELIMITER ;

第3步：定义数据删除时的触发器

目的：在物理删除前，先将即将消失的数据完整归档至日志表。
注意：此时 NEW 为 NULL，所有数据均来源于 OLD。

DELIMITER //
CREATE TRIGGER trigger_after_delete_emp
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
    INSERT INTO emp_log(
        operate_type, operate_user, operate_time,
        emp_id, emp_name, birth, salary, comm, phone, addr
    ) VALUES (
        'DELETE', USER(), NOW(),
        OLD.emp_id, OLD.emp_name, OLD.birth, OLD.salary, OLD.comm, OLD.phone, OLD.addr
    );
END //
DELIMITER ;

完成判断

通过执行典型的增删改语句，检查 emp_log 表是否按预期产出了对应的审计记录。

第1步：插入测试数据并验证

执行插入后，日志表应生成 operate_type = 'INSERT' 的记录，其中包含完整的员工信息。

INSERT INTO emp(emp_name,birth,salary,comm,phone,addr) 
VALUES('刘红','1988-12-3',5000,1200,'13673521212','河南省新乡市'),
       ('王涛','1984-8-21',6000,700,'13755440012','河南省郑州市'),
       ('张静','1992-10-31',5500,800,'13073526644','河南省安阳市');

第2步：更新部分字段并验证

修改薪资时，日志记录的类型应为 UPDATE，且 salary 等快照字段应显示修改前的金额（即 5000 和 5500）。

-- 薪资低于6000的员工增加1000元
UPDATE emp SET salary = salary + 1000 WHERE salary < 6000;

第3步：批量删除并验证

删除操作后，日志表将保留被删行的最后一次完整形态。

DELETE FROM emp WHERE salary = 6000;

简要总结

本方案展示了如何基于 MySQL 原生的触发器特性构建轻量级的数据审计体系。通过分离业务表与日志表，并利用 DELIMITER 语法正确解析复合 SQL 脚本，开发者可以实现对数据生命周期全链路的透明监控。以上就是MySQL触发器实现操作日志自动记录的详细内容，更多关于MySQL数据库操作的资料请关注本站其它相关文章！