史上最简单的 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 教程 <- 目录 —— ☆☆☆ ————

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

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏听雨堂

Mysql高效插入/更新数据

从tushare抓取到的财务数据,最开始只是想存下来,用的办法想简单点,是:插入--报错—update 但发现这个方法太蠢,异常会导致大量无效连接,改为: ...

1835
来自专栏Ryan Miao

mysql创建定时执行存储过程任务

sql语法很多,是一门完整语言。这里仅仅实现一个功能,不做深入研究。 目标:定时更新表或者清空表。 案例:曾经做过定时清空位置信息表的任务。(然而,当时并未考虑...

3517
来自专栏友弟技术工作室

python-MySQLdb的二三事

追寻 介绍 mysqldb是python操作mysql数据库的一个库.mysql的几乎所有的操作都可以实现,另外,mysqldb的一些比较的option让数据...

47212
来自专栏AILearning

Apache Zeppelin 中 Cassandra CQL 解释器

Name Class Description %cassandra CassandraInterpreter 为Apac...

2149
来自专栏java学习

数据库介绍以及使用

第1章 数据库介绍 1.1 数据库概述 l 什么是数据库 数据库就是存储数据的仓库,其本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以...

3194
来自专栏数据科学学习手札

(数据科学学习手札51)用pymysql来操控MySQL数据库

  pymysql是Python中专门用来操控MySQL数据库的模块,通过pymysql,可以编写简短的脚本来方便快捷地操控MySQL数据库,本文就将针对pym...

1014
来自专栏乐沙弥的世界

SQL Tuning Advisor(STA) 到底做了什么?

      SQL Tuing Advisor(STA) 是Automatic Tuning Optimizer(自动优化调整器)的一部分。在前面的文章使用SQ...

684
来自专栏抠抠空间

MySQL之pymysql模块

PyMySQL介绍 PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2中则使用mysqldb。 Django中...

2837
来自专栏帘卷西风的专栏

关于mysql存储过程创建动态表名及参数处理

转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog) 

603
来自专栏乐沙弥的世界

mysqldump 快速搭建特定库主从架构(GTID)

相关知识点参考 基于mysqldump搭建gtid主从 MySQL GTID 错误处理汇总 配置MySQL GTID 主从复制 使用mysqld...

730

扫码关注云+社区