弄不懂MySQL触发器？详细教学与实战分析，保姆式讲解

MySQL触发器 一、什么是触发器

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete， update）时就会激活它执行。简单理解为：你执行一条sql语句，这条sql语句的执行会自动去触发执行其他的sql语句。

二、触发器的作用 三、触发器创建的四要素 四、触发器的使用语法 语法：

before/after：触发器是在增删改之前执行，还是之后执行

delete/insert/update：触发器由哪些行为触发（增、删、改）

on 表名：触发器监视哪张表的（增、删、改）操作

触发SQL代码块：执行触发器包含的SQL语句

1CREATE TRIGGER 触发器名
2BEFORE|AFTER DELETE|INSERT|UPDATE
3ON 表名 FOR EACH ROW
4BEGIN
5触发SQL代码块;
6END;

注意：触发器也是存储过程程序的一种，而触发器内部的执行SQL语句是可以多行操作的，所以在MySQL的存储过程程序中，要定义结束符。

如果MySQL存储过程不了解的小伙伴mysql触发器，可以参考此文面向MySQL存储过程编程，文章中详细讲解了MySQL存储过程的优势和语法等等，相信你会在其中得以收获。

1# 设置MySQL执行结束标志，默认为;
2delimiter //

五、触发器的基本使用 5.1 基本使用步骤

首先，我先展示一下创建的两张表，因为创建的表很简单，这里我没有提供库表操作的SQL命令。

tb_class

employee

其次，创建了一个含有update操作的存储过程

1delimiter //
2create procedure update_emp(in i int, in p int)
3begin
4    update employee set phone = p where id = i;
5end //

再创建一个触发器

分析：触发器名称为t1，触发时间为after，监视动作为update，监视表为employee表。汇总一起解释这个触发器就是：创建一个触发器名称为t1的触发器，触发器监视employee表执行update（更新）操作后，就开始执行触发器内部SQL语句update tb_class set num = num + 1 where id = 1;。

简单来说就是一个监视一个表的增、删、改操作并设置操作前后时间，在设置时间的范围内对另外一个表进行其他操作。

如果你学到这里还是一知半解，后面我会讲解一个订单与库存的数据关系，到那时候你就会明白了！

 1delimiter //
 2# 创建触发器，触发器名称为t1
 3create trigger t1
 4    # 触发器执行在update操作之后
 5    after update
 6    # 监视employee表
 7    on employee
 8    for each row
 9begin
10    # 触发执行的SQL语句
11    update tb_class set num = num + 1 where id = 1;
12end //

最后调用函数，并查看、分析结果

1call update_emp(2, 110);

触发器在此场景的作用分析

当employee表发生update操作时，触发器就对tb_class表中的num值做修改。

执行结果发现，我们在使用函数将employee表中id为2员工的phone修改为110后，触发器监视到employee表中发生了update更新操作，就执行了内部SQL语句，也就是将tb_class表中id为1的num值自增1。

5.2 查看和删除已有的触发器

查看已有触发器：show triggers

删除已有触发器：drop trigger 触发器名称

5.3 for each row

这里扩展，在oracle触发器中，触发器分为行触发器和语句触发器。也就是说，假设你监视一个修改操作，它修改了1000行代码，在Oracle中触发器会触发1000次。

在oracle中，for each row如果不写，无论update语句一次影响了多少行，都只执行一次触发事件。

而MySQL中，不支持语句级触发器，所以在MySQL中并不需要在意。

六、订单与库存关系场景

订单与库存的关系：用户下订单，意味着创建该商品订单，该商品订单中的商品数量为1，库存中的该商品数量-1。往往订单表和库存表中的数量是同时操作的，所以我们这里可以用触发器。

触发器应用：关于订单表，下订单肯定是涉及到insert插入数据数量的操作。我们可以创建一个监视订单表insert操作后执行库存表数量-1的触发器来完成订单与库存表的同时修改。

创建表，并在表中添加几条数据：

1create table goods( 2  gid int,
 3  name varchar(20),
 4  num smallint
 5);
 6create table ord( 7  oid int,
 8  gid int,
 9  much smallint
10);
11insert into goods values(1,'cat',40);
12insert into goods values(2,'dog',63);
13insert into goods values(3,'pig',87);

创建触发器

1create trigger t1 
2after
3insert
4on ord
5for each row
6begin
7 update goods set num = num - 1 where gid = 1;
8end$

该触发器意为，用户不管下什么订单，都会把商品编号为1的商品的库存减去1。

七、触发器中引用行变量 7.1 old和new对象语法 触发语句oldnew

insert

所有字段都为空

将要插入的数据

update

更新以前该行的值

更新后的值

delete

删除以前该行的值

所有字段都为空

7.2 old和new对象应用

关于old和new对象的应用，我在这里没有展开演示。只是将第八章的综合案例结合了old和new对象实现。综合案例中详细讲解了MySQL触发器的使用！

八、综合案例 8.1 创建表、插入表数据

tb_class为幼儿园班级表，其中cid为唯一主键，cname为大、中、小班班级标准，stuNo为班级标准内的学生个数。插入大、中、小班标准，初始化两名学生在大班。

tb_stu为幼儿园学生表，其中sid为唯一主键，sname为学生性名，cno为所在班级标准的外键。插入两条数据并初始化这两名学生在大班，因为我们在班级表中初始化了两名学生在大班嘛，所以要做此操作。

 1create table tb_class
 2(
 3    cid   int auto_increment
 4        primary key,
 5    cname varchar(32) not null,
 6    stuNo int         not null
 7);
 8
 9INSERT INTO temp.tb_class (cname, stuNo) VALUES ('大班', 2)
10INSERT INTO temp.tb_class (cname, stuNo) VALUES ('中班', 0)
11INSERT INTO temp.tb_class (cname, stuNo) VALUES ('小班', 0)
12
13create table tb_stu
14(
15    sid   int auto_increment
16        primary key,
17    sname varchar(32) not null,
18    cno   int         not null
19);
20
21INSERT INTO temp.tb_stu (sname, cno) VALUES ('Ziph', 1)
22INSERT INTO temp.tb_stu (sname, cno) VALUES ('Join', 1)

8.2 添加学生案例

在此表结构中，如果一位新同学来到学校学习，意味着某一个班级中会多出一名学生。假设Marry同学去小班学习，其表结构的变化为：tb_stu表中添加一条Marry的记录（注：cno = 3），tb_class表中小班记录的stuNo = 0修改为stuNo = 1

先创建一个添加学生的存储过程

1# 添加学生函数
2delimiter //
3# 创建存储过程，传入学生性名和班级参数
4create procedure add_stu(in in_sname varchar(32), in in_cno int)
5begin
6    # 插入记录
7    insert into tb_stu (sname, cno) values (in_sname, in_cno);
8end //

创建触发器

注意：在更新学生数量SQL语句中，有一段cid = new.cno的SQL语句。这里我解释一下，new代表产生的新对象，将cid主键与添加Marry记录后产生的新纪录对象的cno外键关联。（因为insert后产生的是新纪录对象嘛，所以用new）

 1# 触发器
 2# 创建名称为t_add_stu的触发器
 3create trigger t_add_stu
 4    # 设置在insert操作之后触发
 5    after
 6        insert
 7    # 监视tb_stu的insert操作
 8    on tb_stu
 9    for each row
10begin
11    # 更新学生数量(cid为tb_class表中主键，cno为tb_stu表中外键)
12    update tb_class set stuNo = stuNo + 1 where cid = new.cno;
13end //

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!