MySql触发器详解

Trigger：扳机、处罚、引发

定义：触发器一类特殊的事务，用于监视某种数据操作(insert/update/delete)，并触发相关操作(insert/update/delete)

触发器可以增强程序的灵活性。

应用场合：

1. 向表添加或者删除记录时，需要在相关表进行同步操作：下单时，商品库存减少
2. 当某列数据的值与其他表种的数据有关联时：生成订单通过触发器判断累计欠款是否超过最大的额度
3. 对某张表跟踪：当有新订单产生时，需要通过相关人员处理

创建：
触发器创建的四个要素：

1. 监视地点
2. 监视时间
3. 触发时间(after/before)
4. 触发事件

需求演示：
商品表 goods
订单表 order

当下单时，对应的商品的库存量需要进行减少操作

分析：

1. 监视地点： order
2. 监视事件： insert
3. 触发时间：after
4. 触发事件：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触发器中，分为

1. 语句级触发器
2. 行级触发器

如果是行级触发器，update语句如果更新了100行记录，触发器将会被触发100次
如果是语句级触发器，update语句如果更新了100行记录，触发器仍然只会触发1次

mysql目前没有语句级别的触发器，在oracle中如果for each row 不写，则是语句级触发器

语句级触发器的使用场景：1人下单，买了5件商品，修改了五次库存，发送一条发货提醒