mysql 触发器介绍「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。

触发器（Trigger）是 MySQL 中非常实用的一个功能，它可以在操作者对表进行「增删改」 之前（或之后）被触发，自动执行一段事先写好的 SQL 代码。

本教程带领大家在实践中学习，你将学到触发器在实际应用场景中的重要应用。

在这个教程中客户管理系统。在这套系统中，你需要设置在INSERT 表之前检测操作者是否输入错误数据、在 UPDATE 时，记录操作者的行为 log ，以及在DELETE 时，判断删除的信息是否符合删除规则。 这三类操作都可以使用 MySQL 触发器来实现。

下面将详细讲解触发器全部六种情况：

1. BEFORE INSERT ： 在插入数据前，检测插入数据是否符合业务逻辑，如不符合返回错误信息。
2. AFTER INSERT ： 在表 A 创建新账户后，将创建成功信息自动写入表 B 中。
3. BEFORE UPDATE ：在更新数据前，检测更新数据是否符合业务逻辑，如不符合返回错误信息。
4. AFTER UPDATE ：在更新数据后，将操作行为记录在 log 中
5. BEFORE DELETE ：在删除数据前，检查是否有关联数据，如有，停止删除操作。
6. AFTER DELETE ：删除表 A 信息后，自动删除表 B 中与表 A 相关联的信息。

先决条件

在开始之前，请确保您具备以下条件：

• 一台配置好的 Ubuntu 服务器，root 账号。
• 服务器上配置好 MySQL Server
• MySQL root 账号

创建示例数据库

我们先创建一个干净的示例数据库，方便大家可以跟随本教程一起实践。我们会在这个数据库中演示 MySQL 触发器的多种工作方式。

首先，以 root 身份登录到你的 MySQL 服务器：

mysql -u root -p

出现提示时，请输入你 MySQL root 账号的密码，然后点击 ENTER 继续。看到 mysql> 提示后，运行以下命令，创建 demo_kalacloud 数据库：

CREATE database demo_kalacloud;

输出结果

Query OK, 1 row affected (0.00 sec)

接下来，切换到新建的 demo_kalacloud 数据库：

USE demo_kalacloud;

输出结果

Database changed

接着创建一个 customers 表。我们使用这个表记录银行客户的信息。这个表包括 customer_id，customer_name，和level。咱们先把客户分为两个级别：BASIC和VIP。

create table customers( 
customer_id BIGINT PRIMARY KEY,  
customer_name VARCHAR(50), level VARCHAR(50) 
) ENGINE=INNODB;

输出结果

Query OK, 0 rows affected (0.01 sec)

接着，我们向 customers 表中添加一些客户记录。

Insert into customers (customer_id, customer_name, level )values('1','Jack Ma','BASIC'); 
Insert into customers (customer_id, customer_name, level )values('2','Robin Li','BASIC'); 
Insert into customers (customer_id, customer_name, level )values('3','Pony Ma','VIP');

分别运行三个 INSERT 命令后，命令行输出成功信息。

输出结果

Query OK, 1 row affected (0.01 sec)

我们使用 SELECT 检查一下三条信息是否已经写入表中：

Select * from customers;

输出结果

下面我们创建另一个表customer_status，用于保存 customers 表中客户的备注信息。

这个表包含 customer_id 和 status_notes 字段：

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

然后，我们再创建一个 sales 表，这个表与 customer_id 关联。保存与客户有关的销售数据。

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

输出结果：

Query OK, 0 rows affected (0.01 sec)

最后一步，我们再建一个 audit_log 表，用来记录操作员操作系统时的操作行为。方便管理员在发生问题时，有 log 可查。

Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

输出结果

Query OK, 0 rows affected (0.02 sec)

至此，已经把客户管理系统表建立完成。接下来，我们将对这个管理系统的关键节点增加对应的触发器。

1.BEFORE INSERT 触发器使用方法

作为严谨的管理系统，对任何写入系统的数据都应该提前检测，以防止错误的信息被写进去。

在写入前检测数据这个功能，我们可以使用BEFORE INSERT 触发器来实现。

在操作者对 sales 表中的sales_amount 字段进行写操作时，系统将在写入（INSERT）前检查数据是否符合规范。

我们先来看一下，创建触发器的基本语法。

DELIMITER // 
CREATE TRIGGER [触发器的名字] 
[触发器执行时机] [触发器监测的对象] 
ON [表名] FOR EACH ROW [触发器主体代码]// 
DELIMITER ;

触发器的结构包括：

• DELIMITER //：MySQL 默认分隔符是; 但在触发器中，我们使用 // 表示触发器的开始与结束。
• [触发器的名字]：这里填写触发器的名字
• [触发器执行时机]：这里设置触发器是在关键动作执行之前触发，还是执行之后触发。
• [触发器监测的对象]：触发器可以监测 INSERT、UPDATE、DELETE 的操作，当监测的命令对触发器关联的表进行操作时，触发器就被激活了。
• [表名]：将这个触发器与数据库中的表进行关联，触发器定义在表上，也附着在表上，如果这个表被删除了，那么这个触发器也随之被删除。
• FOR EACH ROW：这句表示只要满足触发器触发条件，触发器都会被执行，也就是说带上这个参数后，触发器将监测每一行对关联表操作的代码，一旦符合条件，触发器就会被触发。
• [触发器主体代码]：这里是当满足触发条件后，被触发执行的代码主体。这里可以是一句 SQL 语句，也可以是多行命令。如果是多行命令，那么这些命令要写在 BEGIN...END 之间。

注：在创建触发器主体时，还可以使用OLD和NEW 来获取 SQL 执行INSERT，UPDATE和DELETE 操作前后的写入数据。这里没看明白没关系，我们将会在接下来的实践中，展开讲解。

讲到这里，大家看了一大堆云里雾里的概念，如果没看懂，也别担心。接下来进入实践环节，只要跟着贴代码看返回结果，很快你就能够通透理解触发器了。

现在，我们来创建第一个触发器，BEFORE INSERT （在执行 insert 之前，执行触发器）。这个触发器用于监测操作者在写入 sales 表中的 sales_amount 值时，这个值是否大于 10000 ，如果大于，那么返回错误信息进行报错。

登录 MySQL Server 后，我们创建一个触发器：

DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "你输入的销售总额超过 10000 元。";
END IF//
DELIMITER ;

上面这段代码中，我们使用IF...THEN...END IF 来创建一个监测 INSERT 语句写入的值是否在限定的范围内的触发器。

这个触发器的功能时监测 INSERT 在写入sales_amount 值时，这个新增的（NEW）值是否符合条件（ > 10000）。

当操作员录入一个超过 10000 的数字，会返回如下错误信息：

SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = '你输入的销售总额超过 10000 元。';

我们来试试看，看看触发器是否已启用。

我们向 sales_amount 中插入一条 11000 的值。

Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

输出结果

ERROR 1644 (45000): 你输入的销售总额超过 10000 元。

命令行返回错误信息，这就是我们刚刚创建触发器时，填入的错误信息。与我们的设置一致。

下面我们 insert 一个值小于 10000 的数字：

Insert into  sales(sales_id, customer_id, sales_amount) values('1','1','7700');

输入值为 7700 小于设定的 10000 ，insert 命令执行成功。

Output Query OK, 1 row affected (0.01 sec)

我们调出 sales 表，看看是否插入成功：

Select * from sales;

输出确认数据在表中：

通过这张表，我们可以看到，7700 已经插入到表中。

刚刚我们演示了在执行 insert 命令前，检测某个值是否符合设定，接着我们来看在执行 insert 之后，使用触发器将不同的值保存到不同的表中。

• AFTER INSERT ： 在表 A 创建新账户后，将创建成功信息自动写入表 B 中。
• BEFORE UPDATE ：在更新数据前，检测更新数据是否符合业务逻辑，如不符合返回错误信息。
• AFTER UPDATE ：在更新数据后，将操作行为记录在 log 中
• BEFORE DELETE ：在删除数据前，检查是否有关联数据，如有，停止删除操作。
• AFTER DELETE ：删除表 A 信息后，自动删除表 B 中与表 A 相关联的信息