MySQL 学习笔记（8）— 触发器

最新推荐文章于 2023-04-14 17:06:34 发布

wohu007

最新推荐文章于 2023-04-14 17:06:34 发布

阅读量368

点赞数

本文链接：https://blog.csdn.net/wohu1104/article/details/96752126

版权

MySQL 专栏收录该内容

19 篇文章 12 订阅

订阅专栏

1. 触发器概念

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

INSERT
UPDATE
DELETE

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

保持每个数据库的触发器名唯一

2. 创建触发器

创建触发器的基本语法如下：

CREATE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} triggering_event ON table_name
[FOR EACH ROW] | [FOR EACH STATEMENT]
trigger_body;

其中：

CREATE TRIGGER 表示创建一个触发器，trigger_name 是触发的名称；
BEFORE、AFTER、分别表示触发的时机，INSTEAD OF表示替代触发器；
triggering_event 定义了触发事件，例如 INSERT、UPDATE、DELETE 等；
table_name 表示与触发事件相关的表；
FOR EACH ROW 表示行级触发器，FOR EACH STATEMENT 表示语句级触发器；
trigger_body 定义了触发器执行的操作，具体的实现与存储过程或函数类似。

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

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

mysql> CREATE TRIGGER new_product 
AFTER INSERT ON products 
FOR EACH ROW SELECT 'Product added';
1415 - Not allowed to return a result set from a trigger

CREATE TRIGGER 用来创建名为 new_product 的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了 AFTER INSERT，所以此触发器将在 INSERT 语句成功执行后执行。这个触发器还指定 FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本 Product added 将对每个插入的行显示一次。

为了测试这个触发器，使用 INSERT 语句添加一行或多行到 products 中，你将看到对每个成功的插入，显示 Product added 消息。


mysql> INSERT INTO products 
values ('004', '1005', 'HM', 75, 'fashion');
Query OK, 1 row affected

mysql> SELECT 'Product added';
+---------------+
| Product added |
+---------------+
| Product added |
+---------------+
1 row in set

mysql>

3. 删除触发器

为了删除一个触发器，可使用 DROP TRIGGER 语句，如下所示：

mysql> DROP TRIGGER new_product

mysql>

4. 触发器类型

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

因此，每个表最多支持 6 个触发器（每条 INSERT、UPDATE 和 DELETE 的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器。

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

5. INSERT 触发器

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

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

mysql> CREATE TRIGGER neworder 
AFTER INSERT ON orders 
FOR EACH ROW SELECT NEW.order_num into @order_num_result;
Query OK, 0 rows affected

mysql>

此代码创建一个名为 neworder 的触发器，它按照 AFTER INSERT ON orders执行。
在插入一个新订单到 orders 表时，MySQL 生成一个新订单号并保存到 order_num 中。触发器从 NEW. order_num 取得这个值并返回它。此触发器必须按照 AFTER INSERT 执行，因为在 BEFORE
INSERT 语句执行之前，新 order_num 还没有生成。对于 orders 的每次插入使用这个触发器将总是返回新的订单号。

mysql> INSERT INTO  orders (order_date, cust_id) 
VALUES (now(), 10003);
Query OK, 1 row affected

mysql> SELECT @order_num_result;
+-------------------+
| @order_num_result |
+-------------------+
|             20011 |
+-------------------+
1 row in set

mysql>

6. DELETE 触发器

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

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

mysql> CREATE TRIGGER deleteorder 
AFTER DELETE ON orders FOR EACH ROW 
BEGIN
	INSERT INTO archive_orders(order_date, cust_id)
	VALUES(OLD.order_date, OLD.cust_id)
END

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

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

7. UPDATE 触发器

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

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

mysql> CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors 
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);
Query OK, 0 rows affected

mysql> update vendors set vend_state="china" where vend_id=1006;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql>