《MySQL必知必会》--触发器

触发器

什么是触发器

在学习本章之前，所有的SQL命令都是在需要时被我们主动执行。

而触发器便是让某条命令在特定时间发生时自动执行，具体来讲就是在某个表发生更改时自动处理。

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

  • DELETE
  • INSERT
  • UPDATE

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

创建触发器

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

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

创建触发器命令示例：

1
2
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

示例分析：

CREATE TRIGGER 用来创建新的触发器；
指定触发器触发事件：INSERT\DELETE\UPDATE；
指定在事件发生之前还是之后触发：AFTER\BEFORE；
这个触发器还指定了FOR EACH ROW因此代码对每个插入行执行；
该触发器的含义为，每当products表输入新的记录后，显示Product added。

  • 只有表才支持触发器，视图和临时表不支持；
  • 每张表最多支持6个触发器：[AFTER\BEFORE][INSERT\DELETE\UPDATE]；
  • 单一触发器不能与多个事件或多个表关联（一个触发器对应一个事件）

删除触发器

1
DROP TRIGGER newproduct;

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

使用触发器

INSERT 触发器

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

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

示例：

1
2
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

分析：

创建名为 neworder 触发器；
在 INSERT 之后执行；
每次插入新纪录自动生成 order_num。

DELETE 触发器

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

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

示例：

1
2
3
4
5
6
7
8
DELIMITER //
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//
DELIMITER ;

分析：

在订单被删除前执行此触发器；
OLD虚拟表中暂时保存将要被删除的记录；
在执行该命令之前需要首先创建一个名为archive_orders的表格，在执行该命令时，触发器将会把被删除的数据备份到该表中。

UPDATE 触发器

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

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

示例：

1
2
3
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW
SET NEW.vend_state = Upper(NEW.vend_state);

分析：
大致与上述示例相同，只不过该触发器的作用是在更新vend_state时将其首字母大写。

小结

MySQL可以支持的触发器功能有限，但是单单这些也挺有用的。