触发器

  • 需要MySQL 5：对触发器的支持是在MySQL 5中增加的。

  • 触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句，

    或位于BEGIN和END语句之间的一组语句：

    1. DELETE；
    2. INSERT；
    3. UPDATE。

    其他MySQL语句不支持触发器。

创建触发器

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

    1. 唯一的触发器名；
    2. 触发器关联的表；
    3. 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
    4. 触发器何时执行（处理之前或之后）。

  • 保持每个数据库的触发器名唯一：在MySQL 5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。

    这表示同一数据库中的两个表可具有相同名字的触发器。

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

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建名为newproduct的新触发器，对每个插入的行显示Product added
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

/*
    触发器可在一个操作发生之前或之后执行；
    AFTER INSERT表示触发器将在INSERT语句成功执行后执行。
    此触发器指定FOR EACH ROW；
    因此代码对每个插入行执行。
        
    为了测试这个触发器，使用INSERT语句添加一行或多行到products中，
    将看到对每个成功的插入，显示Product added消息。
*/

  • 仅支持表：只有表才支持触发器，视图不支持（临时表也不支持）。

  • 触发器按每个表每个事件每次的定义，每个表每个事件每次只允许一个触发器；

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

    单一触发器不能与多个事件多个表关联；

    如果需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

  • 触发器失败：如果BEFORE触发器失败，则MySQL将不执行请求的操作。

    如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。

删除触发器

  • 触发器不能更新或覆盖。

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

1
2
-- 删除一个触发器
DROP TRIGGER newproduct;

使用触发器

INSERT触发器

  • INSERT触发器在INSERT语句执行之前或之后执行。

    需要注意以下几点：

    1. 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行。
    2. 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）。
    3. 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

/*
    创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。
    在插入一个订单到orders表时，MySQL生成一个新订单号并保存到order_num；
    触发器从NEW.order_num取得这个值并返回它。
*/

-- 测试此触发器
INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);

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

  • BEFORE或AFTER？通常，将BEFORE用于数据验证和净化；

    目的是保证插入表中的数据确实是需要的数据。此也适用于UPDATE。

DELETE触发器

  • DELETE触发器在DELETE语句执行之前或之后执行。

    需要注意以下几点：

    1. 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行。
    2. OLD中的值全都是只读的，不能更新。
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 使用OLD保存将要被删除的行到一个存档表中
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;

/*
    在任意订单被删除前将执行此触发器。
    它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到archive_orders的存档表中。
    为实际使用这个例子，需要用与orders相同的列创建archive_orders表。
*/

  • 使用BEFORE DELETE触发器的优点，相对于AFTER DELETE触发器来说，

    如果由于某种原因，订单不能存档，DELETE本身将被放弃。

  • 多语句触发器：使用BEGIN END块的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

UPDATE触发器

  • UPDATE触发器在UPDATE语句执行之前或之后执行。

    需要注意以下几点：

    1. 在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前），引用一个名为NEW的虚拟表访问新更新的值。
    2. 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）。
    3. OLD中的值全都是只读的，不能更新。
1
2
3
4
5
6
7
8
-- 保证州名缩写总是大写（不管UPDATE语句中给出的是大写还是小写）
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vendor_state = Upper(NEW.vend_state);

/*
    任何数据净化都需要在UPDATE语句之前进行。
    每次更新一个行时，NEW.vendor_state中的值都用Upper(NEW.vend_state)替换。
*/

关于触发器的进一步介绍

  • 结束之前，再介绍一些使用触发器时需要记住的重点：

    1. 与其他DBMS相比，MySQL 5中支持的触发器相当初级。

      未来的MySQL版本中有一些改进和增强触发器支持的计划。

    2. 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。

      如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。

    3. 应该用触发器来保证数据的一致性（大小写、格式等）。

      在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明的进行，与客户机应用无关。

    4. 触发器的一种非常有意义的使用是创建审计跟踪。

      使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。

    5. 遗憾的是，MySQL触发器中不支持CALL语句。

      这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。