mysql系列XIII-触发器

发表于 更新于 分类于 ， Changyan： 本文字数： 1.8k 阅读时长 ≈ 6 分钟

触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型NEW 和 OLD
INSERT型触发器NEW 表示将要或者已经新增的数据
UPDATE型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE型触发器OLD 表示将要或者已经删除的数据

语法

创建

1
2
3
4
5
6
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt;
END;

查看

1
SHOW TRIGGERS ;

删除

1
DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库 。

案例

通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中, 包含增加,修改 , 删除 ;

表结构准备:

1
2
3
4
5
6
7
8
9
-- 准备工作 : 日志表 user_logs
create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;

插入数据触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop trigger if exists user_insert_trigger;
create trigger user_insert_trigger after insert on tb_user for each row
begin
	insert into user_logs(operation, operate_time, operate_id, operate_params) values('insert', now(), new.id, concat('插入的内容为：id=', new.id, ', name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession, ', age=', new.age, ', gender=', new.gender, ', status=', new.status));
end


mysql> select * from user_logs;
+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                                                                                              |
+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------------------------+
|  1 | insert    | 2022-08-08 15:57:16 |         25 | 插入的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=工程造价, age=29, gender=1, status=4 |
+----+-----------+---------------------+------------+-----------------------------------------------------------------------------------------------------------------------------+
1 row 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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
drop trigger if exists user_update_trigger;
create trigger user_update_trigger after update on tb_user for each row
begin
	insert into user_logs(operation, operate_time, operate_id, operate_params) values('update', now(), old.id, concat('更新前的内容为：id=', old.id, ', name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession, ', age=', old.age, ', gender=', old.gender, ', status=', old.status, '；更新后的内容为：id=', new.id, ', name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession, ', age=', new.age, ', gender=', new.gender, ', status=', new.status));
end;



mysql> UPDATE `studydata`.`tb_user` SET `name` = '孙尚香', `phone` = '17799993333', `email` = '33333844@qq.com', `profession` = '舞蹈', `age` = 29, `gender` = '1', `status` = '4', `createtime` = '2103-05-26 00:00:00' WHERE `id` = 25;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from user_logs order by id desc limit 1 \G;
*************************** 1. row ***************************
            id: 2
     operation: update
  operate_time: 2022-08-08 16:04:42
    operate_id: 25
operate_params: 更新前的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=工程造价, age=29, gender=1, status=4；更新后的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=舞蹈, age=29, gender=1, status=4
1 row in set (0.00 sec)



mysql> update tb_user set profession='客服' where id>20;
Query OK, 5 rows affected (0.06 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from user_logs;
+----+-----------+---------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                                                                                                                                                                                                                               |
+----+-----------+---------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | insert    | 2022-08-08 15:57:16 |         25 | 插入的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=工程造价, age=29, gender=1, status=4                                                                                                                                  |
|  2 | update    | 2022-08-08 16:04:42 |         25 | 更新前的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=工程造价, age=29, gender=1, status=4；更新后的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=舞蹈, age=29, gender=1, status=4     |
|  3 | update    | 2022-08-08 16:08:25 |         21 | 更新前的内容为：id=21, name=典韦, phone=17799990020, email=ycaunanjian@163.com, profession=城市规划, age=52, gender=1, status=2；更新后的内容为：id=21, name=典韦, phone=17799990020, email=ycaunanjian@163.com, profession=客服, age=52, gender=1, status=2 |
|  4 | update    | 2022-08-08 16:08:25 |         22 | 更新前的内容为：id=22, name=廉颇, phone=17799990021, email=lianpo321@126.com, profession=土木工程, age=19, gender=1, status=3；更新后的内容为：id=22, name=廉颇, phone=17799990021, email=lianpo321@126.com, profession=客服, age=19, gender=1, status=3     |
|  5 | update    | 2022-08-08 16:08:25 |         23 | 更新前的内容为：id=23, name=后羿, phone=17799990022, email=altycj2000@139.com, profession=城市园林, age=20, gender=1, status=0；更新后的内容为：id=23, name=后羿, phone=17799990022, email=altycj2000@139.com, profession=客服, age=20, gender=1, status=0   |
|  6 | update    | 2022-08-08 16:08:25 |         24 | 更新前的内容为：id=24, name=姜子牙, phone=17799990023, email=37483844@qq.com, profession=工程造价, age=29, gender=1, status=4；更新后的内容为：id=24, name=姜子牙, phone=17799990023, email=37483844@qq.com, profession=客服, age=29, gender=1, status=4     |
|  7 | update    | 2022-08-08 16:08:25 |         25 | 更新前的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=舞蹈, age=29, gender=1, status=4；更新后的内容为：id=25, name=孙尚香, phone=17799993333, email=33333844@qq.com, profession=客服, age=29, gender=1, status=4         |
+----+-----------+---------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

删除数据触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop trigger if exists user_delete_trigger;
create trigger user_delete_trigger after delete on tb_user for each row
begin
	insert into user_logs(operation, operate_time, operate_id, operate_params) values('delete', now(), old.id, concat('删除的内容为：id=', old.id, ', name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession, ', age=', old.age, ', gender=', old.gender, ', status=', old.status));
end;


mysql> delete from tb_user where id = 24;
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_logs order by id desc limit 1;
+----+-----------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                                                                                          |
+----+-----------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------+
|  8 | delete    | 2022-08-08 16:18:43 |         24 | 删除的内容为：id=24, name=姜子牙, phone=17799990023, email=37483844@qq.com, profession=客服, age=29, gender=1, status=4 |
+----+-----------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Mysql系列文章

  1. mysql系列I-概述

  2. mysql系列II-sql语法

    ①. mysql系列II-sql语法-(1)-概述

    ②. mysql系列II-sql语法-(2)-DDL

    ③. mysql系列II-sql语法-(3)-DML

    ④. mysql系列II-sql语法-(4)-DQL

    ⑤. mysql系列II-sql语法-(5)-DCL

  3. mysql系列III-函数

  4. mysql系列IV-约束

  5. mysql系列V-多表查询

  6. mysql系列VI-事务

  7. mysql系列VII-存储引擎

  8. mysql系列VIII-索引

    ①. mysql系列VIII-索引(1)-概述

    ②. mysql系列VIII-索引(2)-结构

    ③. mysql系列VIII-索引(3)-分类

    ④. mysql系列VIII-索引(4)-语法

    ⑤. mysql系列VIII-索引(5)-SQL性能分析

    ⑥. mysql系列VIII-索引(6)-索引使用

    ⑦. mysql系列VIII-索引(7)-设计原则

  9. mysql系列IX-SQL优化

  10. mysql系列X-视图

  11. mysql系列XI-存储过程

  12. mysql系列XII-存储函数

  13. mysql系列XIII-触发器

  14. mysql系列XIV-锁