《MySQL必知必会》[06] 触发器

1、触发器

MySQL中的触发器概念，和Java中的事件监听器有点相似。当你想要某条语句在某个事件发生时自动执行，就要用到触发器了。

触发器能响应如下三类语句：

• DELETE
• INSERT
• UPDATE

但是，在MySQL的触发器中，不支持CALL语句，这意味着不能从触发器内部调用存储过程。只能将所需的存储过程代码复制到触发器内进行使用。

1.1 创建触发器

创建触发器 CREATE TRIGGER ：

• 唯一的触发器名称
• 应该响应的活动（DELETE、INSERT或UPDATE）
• 触发器关联的表
• 触发事件（处理之前还是之后）

e.g.

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

2

 

1

CREATE TRIGGER newproduct AFTER INSERT ON products 

2

FOR EACH ROW SELECT 'Product added';

以上，表示对标products中每个插入行，都执行 SELECT 'Product added'

触发器按每个表每个事件每次定义，且每次仅允许定义一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE、DELETE的AFTER和BEFORE），单一触发器不能与多个事件或者表关联，所以，假如你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

1.2 删除触发器：

删除触发器 DROP TRIGGER ：

e.g.

DROP TRIGGER newproduct;

1

 

1

DROP TRIGGER newproduct;

1.3 使用触发器

1.3.1 INSERT触发器

在INSERT触发器代码内，可以引用名为NEW的虚拟表，访问被插入的行，且该表在BEFORE INSERT触发器中，其表内的值允许更改。

e.g.

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

2

 

1

CREATE TRIGGER neworder AFTER INSERT ON orders

2

FOR EACH ROW SELECT NEW.order_num;

在插入一个新订单到orders表时，MySQL生成新订单号并保存到order_num中，触发器则从NEW.order_num取得这个值（对于orders的每次插入使用这个触发器将总是返回新的订单号）：

--输入
INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001);

--输出
+---------+
|order_num|
+---------+
|  20010  |
+---------+

x

 

1

--输入

2

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

3

4

--输出

5

+---------+

6

|order_num|

7

+---------+

8

|  20010  |

9

+---------+

1.3.2 DELETE触发器

在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，该表可以访问被删除的行，但是该表中的值都是只读的，不能修改：

e.g. 使用OLD保存将要被删除的行道另一个存档表中

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO archive_orders(order_num, order_date, cust_id)
  VALUE(OLD.order_num, OLD.order_date, OLD.cust_id);
END:

6

 

1

CREATE TRIGGER deleteorder BEFORE DELETE ON orders

2

FOR EACH ROW

3

BEGIN

4

  INSERT INTO archive_orders(order_num, order_date, cust_id)

5

  VALUE(OLD.order_num, OLD.order_date, OLD.cust_id);

6

END:

1.3.3 UPDATE触发器

在UPDATE触发器的代码中，通过虚拟表OLD访问更新前的值，通过虚拟表NEW访问要更新的值：

e.g. 数据净化

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

 

1

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors

2

FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);