使用触发器
本章学习什么是触发器,为什么要使用触发器以及如何使用触发器。本章还将介绍创建和使用触发器的语法。
触发器
MySQL语句在需要时被执行,存储过程也是如此。有时,我们需要某条(或某些)语句在事件发生时自动执行,例如:
- 添加顾客到某个数据库表中,需要检查数据的格式是否标准。
- 订购商品时,需要从库存数量中减去订购的数量。
- 删除一行时,需要在某个存档表中保留副本。
这些例子都是需要在某个表发生更改时自动处理。这就是触发器,触发器是MySQL响应DELETE、INSERT和UPDATE语句而自动执行的一条MySQL语句(或位于BEGIN和END语句间的一组语句)。
创建触发器
创建触发器,需要4条信息:
- 唯一的触发器名。
- 触发器关联的表。
- 触发器响应的活动(DELETE、INSERT或UPDATE)。
- 触发器何时执行(处理之前或之后)。
- 保持每个数据库的触发器名唯一 MySQL5中,触发器名必须在每个表中唯一,但不是在每个数据库中唯一。这在很多DBMS中是不允许的,所以最好在数据库范围内使用唯一的触发器名。
触发器用CREATE TRIGGER语句创建:
mysql> CREATE TRIGGER newproduct AFTER INSERT ON products
-> FOR EACH ROW SELECT 'Product added' INTO @info;
Query OK, 0 rows affected (0.02 sec)
CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作前(BEFORE)或后(AFTER)执行,这里给出了AFTER INSERT,所以此触发器将在INSERT语句成功后执行。此触发器还指定FOR EACH ROW,因此代码对每个插入行执行。
可使用INSERT语句添加数据到products表中来测试触发器,使用SELECT @info;查看变量info是否被修改。
- 仅支持表 只有表才支持触发器,视图和临时表不支持。
触发器按每个表每个事件每次地定义,每次只能定义一个触发器。因此,每个表最多拥有6个触发器(每条INSERT、UPDATE和DELETE的之前和之后)。
- 触发器失败 如果BEFORE触发器失败,则MySQL将不执行请求的操作。如果BEFORE触发器或语句失败,MySQL将不执行AFTER触发器。
删除触发器
我们使用DROP TRIGGER语句来删除触发器:
mysql> DROP TRIGGER newproduct;
Query OK, 0 rows affected (0.01 sec)
触发器不能更新或覆盖。为了修改触发器,必须先删除它,再重新创建。
使用触发器
以下是每种触发器类型的差别。
INSERT触发器
INSERT触发器在INSERT语句前或后执行。需要了解以下知识:
- 在INSERT触发器代码内,可引用名为NEW的虚拟表,访问被插入的行。
- 在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许修改被插入的值)。
- 对于AUTO_INCREMENT列,NEW在INSERT执行前为0,在INSERT执行后为自动增量。
我们可以通过触发器,确定当前的自动增量:
mysql> CREATE TRIGGER neworder AFTER INSERT ON orders
-> FOR EACH ROW SELECT NEW.order_num INTO @onum;
Query OK, 0 rows affected (0.02 sec)
此语句创建名为neworder的触发器,它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时,MySQL生成一个新订单号并保存在order_num中。触发器从NEW.order_num取得这个值。
测试触发器:
mysql> INSERT INTO orders(order_date, cust_id)
-> VALUES(Now(), 10001);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @onum;
+-------+
| @onum |
+-------+
| 20010 |
+-------+
1 row in set (0.00 sec)
- BEFORE或AFTER? 通常,将BEFORE用于数据校验和净化(保证数据是确切需要的数据)。
DELETE触发器
DELETE触发器在DELETE语句前或后执行。需要了解以下知识:
- 在DELETE触发器代码内,可以引用一个名为OLD的虚拟表,访问被删除的行。
- OLD中的值都是只读的,不能更新。
使用OLD存档将删除的行:
mysql> DELIMITER //
mysql> CREATE TRIGGER deleteorder BEFORE DELETE ON orders
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO archive_orders(order_num, order_date, cust_id)
-> VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
-> END //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
此样例中,archive_orders表需要另外创建,表结构与orders一致。
使用BEFORE DELETE触发器的优点,如果订单存档失败,将放弃删除。
- 多语句触发器 deleteorder触发器使用BEGIN和END语句标记触发器体。使用BEGIN和END的优点是,可以容纳多条SQL语句。
UPDATE触发器
UPDATE触发器在UPDATE语句前或后执行。需要了解以下知识:
- 在UPDATE触发器代码中,可以引用名为OLD的虚拟表访问更新前的值,引用名为NEW的虚拟表访问更新后的值。
- 在BEFORE UPDATE触发器中,NEW中的值也可更新(允许更改用于UPDATE语句中的值)。
- OLD中的值都是只读的,不可更改。
使用UPDATE触发器保证州名缩写总是大写:
mysql> CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
-> FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
Query OK, 0 rows affected (0.01 sec)
显然,数据格式控制都需在UPDATE语句前进行。
更多介绍
使用触发器的一些重点:
- MySQL 5中支持的触发器较为初级。
- 创建触发器可能需要特殊的安全访问权限,但,触发器的执行是自动的。
- 应该用触发器保证数据的一致性(大小写、格式等)。
- 触发器的一种有意义的使用是创建审计跟踪。使用触发器把更改记录到另一个表十分轻松。
- MySQL触发器不支持CALL语句。存储过程的代码需要复制到触发器内部。