-
MySQL 使用触发器记录用户的操作日志
MySQL 使用触发器记录用户的操作日志
使用 MySQL 触发器可以记录哪些用户、什么时间对数据表进行了增、删、改操作。如果执行删除操作，则记录删除之前的数据记录；如果执行更新操作，记录更新之前的数据记录。
一、创建用户数据表（emp）和保存操作日志的表（emp_log）
-- 创建用户数据表：emp 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 '员工信息数据表'; -- 查看表结构 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_log 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) 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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
二、为 emp 表创建触发器
1、创建触发器 trigger_after_insert_emp
在 emp 表中插入记录时，把执行插入操作的用户名、操作类型（INSERT）、操作时间以及记录的内容添加到操作日志表（emp_log）中。
DROP TRIGGER IF EXISTS `trigger_after_insert_emp`; 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 ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
2、创建触发器 trigger_after_update_emp
在 emp 表中更新记录时，把执行更新操作的用户名、操作类型（UPDATE）、操作时间以及更新之前记录的内容添加到操作日志表（emp_log）中。
DROP TRIGGER IF EXISTS `trigger_after_update_emp`; 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 ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
3、创建触发器 trigger_after_delete_emp
在 emp 表中删除记录时，把执行删除操作的用户名、操作类型（DELETE）、操作时间以及删除之前记录的内容添加到操作日志表（emp_log）中。
DROP TRIGGER IF EXISTS `trigger_after_delete_emp`; 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 ;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
三、数据验证
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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
-
相关阅读:
【数据库】Navicate运行数据区sql文件 1046 no database selected
【网络安全】XSS跨站脚本攻击专题讲解
GIT 工具使用
Docker搭建Dillinger
JAVA学习日记1——JAVA简介及第一个java程序
origin中optimal cluster安装报错解决
JavaEE之CSSⅠ（前端）
1322_FreeRTOS中的队列使用的信息梳理以及初步队列的使用
痞子衡嵌入式：在i.MXRT1170上快速点亮一款全新LCD屏的方法与步骤(MIPI DSI接口)
springboot(spring)整合redis(集群)、细节、底层配置讲解
- 原文地址：https://blog.csdn.net/weixin_44377973/article/details/128173506
