【MySQL】使用触发器

拼搏现实的明天。 2022-11-03 15:25 195阅读 0赞

### 目录 ###

*   *  触发器
     *  创建触发器
     *  删除触发器
     *  使用触发器
     *   *  INSERT触发器
         *  DELETE触发器
         *  UPDATE触发器
         *  关于触发器的进一步介绍

## 触发器 ##

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

*  每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；
 *  每当订购一个产品时，都从库存数量中减去订购的数量；
 *  无论何时删除一行，都在某个存档表中保留一个副本。

所有这些例子的共同之处就是他们都需要在某个表发生更改时自动处理。这确切地说就是触发器。触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语  
句））:

*  DELETE；
 *  INSERT；
 *  UPDATE。

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

## 创建触发器 ##

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

*  唯一的触发器名。
 *  触发器关联的表。
 *  触发器应该响应的活动（delete、insert或update）
 *  触发器何时执行（处理之前或之后）。

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

触发器用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触发器（如果有的话）。

## 删除触发器 ##

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

DROP TRIGGER newproduct

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

## 使用触发器 ##

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

### INSERT触发器 ###

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

*  在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
 *  在BEFORE INSERT触发器中， NEW中的值也可以被更新（允许更改被插入的值）；
 *  对于AUTO\_INCREMENT列， NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

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

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取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFOREINSERT语句执行之前，新order\_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

为测试这个触发器，试着插入一下新行，如下所示：

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语句执行之前或之后执行。需要知道以下两点：

*  在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)
    	 # 引用一个名为OLD的虚拟表，访问被删除的行
        values(old.order_num,old.order_date,old.cust_id);
        
    end

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

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

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

### UPDATE触发器 ###

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

*  在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（ UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
 *  在BEFORE UPDATE触发器中， NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
 *  OLD中的值全都是只读的，不能更新。

下面的例子保证州名缩写总是大写（不管UPDATE语句中给出的是大写还是小写）：

# update触发器
    # 在更新vendors表中的vend_state值时，插入前先修改为大写格式 
    create trigger updatevendor before update on vendors 
    for each row set new.vend_state = upper(new.vend_state);
    # 更新1001供应商的州为china
    update vendors set vend_state = 'china' where vend_id =1001;
    # 查看update后数据，1001供应商对应的vend_state自动更新为大写的CHINA
    select * from vendors;

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

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

我们再介绍一些使用触发器时需要记住的重点。

*  与其他DBMS相比， MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
 *  创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、 UPDATE或DELETE语句能够执行，则相关的触发器也能执行。
 *  应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
 *  触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。
 *  遗憾的是， MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。