如何在Ubuntu18.04上管理和使用MySQL数据库触发器

作为 Write for DOnations 计划的一部分，作者选择了 Apache 软件基金会来接受捐赠。

介绍

在 MySQL 中，trigger 是用户定义的 SQL 命令，在 INSERT、DELETE 或 UPDATE 期间自动调用手术。触发器代码与表相关联，一旦删除表就会被销毁。您可以指定触发动作时间，并设置它是在定义的数据库事件之前还是之后激活。

触发器有几个优点。例如，您可以使用它们在 INSERT 语句期间生成派生列的值。另一个用例是强制引用完整性，您可以使用触发器将记录保存到多个相关表中。其他好处包括将用户操作记录到审计表以及跨不同数据库模式实时复制数据以实现冗余目的，以防止单点故障。

您还可以使用触发器将验证规则保留在数据库级别。这有助于在不破坏业务逻辑的情况下跨多个应用程序共享数据源。这大大减少了到数据库服务器的往返次数，从而提高了应用程序的响应时间。由于数据库服务器执行触发器，它们可以利用改进的服务器资源，例如 RAM 和 CPU。

在本教程中，您将在 MySQL 数据库上创建、使用和删除不同类型的触发器。

先决条件

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

按照 Initial Server Setup 和 Ubuntu 18.04 设置一台 Ubuntu 18.04 服务器，包括 sudo 非 root 用户。
通过以下方式在您的服务器上运行 MySQL 数据库：如何在 Ubuntu 18.04 上安装 MySQL
MySQL 数据库的根用户帐户凭据。

第 1 步 — 创建示例数据库

在这一步中，您将创建一个包含多个表的示例客户数据库，用于演示 MySQL 触发器的工作原理。

要了解有关 MySQL 查询的更多信息，请阅读我们的 MySQL 查询简介。

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

mysql -u root -p

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

Create database test_db;

OutputQuery OK, 1 row affected (0.00 sec)

接下来，使用以下命令切换到 test_db：

Use test_db;

OutputDatabase 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;

OutputQuery OK, 0 rows affected (0.01 sec)

现在，在 customers 表中添加几条记录。为此，请一一运行以下命令：

Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

运行每个 INSERT 命令后，您将看到以下输出：

OutputQuery OK, 1 row affected (0.01 sec)

要确保样本记录已成功插入，请运行 SELECT 命令：

Select * from customers;

Output+-------------+---------------+-------+
| customer_id | customer_name | level |
+-------------+---------------+-------+
|           1 | JOHN DOE      | BASIC |
|           2 | MARY ROE      | BASIC |
|           3 | JOHN DOE      | VIP   |
+-------------+---------------+-------+
3 rows in set (0.00 sec)

您还将创建另一个表来保存有关 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;

OutputQuery OK, 0 rows affected (0.01 sec)

在接下来的步骤中，您将在测试触发器时将示例数据添加到 sales 数据中。接下来，创建一个 audit_log 表来记录在步骤 5 中实现 AFTER UPDATE 触发器时对 sales 表所做的更新：

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;

OutputQuery OK, 0 rows affected (0.02 sec)

有了 test_db 数据库和四个表，您现在将继续使用数据库中的不同 MySQL 触发器。

第 2 步 - 创建插入前触发器

在此步骤中，您将在应用此逻辑创建 BEFORE INSERT 触发器之前检查 MySQL 触发器的语法，该触发器在将数据插入 sales 时验证 sales_amount 字段桌子。

创建 MySQL 触发器的一般语法如下例所示：

DELIMITER //
CREATE TRIGGER [TRIGGER_NAME]
[TRIGGER TIME] [TRIGGER EVENT]
ON [TABLE]
FOR EACH ROW
[TRIGGER BODY]//
DELIMITER ;

触发器的结构包括：

DELIMITER //：默认的 MySQL 分隔符是 ; - 有必要将其更改为其他内容，以便 MySQL 将以下行视为一个命令，直到它遇到您的自定义分隔符。在此示例中，分隔符更改为 //，然后在末尾重新定义 ; 分隔符。

[TRIGGER_NAME]：触发器必须有一个名称，这是您包含值的地方。

[TRIGGER TIME]：可以在不同的时间调用触发器。 MySQL 允许您定义触发器是在数据库操作之前还是之后启动。

[TRIGGER EVENT]：触发器仅由 INSERT、UPDATE 和 DELETE 操作调用。您可以在此处使用任何值，具体取决于您想要实现的目标。

[TABLE]：您在 MySQL 数据库上创建的任何触发器都必须与表相关联。

FOR EACH ROW：该语句告诉 MySQL 为触发器影响的每一行执行触发器代码。

[TRIGGER BODY]：调用触发器时执行的代码称为触发器体。这可以是单个 SQL 语句或多个命令。请注意，如果您在触发器主体上执行多个 SQL 语句，则必须将它们包装在 BEGIN...END 块之间。

注意： 创建触发器主体时，可以使用 OLD 和 NEW 关键字访问在 INSERT 期间输入的新旧列值， UPDATE 和 DELETE 操作。在 DELETE 触发器中，只能使用 OLD 关键字（您将在步骤 4 中使用）。

现在您将创建您的第一个 BEFORE INSERT 触发器。此触发器将与 sales 表相关联，并将在插入记录以验证 sales_amount 之前调用它。触发器的功能是检查插入到销售表中的 sales_amount 是否大于 10000 并在其计算结果为真时引发错误。

确保您已登录到 MySQL 服务器。然后，一一输入以下MySQL命令：

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 = 'Sale has exceeded the allowed amount of 10000.';
END IF//
DELIMITER ;

您正在使用 IF...THEN...END IF 语句来评估在 INSERT 语句期间提供的数量是否在您的范围内。触发器能够提取使用 NEW 关键字提供的新 sales_amount 值。

要引发一般错误消息，请使用以下行来通知用户该错误：

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';

接下来，在sales表中插入一条sales_amount为11000的记录，检查触发器是否会停止操作：

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

OutputERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

此错误表明触发代码按预期工作。

现在尝试使用值为 7500 的新记录来检查命令是否会成功：

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

由于该值在推荐范围内，您将看到以下输出：

OutputQuery OK, 1 row affected (0.01 sec)

要确认数据已插入，请运行以下命令：

Select * from sales;

输出确认数据在表中：

Output+----------+-------------+--------------+
| sales_id | customer_id | sales_amount |
+----------+-------------+--------------+
|        1 |           1 |         7500 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

在此步骤中，您已经测试了触发器以在插入数据库之前验证数据。

接下来，您将使用 AFTER INSERT 触发器将相关信息保存到不同的表中。

第 3 步 - 创建插入后触发器

AFTER INSERT 触发器在记录成功插入表时执行。此功能可用于自动运行其他与业务相关的逻辑。例如，在银行应用程序中，AFTER INSERT 触发器可以在客户完成还清贷款时关闭贷款账户。触发器可以监控插入到交易表中的所有付款，并在贷款余额为零时自动关闭贷款。

在此步骤中，您将使用 AFTER INSERT 触发器来处理 customer_status 表，以输入相关的客户记录。

要创建 AFTER INSERT 触发器，请输入以下命令：

DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
DELIMITER ;

OutputQuery OK, 0 rows affected (0.00 sec)

在这里，您指示 MySQL 在将新客户记录插入 customers 表后将另一条记录保存到 customer_status 表中。

现在，在 customers 表中插入一条新记录，以确认您的触发器代码将被调用：

Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

OutputQuery OK, 1 row affected (0.01 sec)

由于记录插入成功，请检查 customer_status 表中是否插入了新的状态记录：

Select * from customer_status;

Output+-------------+-----------------------------+
| customer_id | status_notes                |
+-------------+-----------------------------+
|           4 | ACCOUNT OPENED SUCCESSFULLY |
+-------------+-----------------------------+
1 row in set (0.00 sec)

输出确认触发器成功运行。

AFTER INSERT 触发器可用于监控客户的生命周期。在生产环境中，客户的账户可能会经历开户、暂停、关闭等不同阶段。

在以下步骤中，您将使用 UPDATE 触发器。

第 4 步 — 创建更新前触发器

BEFORE UPDATE 触发器类似于 BEFORE INSERT 触发器——不同之处在于它们被调用的时间。您可以使用 BEFORE UPDATE 触发器在记录更新之前检查业务逻辑。要对此进行测试，您将使用已在其中插入一些数据的 customers 表。

您在数据库中有两个级别的客户。本例中，客户账号一旦升级到VIP级别，就不能降级到BASIC级别。要强制执行这样的规则，您将创建一个 BEFORE UPDATE 触发器，该触发器将在 UPDATE 语句之前执行，如下所示。如果数据库用户试图将客户从 VIP 级别降级到 BASIC 级别，将触发用户定义的异常。

一一输入以下SQL命令，创建BEFORE UPDATE触发器：

DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
END IF //
DELIMITER ;

您使用 OLD 关键字来捕获用户在运行 UPDATE 命令时提供的级别。同样，您使用 IF...THEN...END IF 语句向用户发出一般错误语句的信号。

接下来，运行以下 SQL 命令来尝试降级与 3 的 customer_id 关联的客户帐户：

Update customers set level='BASIC' where customer_id='3';

您将看到提供 SET MESSAGE_TEXT 的以下输出：

OutputERROR 1644 (45000): A VIP customer can not be downgraded.

如果对 BASIC 级别的客户运行相同的命令，并尝试将帐户升级到 VIP 级别，该命令将成功执行：

Update customers set level='VIP' where customer_id='1';

OutputRows matched: 1  Changed: 1  Warnings: 0

您已使用 BEFORE UPDATE 触发器来强制执行业务规则。现在您将继续使用 AFTER UPDATE 触发器进行审计日志记录。

第 5 步 — 创建更新后触发器

一旦成功更新数据库记录，就会调用 AFTER UPDATE 触发器。此行为使触发器适用于审计日志记录。在多用户环境中，出于审计目的，管理员可能希望查看用户更新特定表中记录的历史记录。

您将创建一个触发器来记录 sales 表的更新活动。我们的 audit_log 表将包含 MySQL 用户更新 sales 表、更新的 date 以及 new 和 old 的信息] sales_amount 值。

要创建触发器，请运行以下 SQL 命令：

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

您将新记录插入 audit_log 表。您使用 NEW 关键字来检索 sales_id 和新的 sales_amount 的值。此外，您使用 OLD 关键字来检索以前的 sales_amount，因为您想记录两个金额以用于审计目的。

命令 SELECT USER() 检索执行操作的当前用户，NOW() 语句从 MySQL 服务器检索当前日期和时间的值。

现在，如果用户尝试更新 sales 表中任何记录的值，log_sales_updates 触发器将向 audit_log 表中插入一条新记录。

让我们用 5 的随机 sales_id 创建一个新的销售记录并尝试更新它。首先，插入销售记录：

Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

OutputQuery OK, 1 row affected (0.00 sec)

接下来，更新记录：

Update sales set sales_amount='9000' where sales_id='5';

您将看到以下输出：

OutputRows matched: 1  Changed: 1  Warnings: 0

现在运行以下命令来验证 AFTER UPDATE 触发器是否能够将新记录注册到 audit_log 表中：

Select * from audit_log;

触发器记录了更新。您的输出显示了先前向更新记录的用户注册的 sales_amount 和 new amount：

Output+--------+----------+-----------------+------------+----------------+---------------------+
| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
+--------+----------+-----------------+------------+----------------+---------------------+
|      1 |        5 |            8000 |       9000 | root@localhost | 2019-11-07 09:28:36 |
+--------+----------+-----------------+------------+----------------+---------------------+
1 row in set (0.00 sec)

您还有执行更新的日期和时间，这对于审计目的很有价值。

接下来，您将使用 DELETE 触发器在数据库级别强制执行引用完整性。

第 6 步 — 创建删除前触发器

BEFORE DELETE 触发器在 DELETE 语句在表上执行之前调用。这些类型的触发器通常用于在不同的相关表上强制执行参照完整性。例如，sales 表中的每条记录都与 customers 表中的 customer_id 相关。如果数据库用户从 customers 表中删除了在 sales 表中有相关记录的记录，您将无法知道与该记录关联的客户。

为避免这种情况，您可以创建一个 BEFORE DELETE 触发器来强制执行您的逻辑。一一运行以下SQL命令：

DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'The customer has a related sales record.';
END IF//
DELIMITER ;

现在，尝试删除具有相关销售记录的客户：

Delete from customers where customer_id='2';

结果，您将收到以下输出：

OutputERROR 1644 (45000): The customer has a related sales record.

BEFORE DELETE 触发器可以防止意外删除数据库中的相关信息。

但是，在某些情况下，您可能希望从不同的相关表中删除与特定记录关联的所有记录。在这种情况下，您将使用 AFTER DELETE 触发器，您将在下一步中对其进行测试。

第 7 步 — 创建删除后触发器

成功删除记录后，将激活 AFTER DELETE 触发器。如何使用 AFTER DELETE 触发器的一个示例是特定客户收到的折扣级别由定义期间内的销售数量决定。如果从 sales 表中删除了一些客户记录，则需要降低客户折扣级别。

AFTER DELETE 触发器的另一个用途是在删除基表中的记录后从另一个表中删除相关信息。例如，如果从 sales 表中删除具有相关 customer_id 的销售记录，您将设置一个触发器来删除客户记录。运行以下命令来创建触发器：

DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;

接下来，运行以下命令删除与 2 的 customer_id 关联的所有销售记录：

Delete from sales where customer_id='2';

OutputQuery OK, 1 row affected (0.00 sec)

现在检查 sales 表中是否有客户的记录：

Select * from customers where customer_id='2';

您将收到 Empty Set 输出，因为与 2 的 customer_id 关联的客户记录已被触发器删除：

OutputEmpty set (0.00 sec)

您现在已经使用了每种不同形式的触发器来执行特定的功能。接下来，您将看到如果不再需要触发器，如何从数据库中删除它。

第 8 步 — 删除触发器

与任何其他数据库对象类似，您可以使用 DROP 命令删除触发器。以下是删除触发器的语法：

Drop trigger [TRIGGER NAME];

例如，要删除您创建的最后一个 AFTER DELETE 触发器，请运行以下命令：

Drop trigger delete_related_info;

OutputQuery OK, 0 rows affected (0.00 sec)

当您想要重新创建其结构时，就会出现删除触发器的需要。在这种情况下，您可以删除触发器并使用不同的触发器命令重新定义一个新触发器。

结论

在本教程中，您已经从 MySQL 数据库中创建、使用和删除了不同类型的触发器。使用与客户相关的示例数据库，您已经为不同的用例实现了触发器，例如数据验证、业务逻辑应用程序、审计日志记录和强制引用完整性。

有关使用 MySQL 数据库的更多信息，请查看以下内容：