MySQL 使用触发器记录用户的操作日志


MySQL 使用触发器记录用户的操作日志

目录

  • MySQL 使用触发器记录用户的操作日志
    • 一、创建用户数据表(emp)和保存操作日志的表(emp_log)
    • 二、为 emp 表创建触发器
        • 1、创建触发器 trigger_after_insert_emp
        • 2、创建触发器 trigger_after_update_emp
        • 3、创建触发器 trigger_after_delete_emp
    • 三、数据验证
        • 1、在 emp 中添加数据记录
        • 2、在 emp 中更新数据记录
        • 3、在 emp 中删除数据记录

使用 MySQL 触发器可以记录哪些用户、什么时间对数据表进行了增、删、改操作。如果执行删除操作,则记录删除之前的数据记录;如果执行更新操作,记录更新之前的数据记录。

一、创建用户数据表(emp)和保存操作日志的表(emp_log)

-- 创建用户数据表:empDROP 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 '员工信息数据表';-- 查看表结构mysql> desc emp;+------------+---------------+------+-----+-------------------+------------------------+| Field      | Type          | Null | Key | Default           | Extra                  |+------------+---------------+------+-----+-------------------+------------------------+| emp_id     | int(11)       | NO   | PRI | NULL              | auto_increment         || emp_name   | char(50)      | NO   | MUL |                   |                        || birth      | date          | YES  |     | NULL              |                        || salary     | decimal(10,2) | NO   |     | 0.00              |                        || comm       | decimal(10,2) | NO   |     | 0.00              |                        || phone      | char(20)      | NO   |     |                   |                        || addr       | varchar(200)  | NO   |     |                   |                        || created_at | timestamp     | NO   |     | CURRENT_TIMESTAMP |                        || updated_at | timestamp     | NO   |    | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+------------+---------------+------+-----+-------------------+------------------------+9 rows in set (0.00 sec)-- 创建操作日志表:emp_logDROP 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) COMMENT '员工id',    `emp_name` char(50) COMMENT '员工姓名',    `birth` date COMMENT '出生日期',    `salary` decimal(10,2) COMMENT '工资',    `comm` decimal(10,2) COMMENT '奖金',    `phone` char(20) COMMENT '电话',    `addr` varchar(200) COMMENT '地址',    PRIMARY KEY (`emplog_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '员工操作日志信息表';-- 查看表结构mysql> desc emp_log;+--------------+---------------+------+-----+---------+----------------+| Field        | Type          | Null | Key | Default | Extra          |+--------------+---------------+------+-----+---------+----------------+| emplog_id    | int(11)       | NO   | PRI | NULL    | auto_increment || operate_type | char(20)      | YES  |     | NULL    |                || operate_user | char(50)      | YES  |     | NULL    |                || operate_time | datetime      | YES  |     | NULL    |                || emp_id       | int(11)       | YES  |     | NULL    |                || emp_name     | char(50)      | YES  |     | NULL    |                || birth        | date          | YES  |     | NULL    |                || salary       | decimal(10,2) | YES  |     | NULL    |                || comm         | decimal(10,2) | YES  |     | NULL    |                || phone        | char(20)      | YES  |     | NULL    |                || addr         | varchar(200)  | YES  |     | NULL    |                |+--------------+---------------+------+-----+---------+----------------+11 rows in set (0.01 sec)

二、为 emp 表创建触发器

1、创建触发器 trigger_after_insert_emp

在 emp 表中插入记录时,把执行插入操作的用户名、操作类型(INSERT)、操作时间以及记录的内容添加到操作日志表(emp_log)中。

DROP TRIGGER IF EXISTS `trigger_after_insert_emp`;delimiter //create trigger trigger_after_insert_empafter insert on empfor each rowbegininsert 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、创建触发器 trigger_after_update_emp

在 emp 表中更新记录时,把执行更新操作的用户名、操作类型(UPDATE)、操作时间以及更新之前记录的内容添加到操作日志表(emp_log)中。

DROP TRIGGER IF EXISTS `trigger_after_update_emp`;delimiter //create trigger trigger_after_update_empafter update on empfor each rowbegininsert 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、创建触发器 trigger_after_delete_emp

在 emp 表中删除记录时,把执行删除操作的用户名、操作类型(DELETE)、操作时间以及删除之前记录的内容添加到操作日志表(emp_log)中。

DROP TRIGGER IF EXISTS `trigger_after_delete_emp`;delimiter //create trigger trigger_after_delete_empafter delete on empfor each rowbegininsert 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 ;

三、数据验证

1、在 emp 中添加数据记录

/*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','河南省安阳市');*/mysql> 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','河南省安阳市');Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0-- 查询 emp 表中的数据mysql> select * from emp;+--------+----------+------------+---------+---------+-------------+--------------------+---------------------+---------------------+| emp_id | emp_name | birth      | salary  | comm    | phone       | addr               | created_at          | updated_at          |+--------+----------+------------+---------+---------+-------------+--------------------+---------------------+---------------------+|      1 | 刘红     | 1988-12-03 | 5000.00 | 1200.00 | 13673521212 | 河南省新乡市       | 2022-12-04 15:40:08 | 2022-12-04 15:40:08 ||      2 | 王涛     | 1984-08-21 | 6000.00 |  700.00 | 13755440012 | 河南省郑州市       | 2022-12-04 15:40:08 | 2022-12-04 15:40:08 ||      3 | 张静     | 1992-10-31 | 5500.00 |  800.00 | 13073526644 | 河南省安阳市       | 2022-12-04 15:40:08 | 2022-12-04 15:40:08 |+--------+----------+------------+---------+---------+-------------+--------------------+---------------------+---------------------+3 rows in set (0.00 sec)-- 查询 emp_log 表中的数据mysql> select * from emp_log;+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+| emplog_id | operate_type | operate_user   | operate_time        | emp_id | emp_name | birth      | salary  | comm    | phone       | addr               |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+|         1 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      1 | 刘红     | 1988-12-03 | 5000.00 | 1200.00 | 13673521212 | 河南省新乡市       ||         2 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      2 | 王涛     | 1984-08-21 | 6000.00 |  700.00 | 13755440012 | 河南省郑州市       ||         3 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      3 | 张静     | 1992-10-31 | 5500.00 |  800.00 | 13073526644 | 河南省安阳市       |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+3 rows in set (0.01 sec)

2、在 emp 中更新数据记录

-- 更新 emp 表中的数据(更新了两条记录)mysql> update emp set salary = salary + 1000 where salary < 6000;Query OK, 2 rows affected (0.01 sec)Rows matched: 2  Changed: 2  Warnings: 0-- 查询 emp 表中的数据mysql> select * from emp;+--------+----------+------------+---------+---------+-------------+--------------------+---------------------+---------------------+| emp_id | emp_name | birth      | salary  | comm    | phone       | addr               | created_at          | updated_at          |+--------+----------+------------+---------+---------+-------------+--------------------+---------------------+---------------------+|      1 | 刘红     | 1988-12-03 | 6000.00 | 1200.00 | 13673521212 | 河南省新乡市       | 2022-12-04 15:40:08 | 2022-12-04 15:47:56 ||      2 | 王涛     | 1984-08-21 | 6000.00 |  700.00 | 13755440012 | 河南省郑州市       | 2022-12-04 15:40:08 | 2022-12-04 15:40:08 ||      3 | 张静     | 1992-10-31 | 6500.00 |  800.00 | 13073526644 | 河南省安阳市       | 2022-12-04 15:40:08 | 2022-12-04 15:47:56 |+--------+----------+------------+---------+---------+-------------+--------------------+---------------------+---------------------+3 rows in set (0.00 sec)-- 查询 emp_log 表中的数据mysql> select * from emp_log;+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+| emplog_id | operate_type | operate_user   | operate_time        | emp_id | emp_name | birth      | salary  | comm    | phone       | addr               |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+|         1 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      1 | 刘红     | 1988-12-03 | 5000.00 | 1200.00 | 13673521212 | 河南省新乡市       ||         2 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      2 | 王涛     | 1984-08-21 | 6000.00 |  700.00 | 13755440012 | 河南省郑州市       ||         3 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      3 | 张静     | 1992-10-31 | 5500.00 |  800.00 | 13073526644 | 河南省安阳市       ||         4 | UPDATE       | root@localhost | 2022-12-04 15:47:56 |      1 | 刘红     | 1988-12-03 | 5000.00 | 1200.00 | 13673521212 | 河南省新乡市       ||         5 | UPDATE       | root@localhost | 2022-12-04 15:47:56 |      3 | 张静     | 1992-10-31 | 5500.00 |  800.00 | 13073526644 | 河南省安阳市       |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+5 rows in set (0.01 sec)

3、在 emp 中删除数据记录

-- 删除 emp 表中的数据(删除了两条记录)mysql> delete from emp where salary = 6000;Query OK, 2 rows affected (0.02 sec)-- 查询 emp 表中的数据mysql> select * from emp;+--------+----------+------------+---------+--------+-------------+--------------------+---------------------+---------------------+| emp_id | emp_name | birth      | salary  | comm   | phone       | addr               | created_at          | updated_at          |+--------+----------+------------+---------+--------+-------------+--------------------+---------------------+---------------------+|      3 | 张静     | 1992-10-31 | 6500.00 | 800.00 | 13073526644 | 河南省安阳市       | 2022-12-04 15:40:08 | 2022-12-04 15:47:56 |+--------+----------+------------+---------+--------+-------------+--------------------+---------------------+---------------------+1 row in set (0.00 sec)-- 查询 emp_log 表中的数据mysql> select * from emp_log;+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+| emplog_id | operate_type | operate_user   | operate_time        | emp_id | emp_name | birth      | salary  | comm    | phone       | addr               |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+|         1 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      1 | 刘红     | 1988-12-03 | 5000.00 | 1200.00 | 13673521212 | 河南省新乡市       ||         2 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      2 | 王涛     | 1984-08-21 | 6000.00 |  700.00 | 13755440012 | 河南省郑州市       ||         3 | INSERT       | root@localhost | 2022-12-04 15:40:08 |      3 | 张静     | 1992-10-31 | 5500.00 |  800.00 | 13073526644 | 河南省安阳市       ||         4 | UPDATE       | root@localhost | 2022-12-04 15:47:56 |      1 | 刘红     | 1988-12-03 | 5000.00 | 1200.00 | 13673521212 | 河南省新乡市       ||         5 | UPDATE       | root@localhost | 2022-12-04 15:47:56 |      3 | 张静     | 1992-10-31 | 5500.00 |  800.00 | 13073526644 | 河南省安阳市       ||         6 | DELETE       | root@localhost | 2022-12-04 15:52:21 |      1 | 刘红     | 1988-12-03 | 6000.00 | 1200.00 | 13673521212 | 河南省新乡市       ||         7 | DELETE       | root@localhost | 2022-12-04 15:52:21 |      2 | 王涛     | 1984-08-21 | 6000.00 |  700.00 | 13755440012 | 河南省郑州市       |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+7 rows in set (0.00 sec)
THE END
分享
二维码
< <上一篇

)">
下一篇>>