MySQL必知必会——第二十五章使用触发器

霖行

于 2022-02-25 21:30:54 发布

阅读量954

点赞数 2

分类专栏： MySQL必知必会文章标签： mysql 数据库 database sql 服务器

本文链接：https://blog.csdn.net/Jerome_Tong/article/details/123141529

版权

MySQL必知必会专栏收录该内容

25 篇文章 10 订阅

订阅专栏

使用触发器

本章学习什么是触发器，为什么要使用触发器以及如何使用触发器。本章还将介绍创建和使用触发器的语法。

触发器

MySQL语句在需要时被执行，存储过程也是如此。有时，我们需要某条（或某些）语句在事件发生时自动执行，例如：

添加顾客到某个数据库表中，需要检查数据的格式是否标准。
订购商品时，需要从库存数量中减去订购的数量。
删除一行时，需要在某个存档表中保留副本。

这些例子都是需要在某个表发生更改时自动处理。这就是触发器，触发器是MySQL响应DELETE、INSERT和UPDATE语句而自动执行的一条MySQL语句（或位于BEGIN和END语句间的一组语句）。

创建触发器

创建触发器，需要4条信息：

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

保持每个数据库的触发器名唯一 MySQL5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这在很多DBMS中是不允许的，所以最好在数据库范围内使用唯一的触发器名。

触发器用CREATE TRIGGER语句创建：

mysql> CREATE TRIGGER newproduct AFTER INSERT ON products
    -> FOR EACH ROW SELECT 'Product added' INTO @info;
Query OK, 0 rows affected (0.02 sec)

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作前（BEFORE）或后（AFTER）执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功后执行。此触发器还指定FOR EACH ROW，因此代码对每个插入行执行。

可使用INSERT语句添加数据到products表中来测试触发器，使用SELECT @info;查看变量info是否被修改。

仅支持表 只有表才支持触发器，视图和临时表不支持。

触发器按每个表每个事件每次地定义，每次只能定义一个触发器。因此，每个表最多拥有6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。

触发器失败 如果BEFORE触发器失败，则MySQL将不执行请求的操作。如果BEFORE触发器或语句失败，MySQL将不执行AFTER触发器。

删除触发器

我们使用DROP TRIGGER语句来删除触发器：

mysql> DROP TRIGGER newproduct;
Query OK, 0 rows affected (0.01 sec)

触发器不能更新或覆盖。为了修改触发器，必须先删除它，再重新创建。

使用触发器

以下是每种触发器类型的差别。

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 @onum;
Query OK, 0 rows affected (0.02 sec)

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

测试触发器：

mysql> INSERT INTO orders(order_date, cust_id)
    -> VALUES(Now(), 10001);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @onum;
+-------+
| @onum |
+-------+
| 20010 |
+-------+
1 row in set (0.00 sec)

BEFORE或AFTER? 通常，将BEFORE用于数据校验和净化（保证数据是确切需要的数据）。

DELETE触发器

DELETE触发器在DELETE语句前或后执行。需要了解以下知识：

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

使用OLD存档将删除的行：

mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

此样例中，archive_orders表需要另外创建，表结构与orders一致。

使用BEFORE DELETE触发器的优点，如果订单存档失败，将放弃删除。

多语句触发器 deleteorder触发器使用BEGIN和END语句标记触发器体。使用BEGIN和END的优点是，可以容纳多条SQL语句。

UPDATE触发器

UPDATE触发器在UPDATE语句前或后执行。需要了解以下知识：

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

使用UPDATE触发器保证州名缩写总是大写：

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