三十、MySQL基础系列笔记之触发器的使用

触发器，见名思意，先来思考一下触发器这三字是什么意思？想一个例，老鼠偷食不小心触发了捕鼠器，捕鼠器被触发后进行了它捕鼠的操作，因此老鼠就被捕鼠器抓到了。有了这个遐想后再来看看什么触发器？

什么是触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

再来举个简单的例子？张三为了捉弄人，在门的上方挂了一个装了半桶水的水桶，用绳子将当作连接器，当李四开门后，半桶水哐的一声浇在了他的身上。

说回数据表本身的案例，那就对A表执行某个操作，触发B表执行了一个杂操作。

触发器创建四要素

监视地点（table）

监视事件（insert/update/delete）

触发时间（after/before）

触发事件（insert/update/delete）

NEW与OLD

MySQL定义了NEW和OLD，用来表示触发器所在表，触发了触发器的那一行数据，用来引用触发器中发生变化的内容：

INSERT型触发器中，NEW表示将要BEFORE或已经AFTER插入的新数据；

UPDATE型触发器中，OLD表示将要或已经被修改的原数据，NEW表示将要或已经修改的新数据；

DELETE型触发器中，OLD表示将要或已经被删除的原始数据。

如：购买了5件商品，下单后就要减少对应的库存量。对于订单表来说就是新增一行，在触发中通过NEW来获取这一行数据，从而云减少对应的库存。

创建触发器

语法

CREAET TRIGGER trigger_name trigger_time trigger_event
ON 表名 FOR EACH ROW [trigger_order] trigger_body

参数说明

trigger_name：触发名
trigger_time:触发时机，为before或after
trigger_event:触发事件，为insert/update/delete
trigger_body:触发的程序体，一条或多条sql语句
FOR FETCH ROW：一般是指行级触发，
对于受触发事件影响的每一行都要激活触发器的动作。
例如，使用 INSERT 语句向某个表中插入多行数据时，
触发器会对每一行数据的插入都执行相应的触发器动作。

触发器可以归纳为6种：

before insert、before update、before delete

after insert、after update、after delete

如：创建多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
	sql语句体
END

创建触发器

准备工作：创建goods(商品表)与订单表(order)

# 商品表
CREATE TABLE `goods`(
	`goods_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '商品ID',
    `goods_name` VARCHAR(100) NOT NULL COMMENT '商品名称',
    `inventory` INT NOT NULL DEFAULT 0 COMMENT '库存'
)ENGINE=MyISAM DEFAULT CHARSET=UTF8;
INSERT INTO `goods` values (1,'小猫',10),(2,'小狗',20),(3,'小猪',30);

# 订单表
CREATE TABLE `order`
(
    `order_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
    `goods_id` INT NOT NULL COMMENT '商品ID',
    `num` INT NOT NULL DEFAULT 0 COMMENT '商品数量'
)ENGINE=MyISAM DEFAULT CHARSET=UTF8;

创建触发器

创建一个下订单的触发器，减少相应的库存

DELIMITER //
DROP TRIGGER IF EXISTS buy //
CREATE TRIGGER buy AFTER INSERT
ON `orders` FOR EACH ROW
BEGIN 
	# 下订单号就减少相应的库存
	UPDATE `goods` SET inventory=inventory-NEW.num WHERE goods_id=NEW.goods_id; 
END//

# 下单购买2只小猫，下订单后就减少对应的商品库存
INSERT INTO `orders` values (1,1,2);

mysql> select * from orders//
+----------+----------+-----+
| order_id | goods_id | num |
+----------+----------+-----+
|        1 |        1 |   2 |
+----------+----------+-----+
1 row in set (0.00 sec)

mysql> select * from goods//
+----------+------------+-----------+
| goods_id | goods_name | inventory |
+----------+------------+-----------+
|        1 | 小猫       |         8 |
|        2 | 小狗       |        20 |
|        3 | 小猪       |        30 |
+----------+------------+-----------+
3 rows in set (0.00 sec)

查看触发器

show triggers \G

删除触发器

DROP TRIGGER [表名.]TRIGGER_NAME

DROP TRIGGER buy;

NEW、OLD案例练习

刚刚创建一个下订单的触发器。假如购买小猫的人不想要了，要退货，我们要把这个订单删掉，同时恢复小猫的原有库存。

DELIMITER //
DROP TRIGGER IF EXISTS go_back//
CREATE TRIGGER go_back AFTER DELETE
ON `orders` FOR EACH ROW
BEGIN 
	UPDATE `goods` SET inventory=inventory+OLD.num WHERE goods_id=OLD.goods_id;
END//

# 退货并恢复库存
DELETE FROM `orders` WHERE order_id = 1;

使用触发器需要注意

1）触发程序既不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但允许存储程序通过参数将数据返回触发程序。也就是存储过程或函数通过OUT/INOUT类型的参数返回触发器是可以的，但不能调用直接返回数据的过程；

2）不能在触发器中使以显式或隐式方式开始或结束事务的语句，如START TRANSACTION、COMMIT或ROLLBACK。

总结

触发器是基于行触发的，因此删除、新增或修改都有可能会激活触发器，所以不要编写过于复杂的触发器，也不要增加过多的触发器，这样不仅对数据的插入、修改、删除带来严重影响，而且可移植性不好。

2020-11-21