23 触发器

23.1 触发器

• MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句在事件发生时自动执行，怎么办呢？例如：
  • 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写
  • 每当订购一个产品时，都从库存数量中减去订购的数量
  • 无论何时删除一行，都在某个存档表中保留一个副本
• 所有这些例子的共同之处是它们需要在某个表发生更改时自动处理。这确切地说是触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句。
  • DELETE
  • INSERT
  • UPDATE
• 其他MySQL语句不支持出发器。

23.2 创建触发器

• 在创建触发器时，需要给出4条信息：
  • 唯一的触发器名
  • 触发器关联的表
  • 触发器应该响应的活动（delete、insert、或update）
  • 触发器何时执行（处理之前或之后）
• 保持每个数据库的触发器唯一 在MySQL5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一数据库中的两个表可具有相同名字的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的。
• 触发器用create trigger语句创建。下面是一个简单的例子：

create trigger newproduct after insert on products
for each row select 'Product added';

• create trigger用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了after insert，所以此触发器将在insert语句成功执行后执行。这个触发器还指定for each row，因此代码对每个插入执行。在这个例子中，本文Product added将对每个插入的行显示一次。
• 为了测试这个触发器，使用Insert语句添加一行或多行到products中，你将看到对每个成功的插入，显示product added消息。
• 仅支持表 只有表才支持触发器，视图不支持。
• 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条insert、update和delete的之前和之后）。单一触发器不能与多个事件或多 个表关联，所以，如果你需要一个对insert和update操作 执行的触发器，则应该定义两个触发器。
• 触发器失败 如果before触发器失败，则MySQL将不执行请求的操作。此外，如果before触发器或语句本身失败，MySQL将不执行after触发器

23.3 删除触发器

• 现在，删除触发器的语法应该很明显了。为了删除一个触发器，可以使用drop trigger语句，如下所示：

drop trigger newproduct;

• 触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

23.4 使用触发器

• 在有了前面的基础知识后，我们现在来看所支持的每种触发器类型以及它们的差别。

23.4.1 insert触发器

• insert触发器在insert语句执行之前或之后执行。需要知道以下几点：
  • 在insert触发器代码内，可引用一个名为new的虚拟表，访问被插入的行
  • 在before insert触发器中，new中的值也可以被更新（允许更改被插入的值）
  • 对auto_increment列，new在isnert执行之前包含0，在insert执行之后包含新的自动生成值。
• 下面举一个例子。auto_increment列具有MySQL自动赋予的值。前面建议了几种确定新生成值的方法，但下面 是一种更好的方法：

create trigger neworder after insert on orders
for each row select NEW.order_num INTO @o_num;

• 此代码创建一个名为neworder的触发器，它按照after insert on orders执行。在插入一个新订单到orders表时 ，MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。此触发器必须按照after insert执行，因为在before insert语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返 回新的订单号。
• 为了测试这个触发器，试着插入一下新行，如下所示：

insert into orders(order_date, cust_id)
values(Now(), 10001);
SELECT @o_num;

+--------+
| @o_num |
+--------+
|  20011 |
+--------+

• orders包含3个列。order_date和cust_id必须给出，order_num由MySQL自动生成，而现在order_num还自动被返回。

23.4.2 delete触发器

• delete触发器在delete语句执行之前或之后执行。需要知道以下两点：
  • 在delete触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行
  • OLD中的值全都是只读的，不能更新
• 下面的例子演示使用OLD保存将要被删除的行到一个存档表中：

create trigger deleteorder before delete on orders
for each row
begin
    insert into archive_orders(order_num, order_date, cust_id)
    vaues(OLD.order_num, OLD.order_date, OLD.cust_id);
end;

• 在任意订单被删除前将执行此触发器。它使用一条insert语句将OLD中的值保存到一个名为archive_orders的存档表中。
• 使用before delete触发器的优点为，如果由于某种原因，订单不能存档，delete本身将被放弃。
• 多语句触发器 正如所见，触发器deleteorder使用begin和end语句标记触发器体。使用begin end块的好处是触发器能容纳多条SQL语句。

23.4.3 update触发器

• upate触发器在update语句执行之前或之后执行。需要知道以下几点：
  • 在update触发器代码中，你可以引用一个名为OLD的虚拟表访问以前的值，引用一个名为NEW的虚拟表访问新的值
  • OLD中的值全都是只读的，不能更新。
• 下面的例子保证州名缩写总是大写：

create trigger updatevendor before update on vendors
for each row set NEW.vend_state = Upper(NEW.vend_state);

• 显然，任何数据净化都需要在update语句之前进行，就像这个例子中一样。每次更新一个行时，NEW.vend_state中的值都用Upper(NEW.vend_state)替换。

25.3.4 关于触发器的进一步介绍

• 在结束之前，我们再介绍一些使用触发器时需要记住的重点。
  • 与其他DBMS相比，MySQL 5中支持的触发器相当初级。
  • 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果insert、update或delete语句能够执行，则相关的触发器也能执行
  • 应该用触发器来保证数据的一致性。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
  • 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改记录到另一个表非常容易。
  • 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。