MySql 触发器（trigger）实例

矫情吗；* 2022-02-03 02:57 300阅读 0赞

### 1.创建表 ###

商品表（goods）

create table goods(
    id int,
    name varchar(20),
    num smallint
    );

订单表（orders）

create table orders(
    id int,
    goodid int,
    count smallint
    );

### 2.创建触发器 ###

增加订单时，商品数量自动减少。

delimiter $$
    drop trigger if exists addOrder $$
    create trigger addOrder after insert on orders for each row
    begin
     update goods set num = num-new.count where id =new.goodid;
    end $$
    delimiter ;

取消订单时，商品数量自动增加。

delimiter $$
    drop trigger if exists deleteOrder$$
    create trigger deleteOrder after delete on orders for each row
    begin
     update goods set num = num + old.count where id =old.goodid;
    end $$
    delimiter ;

更新订单时（更新数量），商品数量自动改变。

delimiter $$
    drop trigger if exists updateOrder$$
    create trigger updateOrder before update on orders for each row
    begin
     update goods set num = num + old.count - new.count where id =old.goodid;
    end $$
    delimiter ;

查看触发器的命令

show triggers \G

### 3.测试 ###

插入商品数据

insert into goods values(1,'cat',10),(2,'dog',5),(3,'pig',5);

![20190508174816628.png][]

提交订单

insert into orders values(100,2,3);

![2019050817500695.png][]     ![20190508175022841.png][]

修改订单（数量）

update orders set count =1 where id = 100;

![20190508175046274.png][]    ![20190508175103254.png][]

取消订单

delete from orders where id = 100;

![2019050817511822.png][]

### 4.扩展 ###

当购买的商品数量大于库存数量时，会发生什么？爆仓！！

我们需要进一步完善触发器，采用如下策略： 当购买的商品数量大于库存数量时，将购买数量更改为库存数量。

delimiter $$
    drop trigger if exists addOrder $$
    create trigger addOrder before insert on orders for each row
    begin
    declare
    maxNum int;
    select num into maxNum from goods where id = new.goodid;
    if new.count > maxNum then
     set new.count = maxNum;
     end if;
    update goods set num = num-new.count where id =new.goodid;
    end $$
    delimiter ;

提交订单

insert into orders values(100,2,8);

![20190508181634411.png][]    ![20190508181651996.png][]

同样的方法，完善updateOrder触发器。

[20190508174816628.png]: /images/20220203/c7b59919b4b343539aaf1aa5122d7779.png
[2019050817500695.png]: https://img-blog.csdnimg.cn/2019050817500695.png
[20190508175022841.png]: /images/20220203/c8c2b39d702d4b668a4017315cfc9b4a.png
[20190508175046274.png]: https://img-blog.csdnimg.cn/20190508175046274.png
[20190508175103254.png]: /images/20220203/2f419f96a8a94950a11bee15c33a9da5.png
[2019050817511822.png]: /images/20220203/64899cfaa6d64f83a6791e0a85a2b02e.png
[20190508181634411.png]: https://img-blog.csdnimg.cn/20190508181634411.png
[20190508181651996.png]: /images/20220203/3dbfb69bee5f4f168364f46cc2bf7fb4.png