Trigger:扳机、处罚、引发
定义:触发器一类特殊的事务,用于监视某种数据操作(insert/update/delete),并触发相关操作(insert/update/delete)
触发器可以增强程序的灵活性。
应用场合:
- 向表添加或者删除记录时,需要在相关表进行同步操作:下单时,商品库存减少
- 当某列数据的值与其他表种的数据有关联时:生成订单通过触发器判断累计欠款是否超过最大的额度
- 对某张表跟踪:当有新订单产生时,需要通过相关人员处理
创建:
触发器创建的四个要素:
![file](https://i-blog.csdnimg.cn/blog_migrate/0f1fcaa10f3d66e99e4bf3f9f51377b3.png)
- 监视地点
- 监视时间
- 触发时间(after/before)
- 触发事件
需求演示:
商品表 goods
订单表 order
当下单时,对应的商品的库存量需要进行减少操作
分析:
- 监视地点: order
- 监视事件: insert
- 触发时间:after
- 触发事件:update goods
创建测试表:
create database if not exists trigger_test;
use trigger_test;
create table `goods` (
id int primary key auto_increment,
name varchar(20) comment '商品名称',
stock int comment '商品库存'
)
charset utf8
engine myisam;
create table `order` (
id int primary key auto_increment,
g_id int comment '订单购买的商品id',
num int comment '订单购买的商品数量'
)
charset utf8
engine myisam;
编写触发器:
-- 编写触发器
delimiter //
-- 添加订单
drop trigger if exists order_create//
create trigger order_create
after insert
on `order`
for each row
begin
update goods set stock = stock - new.num where id = new.g_id;
end//
-- 撤销订单
drop trigger if exists order_cancel//
create trigger order_cancel
after delete
on `order`
for each row
begin
update goods set stock = stock + old.num where id = old.g_id;
end //
-- 修改订单数量
delimiter ;
-- 测试买两只小猫
insert into `order` values (1, 1, 2);
select * from goods where id = 1; # 发现小猫的库存减少了2
-- 取消订单
delete from `order` where id = 1;
select * from goods where id = 1; # 发现小猫的库存增加了2
注意:
要获取插入/修改的新行,需要引用行变量new
要获取删除/修改的旧行,需要引用行变量old
限制购买数量:
# 上面的触发器触发时间全部为after,当我们需要监控商品的库存量的时候,比如当超过库存后,只能购买最大库存量,此时就需要before
# before可以在记录插入之前修改记录的值,从而限制购买数量
delimiter //
drop trigger if exists order_create//
create trigger order_create
before
insert
on `order`
for each row
begin
declare g_stock int;
# 获取库存量, 使用into将查询结果复制给变量中
select stock into g_stock from goods where id = new.g_id;
# 如果库存不够,就将购买数量设置位最大的库存
if g_stock < new.num then
set new.num = g_stock ;
end if ;
update goods set stock = stock - new.num where id = new.g_id;
end;
delimiter ;
-- 测试,购买100只猫
select * from goods;
insert into `order` values (3, 1, 100);
select * from `order`; # 最后只买了最大的库存量
for each row:
mysql触发器中的for each row是做什么的?
在oracle触发器中,分为
- 语句级触发器
- 行级触发器
如果是行级触发器,update语句如果更新了100行记录,触发器将会被触发100次
如果是语句级触发器,update语句如果更新了100行记录,触发器仍然只会触发1次
mysql目前没有语句级别的触发器,在oracle中如果for each row 不写,则是语句级触发器
语句级触发器的使用场景:1人下单,买了5件商品,修改了五次库存,发送一条发货提醒