22.MySQL之触发器

如果想看其他有关于MySQL数据库的文章，请跳转到到MySQL自学目录

本节将学习什么是触发器，为什么要使用触发器以及触发器的使用方法

触发器的概念

  我们先来看一个需求，每当订购一个产品时，都会从库存中减去相应产品的数量。这个步骤分成两步，第一步向订单表中增添一个订单，第二步在库存表中减去相应产品的数量。
  仔细分析，只要对订单表有操作时，库存表中就会有相应的操作。为什么不将第二步操作设置成自动进行的呢？
  这就涉及到了触发器。触发器就是MySQL响应delete、insert和update三个语句中任意语句而自动执行的一条MySQL语句。学过其他编程语言的同学可以将之类比成做图形化界面的事件机制。
  说更明白点就是，触发器主要用于监视某个表的delete、insert和update等更新操作，这些操作分别可以激活该表的delete、insert或update类型的触发器程序运行，从而实现数据的自动维护。
  还是不懂？那就直接看看触发器的使用吧

触发器的使用

故从触发器的定义可知，创建触发器需要4条信息：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（delete、insert和update）
• 触发器何时执行

故触发器的语法格式如下：
create trigger 触发器名
after/before insert/delete/update语句
on 表名
for each row (表行触发器，见tips)
begin
sql语句
end;
因为触发器涉及到三种相应情况，这里就按insert、delete以及update来讨论。
1. insert触发器
  insert是用来向插入数据的，必然会新增一行数据。当我们想对插入的行访问时，可以引用一个名为new的虚拟表。在before insert触发器中，new中的值可以被更新。

# 有一个空的warehouse表和goods表
mysql> delimiter $  # 重置命令结束标记
mysql> select * from goods$
+--------+------+------+
| name   | id   | num  |
+--------+------+------+
| 苹果   |    1 |   17 |
| 香蕉   |    2 |   10 |
| 西瓜   |    3 |   15 |
+--------+------+------+
3 rows in set (0.00 sec)

mysql> # delete触发器
mysql> # 当有订单被删除时，更改goods表中的数据
mysql> create trigger tg2
    -> after delete on warehouse
    -> for each row
    -> begin
    -> update goods set num = num + old.num where id = old.goodsid;
    -> end$
Query OK, 0 rows affected (0.25 sec)

mysql> # 购买3斤西瓜
mysql> insert into warehouse values (1,3,3)$
Query OK, 1 row affected (0.21 sec)

mysql> select * from warehouse$
+------+---------+------+
| id   | goodsid | num  |
+------+---------+------+
|    1 |       3 |    3 |
+------+---------+------+
1 row in set (0.00 sec)

mysql> select * from goods$
+--------+------+------+
| name   | id   | num  |
+--------+------+------+
| 苹果   |    1 |   17 |
| 香蕉   |    2 |   10 |
| 西瓜   |    3 |   12 |
+--------+------+------+
3 rows in set (0.00 sec)

2.delete触发器
  delete是用来删除数据的，必然会删除一行数据。当我们想访问被删除的行，可以引用一个名为old的虚拟表。old都是只读的，不能更新。

mysql> # delete触发器
mysql> # 当有订单被删除时，更改goods表中的数据
mysql> create trigger tg2
    -> after delete on warehouse
    -> for each row
    -> begin
    -> update goods set num = num + old.num where id = old.goodsid;
    -> end$
Query OK, 0 rows affected (0.25 sec)

mysql> # 客户返回退了id = 1的订单
mysql> delete from warehouse where id =1$
Query OK, 1 row affected (0.47 sec)

mysql> select * from warehouse$
Empty set (0.00 sec)

mysql> select * from goods$
+--------+------+------+
| name   | id   | num  |
+--------+------+------+
| 苹果   |    1 |   17 |
| 香蕉   |    2 |   10 |
| 西瓜   |    3 |   15 |
+--------+------+------+
3 rows in set (0.00 sec)

3.update触发器
  update是用来更新数据的，必然由旧的数据转变为新的数据。当我们想访问旧的数据可以引用一个名为old的虚拟表。当我们想访问新的数据时可以引用一个名为new的虚拟表。new 和 old的用法与前面的讲述一直。

mysql> # update触发器
mysql> # 当有订单被修改时，更改goods表中的数据
mysql> create trigger tg3
    -> after update on warehouse
    -> for each row
    -> begin
    -> update goods set num = num + old.num - new.num where id = old.goodsid;
    -> end$
Query OK, 0 rows affected (0.07 sec)

mysql> # 创建一个订单
mysql> insert into warehouse values (1,1,4)$
Query OK, 1 row affected (0.06 sec)

mysql> select *  from goods$
+--------+------+------+
| name   | id   | num  |
+--------+------+------+
| 苹果   |    1 |   13 |
| 香蕉   |    2 |   10 |
| 西瓜   |    3 |   15 |
+--------+------+------+
3 rows in set (0.00 sec)

mysql> update warehouse set num = 7 where id = 1;
    -> $
Query OK, 1 row affected (2.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from goods$
+--------+------+------+
| name   | id   | num  |
+--------+------+------+
| 苹果   |    1 |   10 |
| 香蕉   |    2 |   10 |
| 西瓜   |    3 |   15 |
+--------+------+------+
3 rows in set (0.00 sec)

before与after

  前面例子我们看到了after的使用方式，after代表触发器里面的命令在DML修改数据之后执行。而before代表触发器里面的命令在DML修改数据之前执行。
  举个切合实际的例子，当有订单时，我们就判断订单数量是否>5，若>5,将订单的数量置为5，防止刷单的情况。这时候就要在下订单前检查，这就用到了before。而after是在下订单之后执行的。
举个栗子

mysql> # before用法
mysql> create trigger tg4
    -> before insert on warehouse
    -> for each row
    -> begin
    -> if new.num > 5 then
    ->  set new.num = 5;
    -> end if;
    -> update goods set num = num - new.num where id = new.goodsid;
    -> end$
Query OK, 0 rows affected (0.15 sec)

mysql> insert into warehouse values(2,2,8)$
Query OK, 1 row affected (1.74 sec)

mysql> select * from goods $
+--------+------+------+
| name   | id   | num  |
+--------+------+------+
| 苹果   |    1 |   10 |
| 香蕉   |    2 |    5 |
| 西瓜   |    3 |   15 |
+--------+------+------+
3 rows in set (0.00 sec)

触发器的相关操作

1. 删除触发器
   触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，再去重新创建。
   sql语句：drop trigger 触发器名

2. 查看触发器的定义
   sql语句：show triggers\G;（查看当前数据库中所有的触发器信息）
   sql语句：show create trigger 触发器名 \G;(查看某个触发器的定义)

tips

• 触发器不涉及select语句
• for each row表示行级触发器。for each row 表示更新操作（delete、insert和update）影响的每一条记录都会执行一次触发器。目前MySQL仅支持行级触发器，不支持语句级别的触发器（如create table等语句）。
• 建议保持每个数据库的触发器名唯一。在MySQL5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。但在这种情况在其他数据库触发器名必须唯一的DBMS中是不允许的。而且以后的MySQL版本可能会使用命名规则更为严格。
• 只有表才支持触发器，视图不支持，临时表也不支持。