史上最简单的 MySQL 教程(四十一)「触发器」

温馨提示:本系列博文已经同步到 GitHub,地址为「mysql-tutorial」,欢迎感兴趣的童鞋StarFork,纠错。

案例:网上购物,根据生产订单的类型,商品的库存量对应的进行增和减。此案例涉及两张表,分别为订单表和商品表,下单时,商品表库存减少;退单时,商品表库存增加。如何实现?

触发器

触发器trigger,是指事先为某张表绑定一段代码,当表中的某些内容发生改变(增、删、改)的时候,系统会自动触发代码并执行

触发器包含三个要素,分别为

  • 事件类型:增删改,即insertdeleteupdate
  • 触发时间:事件类型前和后,即beforeafter
  • 触发对象:表中的每一条记录(行),即整张表

每张表只能拥有一种触发时间的一种事件类型的触发器,即每张表最多可以拥有 6 种触发器

创建触发器

-- 创建触发器基本语法
delimiter 自定义符号 -- 临时修改语句结束符,在后续语句中只有遇到自定义符号才会结束语句
create trigger + 触发器名称 + 触发器时间 + 事件类型 on 表名 for each row
begin -- 代表触发器内容开始
-- 触发器内容主体,每行用分号结尾
end -- 代表触发器内容结束
自定义符号 -- 用于结束语句
delimiter ; -- 恢复语句结束符

根据上述案例的需求,我们先来创建两张表,分别为商品表goods和订单表orders,SQL 语句如下:

-- 创建商品表
create table goods(
	id int primary key auto_increment,
	name varchar(20) not null,
	price decimal(10, 2) default 0,
	inventory int comment '商品库存量'
)charset utf8;

-- 插入两条数据
insert into goods values(null, 'iPhone8', 5088, 1000), (null, 'iPhoneX', 8088, 1000);

-- 创建订单表
create table orders(
	id int primary key auto_increment,
	goods_id int not null,
	goods_number int default 1
)charset utf8;
createtables

接下来,执行如下 SQL 语句,创建触发器:

-- 创建触发器
delimiter $$ -- 临时修改语句结束符
create trigger after_order after insert on orders for each row
begin -- 触发器内容开始
	-- 触发器内容主体,每行用分号结尾
	update goods set inventory = inventory - 1 where id = 1;
end -- 触发器内容结束
$$ -- 结束语句
delimiter ; -- 恢复语句结束符
createtrigger

查询触发器

查询所有触发器或模糊匹配,

  • 基本语法show triggers + [like 'pattern'];

执行如下 SQL 语句,进行测试:

-- 查询所有触发器,\G 表示旋转
show triggers\G;
showtrigger

当然,我们也可以查询创建触发器的语句

  • 基本语法show create trigger + 触发器名称;

执行如下 SQL 语句,进行测试:

-- 查询触发器创建语句,\G 表示旋转
show create trigger after_order\G;
sqltrigger

此外,所有的触发器都会被系统保持到information_schema.triggers这张表中,执行如下 SQL,进行测试:

-- 查询触发器,\G 表示旋转
select * from information_schema.triggers\G;
oooo

使用触发器

实际上,触发器不是我们手动触发的,而是在某种情况发生的时候自动触发,例如我们上面创建的after_order触发器,当我们insert订单表的时候,该触发器自动执行。执行如下 SQL 语句,进行测试:

-- 查看商品表
select * from goods;
-- 查看订单表
select * from orders;
-- 插入订单表
insert into orders values(null, 2, 10);
-- 查看订单表
select * from orders;
-- 查看商品表
select * from goods;
usetrigger

观察上图,我们会发现:触发器确实生效了,在我们向ordersinsert数据的时候,goods表发生了变化;但是其并没有如我们期望那样执行,就算我们将goods_id设置为2goods_number设置为10,触发器操作的仍然是goods表中id1的记录且库存量只减1。且先不提这个问题,在创建触发器的时候,我们要特别注意:触发器的触发对象和事件类型,决不能同触发器主体的内容相同,防止发生死循环

修改触发器 & 删除触发器

触发器不能修改,只能删除。因此,当我们需要修改触发器的时候,唯一的方法就是:先删除,后新增。

  • 基本语法drop trigger + 触发器名称;

执行如下 SQL 语句,进行测试:

-- 删除触发器
drop trigger after_order;
-- 查询触发器
show triggers;
deletetrigger

触发器记录

触发器记录:无论触发器是否触发,只要当某种操作准备执行,系统就会将当前操作的记录的当前状态和即将执行之后的状态分别记录下来,供触发器使用。其中,当前状态被保存到old中,操作之后的状态被保存到new中。至于oldnew是什么鬼?不知道大家是否还记得查看表information_schema.triggers的时候,标红的两个字段:

  • ACTION_REFERENCE_OLD_ROW:OLD
  • ACTION_REFERENCE_NEW_ROW:NEW

其中,

  • OLD,代表是旧记录,也就是当前记录的状态,插入时没有OLD
  • NEW,代表是新记录,也就是假设操作发生之后记录的状态,删除时没有NEW

无论OLD还是 NEW,都代表记录本身,而且任何一条记录除了有数据,还有字段名。因此,使用OLDNEW的方法就是:

  • 基本语法OLD/NEW + . + 字段名

在这里,我们就能够通过触发器记录解决刚才after_order触发器的问题了。依次执行如下 SQL 语句,进行测试:

-- 创建新触发器
delimiter $$ -- 临时修改语句结束符
create trigger after_order_new after insert on orders for each row
begin -- 触发器内容开始
	-- 触发器内容主体,每行用分号结尾
	update goods set inventory = inventory - NEW.goods_number where id = NEW.goods_id;
end -- 触发器内容结束
$$ -- 结束语句
delimiter ; -- 恢复语句结束符

-- 查看新触发器
show triggers\G;
newtrigger
-- 查看商品表
select * from goods;
-- 查看订单表
select * from orders;
-- 插入订单表
insert into orders values(null, 2, 10);
-- 查看订单表
select * from orders;
-- 查看商品表
select * from goods;
testnewtrigger

如上图所示,显然after_order_new触发器按我们预期那样正确的工作啦!


温馨提示:符号[]括起来的内容,表示可选项;符号+,则表示连接的意思。


———— ☆☆☆ —— 返回 -> 史上最简单的 MySQL 教程 <- 目录 —— ☆☆☆ ————

原创声明,本文系作者授权云+社区-专栏发表,未经许可,不得转载。

如有侵权,请联系 zhuanlan_guanli@qq.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

PL/SQL --> 存储过程

存储过程子程序的一种类型,能够完成一些任务,作为schema对象存储于数据库。是一个有名字的PL/SQL代码块,支持接收或不接受参数

543
来自专栏杨建荣的学习笔记

MySQL关于数据字典的一个疑问

今天看着MySQL的数据字典,突然想到一个问题:为什么MySQL数据字典 information_schema中的表名是大写,而performance_sche...

3368
来自专栏LanceToBigData

MySQL(十一)之触发器

上一篇介绍的是比较简单的视图,其实用起来是相对比较简单的,以后有什么更多的关于视图的用法,到时候在自己补充。接下来让我们一起了解一下触发器的使用! 一、触发器概...

1958
来自专栏跟着阿笨一起玩NET

我的MYSQL学习心得(一) 简单语法

使用MYSQL有一段时间了,由于公司使用SQLSERVER和MYSQL,而且服务器数量和数据库数量都比较多

561
来自专栏黑白安全

sqlmap的tamper详解

sqlmap 是一款注入神器广为人知,里面的 tamper 常常用来绕过 WAF ,很实用的模块,但是却常常被新手忽略(比如我),今天就整理总结一下 tampe...

773
来自专栏杨建荣的学习笔记

pl/sql中的参数模式(r4笔记第54天)

在平时的工作中,可能通过pl/sql传入参数来做一些特定的操作,参数模式一般有In,out.in out这几种 比如dbms_sqltune下的PREPARE_...

2994
来自专栏后台日记

Mysql INSERT ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE是MySQL insert的一种扩展。当发现有重复的唯一索引(unique key)或者主键...

1223
来自专栏乐沙弥的世界

批量生成sqlldr文件,高速卸载数据

      SQL*Loader 是用于将外部数据进行批量高速加载的数据库的最高效工具,可用于将多种平面格式文件加载到Oracle数据库。SQL*Loader支...

532
来自专栏Java呓语

第23章、存储程序和视图

本章讨论存储的程序和视图,这些数据库对象是根据存储在服务器上供以后执行的SQL代码定义的数据库对象。

583
来自专栏维C果糖

史上最简单的 MySQL 教程(二十九)「外键(下)」

外键:foreign key,外面的键,即不在自己表中的键。如果一张表中有一个非主键的字段指向另外一张表的主键,那么将该字段称之为外键。每张表中,可以有多个外键...

3534

扫描关注云+社区