4.触发器(trigger)简单实例

1.已经介绍了触发器的定义以及适用场景，此时我们这里要实战一下：

1.首先我们设计一个触发器的场景
    这里我们有两张表，一个goods表：里面对应的是商品的各种信息(gid,gname,gmuch)
    一个订单表order:里面是用户下的订单（oid,gid,omuch）
    设计原理：每当用户下订单后，商品表中的对应的商品数量会随着订单表的数量改变而变

2. 建表
    create table goods(gid int not null,gname varchar(20),gmuch  int)engine=innodb;

    create table orders(oid int not null,gid int,omuch int ) engine=innodb;

3.插入数据
    insert into goods values(1,'cat',100),(2,'dog',200),(3,'pig'300);

2.创建触发器

语法：
create  trigger  触发器名称
after/before(触发时间）
insert /update/delete（监视事件）
on 表名(监视地址)
for each row
begin
sql1;
..
sql2;
end

这里的逻辑是：当用户下订单时，买了几个商品后，goods的总商品数量就会减少几个，因此这里用的是关键字new，且用总商品数量-new.用户下的商品数量
create  trigger  t1
after
insert 
on orders
for each row
begin
    update goods set  gmuch=gmuch-new.omuch where gid=new.gid;
end $

如果这里逻辑是：当用户取消订单时，这里是要用关键字old, 具体操作是用总商品的数量+old.用户取消的商品的数量

delimiter $
create trigger t2
after
delete
on orders
for each row
begin
　　 update goods set gmuch=gmuch+old.omuch where gid=old.gid;
end $


#以上都是对数量进行插入和删除操作，如果可以对数量进行修改操作，那么该如何？
delimiter $
creater trigger t3
before
update
on orders
for each row
begin
　　update goods set gmuch=gmuch+old.omuch -new.omuch where gid=old.gid;  ##这里的逻辑是先把旧行数据给返回去，然后再减掉新行的数据，这时就是此时商品中还剩的数量
end $

 这里有几个问题？

　　1.首先before与after到底有什么区别？

　　2.如果用户购买的数量超过了商品库存总数，会发生什么情况？

mysql> select * from goods;
+-----+-------+-------+
| gid | gname | gmuch |
+-----+-------+-------+
|   1 | cat   |    90 |
|   2 | dog   |   299 |
|   3 | pig   |   300 |
+-----+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+-----+------+-------+
| oid | gid  | omuch |
+-----+------+-------+
| 121 |    1 |     2 |
| 122 |    1 |     9 |
+-----+------+-------+
2 rows in set (0.00 sec)              ###这里我们可以发现cat的数量还剩90只，如果此时另外有个用户想买100只猫，此时会发生什么情况？

mysql> insert into orders values(123,1,100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+-----+-------+-------+
| gid | gname | gmuch |
+-----+-------+-------+
|   1 | cat   |   -10 |             ###这里看到商品库存中猫的数量变成了-10只，这样的情况在现实的数据库中是不允许的
|   2 | dog   |   299 |
|   3 | pig   |   300 |
+-----+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+-----+------+-------+
| oid | gid  | omuch |
+-----+------+-------+
| 121 |    1 |     2 |
| 122 |    1 |     9 |
| 123 |    1 |   100 |
+-----+------+-------+
3 rows in set (0.00 sec)

此时，我们可以再创建一个触发器进行如下操作

delimiter $
create  trigger t4
before
insert
on orders
for each row
begin
    declare  rnum int;      ##声明变量
    select  gmuch  into rnum from goods where gid=new.gid;      ##into也是另一种赋值操作
    if new.omuch > rnum then
        set new.omuch = rnum;
    end if;
    update  goods set gmuch=gmuch - new.omuch where gid=new.gid;
end $

我们查看结果：

mysql> select * from goods;
+-----+-------+-------+
| gid | gname | gmuch |
+-----+-------+-------+
|   1 | cat   |    90 |
|   2 | dog   |   299 |
|   3 | pig   |   300 |
+-----+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+-----+------+-------+
| oid | gid  | omuch |
+-----+------+-------+
| 121 |    1 |     2 |
| 122 |    1 |     9 |
+-----+------+-------+
2 rows in set (0.00 sec)

mysql> insert into orders values(123,1,100);
Query OK, 1 row affected (0.01 sec)

mysql> select * from goods;
+-----+-------+-------+
| gid | gname | gmuch |
+-----+-------+-------+
|   1 | cat   |     0 |         ####此时这里的库存就是0，而不是负数了！
|   2 | dog   |   299 |
|   3 | pig   |   300 |
+-----+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+-----+------+-------+
| oid | gid  | omuch |
+-----+------+-------+
| 121 |    1 |     2 |
| 122 |    1 |     9 |
| 123 |    1 |    90 |
+-----+------+-------+
3 rows in set (0.00 sec)

 补充：new和old的判断：

如下图：

触发器引用行变量