如何在 Ubuntu 18.04 上管理和使用 MySQL 数据库触发器

作者选择了 Apache Software Foundation作为 Write for Donations计划的一部分接受捐款。

介绍

在 MySQL中,一个 trigger是用户定义的 SQL 命令,在INSERT、DELETE或UPDATE操作中自动召唤。触发代码与表相关联,一旦丢掉表,就会被破坏。

触发器有几个优点. 例如,您可以使用它们在INSERT语句中生成衍生列的值。另一个用例是强制引用完整性,您可以使用触发器将记录保存到多个相关表中。

您还可以使用触发器来保持数据库级别的验证规则。这有助于在不破坏业务逻辑的情况下在多个应用程序中共享数据源。这大大减少了到数据库服务器的回程,从而提高了应用程序的响应时间。

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

前提条件

在您开始之前,请确保您有以下内容:

一个 Ubuntu 18.04 服务器通过遵循 Ubuntu 18.04 初始服务器设置设置,包括一个 sudo 非根用户。
一个 MySQL 数据库运行在您的服务器上,如下: 如何在 Ubuntu 18.04 上安装 MySQL
根用户帐户凭证为您的 MySQL 数据库。

步骤 1 - 创建样本数据库

在此步骤中,您将创建一个具有多个表的样本客户数据库,以展示MySQL触发器如何工作。

要了解更多关于 MySQL 查询的信息,请阅读我们的介绍 MySQL 查询。

首先,登录您的MySQL服务器作为 root:

1mysql -u root -p

请在提示时输入您的MySQL根密码,然后按ENTER,以继续。当您看到mysql>提示时,运行以下命令来创建一个test_db数据库:

1Create database test_db;

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

接下来,切换到 test_db 以:

1Use test_db;

1[secondary_label Output]
2Database changed

您将开始创建一个客户表,该表将包含客户的记录,包括客户ID,客户名和水平。

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

1[secondary_label Output]
2Query OK, 0 rows affected (0.01 sec)

现在,将一些记录添加到客户表中,然后单独执行下列命令:

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

在运行每个INSERT命令后,您将看到以下输出:

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

要确保样本记录被成功插入,请运行SELECT命令:

1Select * from customers;

1[secondary_label Output]
2+-------------+---------------+-------+
3| customer_id | customer_name | level |
4+-------------+---------------+-------+
5|           1 | JOHN DOE      | BASIC |
6|           2 | MARY ROE      | BASIC |
7|           3 | JOHN DOE      | VIP   |
8+-------------+---------------+-------+
93 rows in set (0.00 sec)

您还将创建另一个表,以存储有关客户帐户的相关信息。

运行以下命令:

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

接下来,您将创建一个销售表,该表将包含与不同客户相关的销售数据,通过客户ID列:

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

1[secondary_label Output]
2Query OK, 0 rows affected (0.01 sec)

在测试触发器时,您将在接下来的步骤中将样本数据添加到销售数据中,然后创建一个audit_log表来记录在步骤 5中实施UTTER UPDATE触发器时对销售表进行的更新。

1Create 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;

1[secondary_label Output]
2Query OK, 0 rows affected (0.02 sec)

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

步骤 2 — 创建插入之前的触发器

在此步骤中,您将在应用此逻辑之前检查 MySQL 触发器的语法,以创建一个在销售表中插入数据时验证sales_amount字段的BEFORE INSERT触发器。

创建 MySQL 触发器的通用语法显示在下面的示例中:

1DELIMITER //
2CREATE TRIGGER [TRIGGER_NAME]
3[TRIGGER TIME] [TRIGGER EVENT]
4ON [TABLE]
5FOR EACH ROW
6[TRIGGER BODY]//
7DELIMITER ;

触发器的结构包括:

DELIMITER //:默认的MySQL分界器是 ;—必须将其更改为其他东西,以便MySQL将下列行作为一个命令处理,直到它触及您的自定义分界器。

[TRIGGER_NAME]:一个触发器必须有一个名称,这就是你包含该值的地方。

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

「 [TRIGGER EVENT]」:啟動器只會被「INSERT」、「UPDATE」和「DELETE」操作召喚。

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

for each row:此语句告诉MySQL为触发器影响的每个行执行触发器代码。

[TRIGGER BODY]:当触发器被召唤时执行的代码称为 trigger body. 它可以是一个单一的 SQL 命令或多个命令. 请注意,如果您在触发器上执行多个 SQL 命令,则必须在一个 BEGIN...END 块之间包装它们。

<$>[注] **注:**在创建触发器时,您可以使用旧和新关键字来访问在输入、更新和删除操作中输入的旧和新列值。

现在,您将创建您的第一个BEFORE INSERT触发器。这个触发器将与销售表相关联,并在插入记录之前将被召唤以验证销售额。

请确保您已登录到 MySQL 服务器,然后单独输入以下 MySQL 命令:

 1DELIMITER //
 2CREATE TRIGGER validate_sales_amount
 3BEFORE INSERT
 4ON sales
 5FOR EACH ROW
 6IF NEW.sales_amount>10000 THEN
 7SIGNAL SQLSTATE '45000'
 8SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
 9END IF//
10DELIMITER ;

您正在使用IF...THEN...END IF声明来评估在INSERT声明中提供的金额是否在您的范围内。

若要提到通用错误消息,请使用以下行通知用户错误:

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

接下来,在销售表中插入一个销售额为11000的记录,以检查触发器是否会停止操作:

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

1[secondary_label Output]
2ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

此错误显示触发代码按预期工作。

现在尝试一个新的记录,值为7500,以检查命令是否成功:

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

由于值在推荐范围内,您将看到以下输出:

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

要确认数据已被插入,运行以下命令:

1Select * from sales;

输出确认数据在表中:

1[secondary_label Output]
2+----------+-------------+--------------+
3| sales_id | customer_id | sales_amount |
4+----------+-------------+--------------+
5|        1 |           1 |         7500 |
6+----------+-------------+--------------+
71 row in set (0.00 sec)

在此步骤中,您已经测试了触发器来验证数据,然后将其插入数据库。

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

步骤 3 — 创建后插入触发器

此功能可用于自动运行其他业务相关逻辑,例如,在银行应用中,在客户完成支付贷款后,一个INSERT触发器可以关闭贷款帐户。

在此步骤中,您将使用在输入后触发器与您的客户状态表进行工作,以输入相关的客户记录。

要创建INSERT触发器,请输入以下命令:

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

1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)

在这里,您指示MySQL在客户表中插入新客户记录后,将另一个记录保存到客户状态表中。

现在,在客户表中插入一个新的记录,以确认您的触发代码将被召唤:

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

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

由于记录已成功插入,请检查新状态记录是否被插入到客户状态表中:

1Select * from customer_status;

1[secondary_label Output]
2+-------------+-----------------------------+
3| customer_id | status_notes                |
4+-------------+-----------------------------+
5|           4 | ACCOUNT OPENED SUCCESSFULLY |
6+-------------+-----------------------------+
71 row in set (0.00 sec)

输出证实触发器成功运行。

在生产环境中,客户的账户可能经历不同的阶段,如开户、暂停和关闭。

在以下步骤中,您将使用更新触发器。

步骤 4 — 创建预先更新触发器

一个BEFORE UPDATE触发器类似于BEFORE INSERT触发器 - 区别在于它们被召唤时。您可以使用BEFORE UPDATE触发器在更新记录之前检查业务逻辑。

在此示例中,一旦客户帐户升级到VIP级别,该帐户无法降级至BASIC级别. 要执行此规则,您将创建一个前更新触发器,该触发器将在更新声明之前执行,如下所示。

输入以下 SQL 命令一个接一个来创建前更新触发器:

 1DELIMITER //
 2CREATE TRIGGER validate_customer_level
 3BEFORE UPDATE
 4ON customers
 5FOR EACH ROW
 6IF OLD.level='VIP' THEN
 7SIGNAL SQLSTATE '45000'
 8SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
 9END IF //
10DELIMITER ;

您使用OLD关键字来捕捉用户在运行UPDATE命令时提供的级别。

接下来,运行以下 SQL 命令,试图降级与 3 的 customer_id' 相关的客户帐户:

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

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

1[secondary_label Output]
2ERROR 1644 (45000): A VIP customer can not be downgraded.

如果您将相同的命令执行到BASIC级别的客户端,并尝试将帐户升级到VIP级别,该命令将成功执行:

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

1[secondary_label Output]
2Rows matched: 1 Changed: 1 Warnings: 0

您已经使用了前更新触发器来执行业务规则,现在您将继续使用后更新触发器进行审计日志。

步骤 5 – 创建一个更新后触发器

成功更新数据库记录后,会引用一个更新后触发器。此行为使触发器适合审计日志。在多用户环境中,管理员可能希望查看某个表中的用户更新记录的历史记录以进行审计。

我们的audit_log表将包含更新sales表的MySQL用户、更新日期和新和旧``sales_amount值的信息。

要创建触发器,请运行以下 SQL 命令:

1DELIMITER //
2CREATE TRIGGER log_sales_updates
3AFTER UPDATE
4ON sales
5FOR EACH ROW
6Insert 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() )//
7DELIMITER ;

您将新记录插入到audit_log表中. 您使用NEW关键字来检索sales_id和新sales_amount的值。

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

现在,如果用户尝试更新销售表中的任何记录的值,则log_sales_updates触发器将向audit_log表插入新的记录。

让我们创建一个新的销售记录,以5的随机sales_id,并尝试更新它。

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

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

接下来,更新记录:

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

您将看到以下输出:

1[secondary_label Output]
2Rows matched: 1 Changed: 1 Warnings: 0

现在运行以下命令来验证更新后触发器是否能够在audit_log表中注册新记录:

1Select * from audit_log;

触发器登录了更新. 您的输出显示了与更新记录的用户注册的以前的sales_amount和new amount:

1[secondary_label Output]
2+--------+----------+-----------------+------------+----------------+---------------------+
3| log_id | sales_id | previous_amount | new_amount | updated_by     | updated_on          |
4+--------+----------+-----------------+------------+----------------+---------------------+
5|      1 |        5 |            8000 |       9000 | root@localhost | 2019-11-07 09:28:36 |
6+--------+----------+-----------------+------------+----------------+---------------------+
71 row in set (0.00 sec)

您还可以知道更新的日期和时间,这些更新对于审计的目的非常有价值。

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

步骤 6 — 创建一个删除之前的触发器

在表上執行「DELETE」聲明之前,「BEFORE DELETE」啟動器會召喚。這些類型的啟動器通常用於在不同的相關表上執行引用完整性。例如,在「銷售」表上的每個記錄都與「顧客」表中的「customer_id」相關。

要避免这种情况,您可以创建一个前删除触发器来执行您的逻辑。

 1DELIMITER //
 2CREATE TRIGGER validate_related_records
 3BEFORE DELETE
 4ON customers
 5FOR EACH ROW
 6IF OLD.customer_id in (select customer_id from sales) THEN
 7SIGNAL SQLSTATE '45000'
 8SET MESSAGE_TEXT = 'The customer has a related sales record.';
 9END IF//
10DELIMITER ;

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

1Delete from customers where customer_id='2';

因此,您将获得以下输出:

1[secondary_label Output]
2ERROR 1644 (45000): The customer has a related sales record.

在删除之前的触发器可以防止数据库中的相关信息被意外删除。

然而,在某些情况下,您可能希望从不同的相关表中删除与某个特定记录相关的所有记录. 在这种情况下,您将使用在下一步中测试的DELETE触发器。

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

DELETE触发器一旦成功删除记录,就会被激活。你如何使用DELETE触发器的一个例子是,在某个特定客户获得的折扣水平是由在特定时间段内完成的销售数量来决定的。

启动器的另一个用途是从另一个表中删除来自基表的记录后删除相关信息,例如,如果从销售表中删除与相关customer_id的销售记录,您将设置一个启动器来删除客户记录。

1DELIMITER //
2CREATE TRIGGER delete_related_info
3AFTER DELETE
4ON sales
5FOR EACH ROW
6Delete from customers where customer_id=OLD.customer_id;//
7DELIMITER ;

接下来,执行以下操作以删除与2的客户 ID相关的所有销售记录:

1Delete from sales where customer_id='2';

1[secondary_label Output]
2Query OK, 1 row affected (0.00 sec)

现在检查从销售表中是否有客户的记录:

1Select * from customers where customer_id='2';

您将收到空组输出,因为触发器已删除与2的customer_id相关的客户记录:

1[secondary_label Output]
2Empty set (0.00 sec)

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

步骤 8 - 删除触发器

与任何其他数据库对象一样,您可以使用DROP命令删除触发器,以下是删除触发器的语法:

1Drop trigger [TRIGGER NAME];

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

1Drop trigger delete_related_info;

1[secondary_label Output]
2Query OK, 0 rows affected (0.00 sec)

在这种情况下,你可以放下触发器并用不同的触发器命令重新定义一个新的触发器。

结论

在本教程中,您已创建、使用和删除 MySQL 数据库中的不同类型的触发器. 使用一个客户相关的数据库示例,您已实现了不同用例的触发器,如数据验证,业务逻辑应用程序,审计日志和执行参考完整性。

有关使用您的 MySQL 数据库的更多信息,请参阅以下内容: