mysql 必知必会整理—触发器[十五]

前言

现在很多都是程序用于触发的，而不是触发器了。

正文

需要MySQL 5 对触发器的支持是在MySQL 5中增加的。因此，本章内容适用于MySQL 5或之后的版本。

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？例如：

1. 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；

2. 每当订购一个产品时，都从库存数量中减去订购的数量；

3. 无论何时删除一行，都在某个存档表中保留一个副本。

所有这些例子的共同之处是它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

1. DELETE

2. INSERT

3. UPDATE

在创建触发器时，需要给出4条信息：

1. 唯一的触发器名；

2. 触发器关联的表；

3. 触发器应该响应的活动（DELETE、INSERT或UPDATE）；

4. 触发器何时执行（处理之前或之后）。

保持每个数据库的触发器名唯一 在MySQL 5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一数据库中的两个表可具有相同名字的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且以后的MySQL版本很可能会使命名规则更为严格。因此，现在最好是在数据库范围内使用唯一的触发器名。

例子:

CREATE TRIGGER newproduct AFTER INSERT on productsfor EACH ROW INSERT INTO table1(teststring) VALUES(\'one\');

触发:

INSERT INTO products(prod_id,vend_id,prod_name,prod_price,prod_desc) VALUES(\'dsada\',\'1001\',\'dsadsa\',5.99,\'1+1\');

然后就被出发插入一条了。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。

因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。

单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

触发器失败 如果BEFORE触发器失败，则MySQL将不执行请求的操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。

删除触发器:

drop trigger newproduct;

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

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

1.在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

2.在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）

3.对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

下面举一个例子（一个实际有用的例子）。AUTO_INCREMENT列具有MySQL自动赋予的值。

CREATE TRIGGER neworder AFTER INSERT on ordersFOR EACH ROW SELECT NEW.order_num INTO @order_num;

然后使用:

INSERT INTO orders(order_date,cust_id)VALUES(NOW(),10001);

然后查询:SELECT @order_num

SELECT @order_num

注意:

BEFORE或AFTER？ 通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器。

delete 触发器:

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点：

1. 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
2. OLD中的值全都是只读的，不能更新。

下面的例子演示使用OLD保存将要被删除的行到一个存档表中：

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_ orders的存档表中。

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）为，如果由于某种原因，订单不能存档，DELETE本身将被放弃。

多语句触发器 正如所见，触发器deleteorder使用BEGIN和END语句标记触发器体。这在此例子中并不是必需的，不过也没有害处。使用BEGIN END块的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

UPDATE触发器:

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：

1. 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；

2. 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）

3. OLD中的值全都是只读的，不能更新。

何数据净化都需要在UPDATE语句之前进行，就像这个 例 子 中一样。每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。

注意

1. 与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。

2. 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。

3. 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。

4. 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。

5. 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

结

下一节事务管理。