MySQL学习随记---触发器

概念

(1) 触发器是一个特殊的存储过程, 他是MySQL在insert, update, delete 的时候自动执行的代码块

(2) 触发器必须定义在特定的表上

(3)自动执行, 不能直接调用

作用: 监视某种情况并触发某种操作

触发器的思路:

监视 it_order 表, 如果 it_order 里面有增删改查的操作, 则自动触发 it_goods 里面增删改的操作

比如新增一个订单, 则it_goods表, 就自动减少对应商品的库存

比如取消一个订单, 则it_goods表, 就自动增加对应商品的减少的库存

触发器四要素

监视地点

table

监视事件

insert/update/delete

触发时间

after/before

触发事件

insert/update/delete

创建触发器

创建触发器语法:

create trigger trigger_name

after/before insert/update/delete on 表名

for each row

begin

SQL 语句:(触发的语句一句或多句)

end

删除触发器

语法: drop trigger 触发器名字

查看触发器

语法: show triggers

案例1: 第一个触发器, 购买一个包子, 减少一个库存

分析:

监视的地点: orders表

监视的事件: orders表的insert操作

触发的时间: orders表的insert操作之后

触发的事件: goods表减少库存的操作

CREATE TRIGGER t1
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
	UPDATE goods SET goods_number=goods_number-1 WHERE id=1;
END;

这个触发器存在问题, 永远减少id=1 商品的库存

案例2: 购买商品, 减少对应库存

注意: 如果在触发器中引用行的值:

对于insert而言, 新增的行用new来表示; 行中的每一列的值, 用 new.列名来表示

CREATE TRIGGER t1
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
	UPDATE goods SET goods_number=goods_number-new.much WHERE id=new.goods_id;
END;

案例3: 取消订单是, 减少的库存要添加回来

监视的地点: orders表

监视的事件: orders表的delete操作

触发的时间: orders表的delete操作之后

触发的事件: goods表恢复库存的操作

对于delete而言, 删除的行我们使用old来表示, 如果要引用里面的数据, 则使用old.列名来表示

CREATE TRIGGER t1
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
	UPDATE goods SET goods_number=goods_number+old.much WHERE id=old.goods_id;
END;

案例4: 修改订单时, 库存也要做对应的修改(修改购买数量和购买类型)

监视的地点: orders表

监视的事件: orders表的update操作

触发的时间: orders表的update操作之后

触发的事件: goods表修改对应库存的操作

完成修改的思路:

1. 撤销订单, goods表中的库存要恢复

2.重新下订单, goods表中的库存要减少

对于update而言, 修改前的数据用old来表示, old.列名引用被修改之前行中的值, 修改后的数据用new来表示, new.列名引用被修改之后行中的值

CREATE TRIGGER t2
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
	UPDATE goods SET goods_number=goods_number+old.much WHERE id=old.goods_id;
	UPDATE goods SET goods_number=goods_number-new.much WHERE id=new.goods_id;
END;

Before 和 After 的区别

after是先完成数据的增删改, 再触发, 触发其中的语句晚于监视的增删改, 无法影响前面的增删改动作, 就类似于先吃饭, 再付钱

before是先完成触发, 再增删改, 触发的语句优先于监视的增删改发生, 我们有机会判断修改即将发生的操作, 就类似于先付钱, 再吃饭

典型案例: 对于已下的订单, 进行判断, 如果订单的数量>5, 就认为是恶意订单, 强制把所定的商品数量改为5

分析:

监视的地点: orders表

监视的事件: orders表的insert操作

触发的时间: orders表的insert操作之前

触发的事件: 如果订单的数量>5, 强制把所定的商品数量改为5

CREATE TRIGGER t3
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
	IF new.much>5 THEN
	SET new.much=5;
	END IF;
END;

目前MySQL不支持 多个具有同一动作, 同一时间, 同一事件, 同一地点的触发器