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

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

目录

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

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

-- 创建用户数据表：empDROPTABLEIFEXISTS`emp`;CREATETABLE`emp`(`emp_id`int(11)AUTO_INCREMENTCOMMENT'员工id',`emp_name`char(50)NOTNULLDEFAULT''COMMENT'员工姓名',`birth`dateCOMMENT'出生日期',`salary`decimal(10,2)NOTNULLDEFAULT0.00COMMENT'工资',`comm`decimal(10,2)NOTNULLDEFAULT0.00COMMENT'奖金',`phone`char(20)NOTNULLDEFAULT''COMMENT'电话',`addr`varchar(200)NOTNULLDEFAULT''COMMENT'地址',`created_at`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPcomment'插入记录的时间',`updated_at`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPcomment'最后更新记录的时间',PRIMARYKEY(`emp_id`),KEY`idx_empname`(`emp_name`))ENGINE=InnoDBDEFAULTCHARSET=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|onupdateCURRENT_TIMESTAMP|+------------+---------------+------+-----+-------------------+------------------------+9rowsinset(0.00 sec)-- 创建操作日志表：emp_logDROPTABLEIFEXISTS`emp_log`;CREATETABLE`emp_log`(`emplog_id`int(11)AUTO_INCREMENTCOMMENT'操作日志id',`operate_type`char(20)COMMENT'操作类型：insert（插入）、delete（删除）、update（更新）',`operate_user`char(50)COMMENT'执行操作的用户名称',`operate_time`datetimeCOMMENT'操作时间',`emp_id`int(11)COMMENT'员工id',`emp_name`char(50)COMMENT'员工姓名',`birth`dateCOMMENT'出生日期',`salary`decimal(10,2)COMMENT'工资',`comm`decimal(10,2)COMMENT'奖金',`phone`char(20)COMMENT'电话',`addr`varchar(200)COMMENT'地址',PRIMARYKEY(`emplog_id`))ENGINE=InnoDBDEFAULTCHARSET=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||+--------------+---------------+------+-----+---------+----------------+11rowsinset(0.01 sec)

二、为 emp 表创建触发器

1、创建触发器 trigger_after_insert_emp

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

DROPTRIGGERIFEXISTS`trigger_after_insert_emp`;delimiter//createtrigger trigger_after_insert_emp
afterinserton emp
for each rowbegininsertinto 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）中。

DROPTRIGGERIFEXISTS`trigger_after_update_emp`;delimiter//createtrigger trigger_after_update_emp
afterupdateon emp
for each rowbegininsertinto 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）中。

DROPTRIGGERIFEXISTS`trigger_after_delete_emp`;delimiter//createtrigger trigger_after_delete_emp
afterdeleteon emp
for each rowbegininsertinto 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>insertinto 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,3rows affected (0.00 sec)
Records: 3  Duplicates: 0Warnings: 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-0415:40:08|2022-12-0415:40:08||2| 王涛     |1984-08-21|6000.00|700.00|13755440012| 河南省郑州市       |2022-12-0415:40:08|2022-12-0415:40:08||3| 张静     |1992-10-31|5500.00|800.00|13073526644| 河南省安阳市       |2022-12-0415:40:08|2022-12-0415:40:08|+--------+----------+------------+---------+---------+-------------+--------------------+---------------------+---------------------+3rowsinset(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-0415:40:08|1| 刘红     |1988-12-03|5000.00|1200.00|13673521212| 河南省新乡市       ||2|INSERT| root@localhost|2022-12-0415:40:08|2| 王涛     |1984-08-21|6000.00|700.00|13755440012| 河南省郑州市       ||3|INSERT| root@localhost|2022-12-0415:40:08|3| 张静     |1992-10-31|5500.00|800.00|13073526644| 河南省安阳市       |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+3rowsinset(0.01 sec)

2、在 emp 中更新数据记录

-- 更新 emp 表中的数据（更新了两条记录）
mysql>update emp set salary = salary +1000where salary <6000;
Query OK,2rows affected (0.01 sec)Rowsmatched: 2  Changed: 2Warnings: 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-0415:40:08|2022-12-0415:47:56||2| 王涛     |1984-08-21|6000.00|700.00|13755440012| 河南省郑州市       |2022-12-0415:40:08|2022-12-0415:40:08||3| 张静     |1992-10-31|6500.00|800.00|13073526644| 河南省安阳市       |2022-12-0415:40:08|2022-12-0415:47:56|+--------+----------+------------+---------+---------+-------------+--------------------+---------------------+---------------------+3rowsinset(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-0415:40:08|1| 刘红     |1988-12-03|5000.00|1200.00|13673521212| 河南省新乡市       ||2|INSERT| root@localhost|2022-12-0415:40:08|2| 王涛     |1984-08-21|6000.00|700.00|13755440012| 河南省郑州市       ||3|INSERT| root@localhost|2022-12-0415:40:08|3| 张静     |1992-10-31|5500.00|800.00|13073526644| 河南省安阳市       ||4|UPDATE| root@localhost|2022-12-0415:47:56|1| 刘红     |1988-12-03|5000.00|1200.00|13673521212| 河南省新乡市       ||5|UPDATE| root@localhost|2022-12-0415:47:56|3| 张静     |1992-10-31|5500.00|800.00|13073526644| 河南省安阳市       |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+5rowsinset(0.01 sec)

3、在 emp 中删除数据记录

-- 删除 emp 表中的数据（删除了两条记录）
mysql>deletefrom emp where salary =6000;
Query OK,2rows 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-0415:40:08|2022-12-0415:47:56|+--------+----------+------------+---------+--------+-------------+--------------------+---------------------+---------------------+1rowinset(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-0415:40:08|1| 刘红     |1988-12-03|5000.00|1200.00|13673521212| 河南省新乡市       ||2|INSERT| root@localhost|2022-12-0415:40:08|2| 王涛     |1984-08-21|6000.00|700.00|13755440012| 河南省郑州市       ||3|INSERT| root@localhost|2022-12-0415:40:08|3| 张静     |1992-10-31|5500.00|800.00|13073526644| 河南省安阳市       ||4|UPDATE| root@localhost|2022-12-0415:47:56|1| 刘红     |1988-12-03|5000.00|1200.00|13673521212| 河南省新乡市       ||5|UPDATE| root@localhost|2022-12-0415:47:56|3| 张静     |1992-10-31|5500.00|800.00|13073526644| 河南省安阳市       ||6|DELETE| root@localhost|2022-12-0415:52:21|1| 刘红     |1988-12-03|6000.00|1200.00|13673521212| 河南省新乡市       ||7|DELETE| root@localhost|2022-12-0415:52:21|2| 王涛     |1984-08-21|6000.00|700.00|13755440012| 河南省郑州市       |+-----------+--------------+----------------+---------------------+--------+----------+------------+---------+---------+-------------+--------------------+7rowsinset(0.00 sec)