mysql创建触发器

首先,我们来了解一下什么是触发器,触发器,就是在对一张表数据进行增(insert),删(delete),改(update)的时候,为了保持数据的一致性,对别的表也要进行相应的数据修改。

我们都知道mysql最后事务提交后,数据是会保存到磁盘上的,那么每次在insert,delete,update时候旧数据和新数据,会在内存中生成临时的行数据,分别叫old和new。例如我要inset插入一条数据的

时候,会先将这行数据放在内存中,叫new临时表。update的时候,会先将更新之前的数据放在内存old表中,即将更新的数据放在new表中。

举个例子吧:

假设有商品goods表:商品id,商品名字,商品数量

good_id  good_name  num

    1            books         10

    2            phones       20

    3            snacks        30

有订单ord表:订单id,商品id,订单数量。

假设购买了10部手机,订单表中插入10部手机,那么商品中对应手机的数量是不是要减少10部。这完全可以通过编写高级程序代码来实现,但是高级程序要多次与数据库交互,浪费时间。

这也可以通过触发器来实现,就减少了程序与数据库的交互,节省时间。

翻译一下上面的业务,就是在ord表中插入一条订单时,要在商品表中,对应的商品的对应num数量要减少,怎么写这个触发器呢?

mysql创建触发器

 

 

1.创建插入数据时候的触发器

 

先修改mysql默认的结束符号位$

delimiter $  //结尾不要带分号

mysql创建触发器

 

代码如下:

create trigger t1
after #是在ord表上创建触发器t1,当ord表插入数据之后触发
insert
on ord
for each row
begin
    update good set good_num = good_num - new.ord_num where good_id=new.good_id;
end$

当我向订单表插入数据时,商品表数据自动更新了。这就是出发器的作用。其中for each row是固定代码,就是如果做批量更新,每行都是这样操作。

其中new.ord_num 和new.good_id 是表示在内存中的临时表,即将要插入的ord表中的那一行数据。本质是就是

ord_id  good_id   ord_num

   1           2          10

 这行数据,只不过是暂时在内存new表中中存放。

 

2.创建删除时候的触发器

如何这时候订单1,撤销了,删除了,那么对应的2号商品Phone是不是要增加10,回到20。

这就是删除触发器,

mysql创建触发器

 

 代码如下:

create trigger t2
after #是在ord表上创建触发器t1,当ord表插入数据之后触发
delete
on ord
for each row
begin
    update good set good_num = good_num + old.ord_num where good_id=old.good_id;
end$

 

3.创建更新时候的触发器

数据库的更新,其实是分两步走,第一步是先将旧数据删除,保存在old临时表,再将新数据插入,保存在new临时表中。

那么我更新订单的数量,本来是买10部手机,那我想买12部手机了,这时候商品good表应该再减2部手机,这两部是new.12 - old.10,新表保存了12,旧表保存了10

mysql创建触发器

 

 

mysql创建触发器

 

代码如下:

create trigger t3
after #是在ord表上创建触发器t1,当ord表插入数据之后触发
update
on ord
for each row
begin
    update good set good_num = good_num - (new.ord_num-old.ord_num) where good_id=old.good_id;
end$

这里where good_id = old.good_id 也可以写成 good_id = new.good_id 应为这两张表中保存的都是这种这个商品的id。

 

到此为止,我就把这三个操作的触发器都创建了一遍。

现在还没有说 在创建触发器时,before和after有什么区别?

其实要理解这个区别,一定要牢记的就是,mysql在插入操作的时候,是先将数据保存在内存new表中,再将数据写入磁盘,删除操作时,是先将要删除的数据保存在内存的old表中,再写入磁盘完成删除。

这个before和after是相对这个临时表 new或old相对而言的。定义after就是指在临时表从内存更新到磁盘之后才触发,定义before就是指,在临时表更新到磁盘之前触发。

如下面的业务场景,如果我用户购买商品,想购买25部手机,但是库存中只有10部手机,我想通过数据库来判断如果想购买的手机超过了库存量,就只让用户购买库存量这么多手机,不能超额购买。

针对这个问题怎么实现呢?

是不是当用户购买25部手机时,先去查询good表,手机还剩多少部手机,如果库存手机数大于 25部,就让购买。如果不够时,将要购买的手机数量个更新为当前的库存最大数量。

mysql创建触发器

 

 mysql创建触发器

 

 初始phone是有20部,但是要买25部,只能让购买20部,代码如下:

create trigger t4
before
insert
on ord
for each row
begin
    declare has_num int;
    select good_num into has_num from good where good_id = new.good_id;
    
    if has_num < new.ord_num then

    set new.ord_num = has_num;

    end if;

    update good set good_num =0 where good_id = new.good_id;   

end$

我这个地方调了很久,妈耶,一直是下完订单之后超过库存数量后,good表更新的值有问题,不是为0。

原来是这个触发器是before发生,与之前的after触发器冲突了。更新了两遍good表。

 

mysql创建触发器

上一篇:数据库(索引)


下一篇:【20200407】MySQL账号不规则删除导致权限错误