如何使用 MySQL 触发器？

MySQL 触发器是强大的数据库对象，可以让你基于数据库中的特定事件自动执行任务。通过定义触发器，可以提高数据完整性，应用业务规则，并简化数据管理流程。

在本文中，我们将深入了解如何有效使用 MySQL 触发器。我们将学习触发器的概念、触发器的语法以及各种类型的触发器及其示例。

MySQL 触发器是什么意思？

MySQL 触发器是存储程序，会在数据库表中发生特定事件时自动执行。这些特定事件可以是 INSERT、UPDATE 或 DELETE 记录。触发器包含三个主要部分：触发器事件，定义事件；触发器操作，定义需要执行的操作；以及触发器时间，指示触发器何时执行。

MySQL 触发器的语法和结构

你需要使用 CREATE TRIGGER 命令来创建触发器，该命令遵循特定的语法。MySQL 触发器的结构包含 triggerName（触发器名称）、triggerTime（触发器时间）、triggerEvent（触发器事件）、triggerAction（触发器操作）以及用于定义触发器的分隔符。

triggerName 是为触发器指定的用于识别的名称。

triggerTime 定义触发器执行的时间，可以是触发事件发生之前或之后。

triggerEvent 定义触发触发器的事件，例如 UPDATE、INSERT 或 DELETE。

tableName 是应用触发器的表的名称。

FOR EACH ROW 表示触发器将为受影响的每一行执行。

在 BEGIN 和 END 块内，你必须编写定义触发器触发时需要执行的操作的 SQL 语句。这些语句可以包括调用数据修改、计算或其他存储过程。

为什么要在 MySQL 中使用触发器

• 触发器用于通过应用数据库中的约束和规则来提高数据完整性。你可以定义一个触发器来控制记录的删除，如果它被其他表引用，从而确保引用完整性。触发器允许你在插入、更新或删除数据之前自动验证数据，并确保数据库中只存储有效数据。
• 触发器可以自动执行数据库中的重复任务。你可以定义触发器，以便在发生特定事件时自动执行某些操作。通过自动化复杂的数据转换或同步过程，触发器有助于最大限度地减少手动工作并保持数据准确性。
• 触发器有助于跟踪和审计重要数据的更改。通过定义触发器，可以捕获和记录对特定表或列所做的修改。你还可以跟踪谁进行了更改、更改了什么以及何时进行了更改。触发器还可以根据特定条件生成通知，并实现对关键数据事件的实时监控。
• 触发器允许在数据库中应用特定的业务规则。你可以使用触发器来应用安全策略、访问限制或数据验证规则。触发器允许你在数据库级别实现复杂的业务逻辑，并确保跨各种应用程序的一致性，符合预定义规则。

MySQL 触发器类型

MySQL 中有两种触发器：BEFORE 触发器和 AFTER 触发器。

1. BEFORE 触发器

BEFORE 触发器也称为预触发器，它在触发事件发生之前执行。它允许你在实际更改发生之前修改数据或执行其他操作。通常用于数据验证或应用业务规则。

MySQL 中有三种不同类型的 BEFORE 触发器

BEFORE INSERT 触发器：此触发器在对表执行 INSERT 操作之前执行。它可以用于修改要插入的值，或在数据插入表之前对其进行验证。

BEFORE UPDATE 触发器：此触发器在对表执行 UPDATE 操作之前执行。它允许你修改要更新的值，或对更新操作应用某些条件。

BEFORE DELETE 触发器：此触发器在对表执行 DELETE 操作之前执行。它可以用于在删除发生之前执行特定操作。你可以使用它来阻止删除特定行或执行级联删除。

2. AFTER 触发器

AFTER 触发器也称为后触发器，它在触发事件完成后执行。它用于根据触发事件的结果执行操作。通常用于更新相关表、记录更改等任务。

MySQL 中有三种不同类型的 AFTER 触发器

AFTER INSERT 触发器：此触发器在新行插入表中后自动执行。它可以定义为根据插入的数据执行某些操作或计算。

AFTER UPDATE 触发器：此触发器在表中的一行或多行更新后自动执行。通常用于根据更新后的数据采取操作。

AFTER DELETE 触发器：此触发器在从表中删除一行或多行后自动执行。它允许你根据删除的数据执行操作。

以下是一些演示 MySQL 触发器用法的示例

示例 1

让我们创建一个名为“passengers”的表，其中包含 Id、Name、Address 和 Charges 等字段。要创建表，请使用提供的命令

表已创建，外观如下所示

在 'passengers' 表中插入值后，外观如下所示

现在我们将创建一个名为“flight”的触发器，当在表中输入新记录时，该触发器会将新 charges 减去 1500。使用给定的语句创建触发器

创建触发器后，执行它。

现在，我们将使用给定的命令在 'passengers' 表中插入一条新记录

使用以下语句查看结果

如您在下方所见，新 charges 已扣除 1500。

示例 2

让我们创建两个表：“college_student”，其中包含 Id、Name、Age 和 Address 等字段，以及“college_audit”，其中包含 Id 和 audit_description 等字段。

使用给定的命令创建 'college_student' 表

表已创建，外观如下所示

现在，我们将向 'college_student' 表插入值，

在 'college_student' 表中插入值后，外观如下所示

现在，我们将使用以下命令创建 'college_audit' 表

表已创建，外观如下所示

现在我们将创建一个名为“afterInsertCollege”的触发器，当在 'college_student' 表中添加新行时，该触发器会将审计详细信息插入 'college_audit' 表。使用以下语句创建触发器

创建触发器后，执行它。

现在，我们将向 'college_student' 表插入值，

在 'college_student' 表中插入值后，外观如下所示；

由于我们在 'college_student' 表上应用了触发器，随着 'college_student' 表中每插入一行新数据，日期和时间就会被添加到 'college_audit' 表中。

我们将使用以下语句查看 'college_audit' 表

如您在下方所见，日期和时间已自动添加到 'college_audit' 表中。

示例 3

让我们创建一个名为“workers”的表，其中包含 Id、Name、Age、Address 和 Salary 等字段。

使用给定的命令创建 'workers' 表

表已创建，外观如下所示

现在，我们将使用给定的命令向 'workers' 表插入值

在 'workers' 表中插入值后，外观如下所示

我们将创建一个名为“salary_update”的触发器，该触发器将用于更新表中新插入的工人的工资。如果新工人的工资低于 15000，则新工人的工资将更新为 15000。我们将使用以下语句创建触发器

创建触发器后，执行它。

现在，我们将使用以下语句在 'workers' 表中插入一条新记录

执行上述语句后，我们将使用以下语句显示 'workers' 表

如您在下方所见，添加到表中的新工人的工资已更新为 15000。

MySQL 触发器的局限性

• 如果你的触发器设计不当，可能会影响数据库的性能。触发器在事务中同步执行，因此如果触发器执行复杂的操作，可能会减慢整体数据库性能。仔细的考虑和测试是必要的，以确保触发器不会对性能产生不利影响。
• 触发器可能难以调试和测试。与常规应用程序代码不同，触发器会响应特定事件而自动执行，这使得隔离和识别问题更加困难。此外，触发器执行的可见性可能有限，这会使调试过程复杂化。
• 触发器可能在数据库中引入隐藏的行为。如果触发器没有得到妥善记录，或者开发人员不知道它们的存在，这可能会导致混淆和意外后果。保持透明度很重要，围绕触发器的文档对于理解和维护数据库系统至关重要。
• 过度使用触发器可能导致复杂的数据库逻辑，难以理解和维护。当触发器散布在整个数据库模式中时，跟踪数据流和逻辑就会变得困难，从而导致维护问题。
• MySQL 没有内置机制来防止递归触发器。如果一个触发器修改了触发它的同一个表，它可能会导致无限循环并导致系统崩溃。避免递归行为需要在设计触发器时仔细考虑。
• MySQL 中的触发器使用 SQL 编写，并遵循 MySQL 支持的特定语法和功能。如果你计划将数据库迁移到另一个数据库管理系统。在这种情况下，可能需要重写或修改触发器，以匹配新 DBMS 的语法和行为。
• MySQL 中的触发器设计用于处理单个行级操作。它们不适合处理批量操作或跨多行执行复杂聚合。尝试将触发器用于此类场景可能会导致性能不佳和行为不可预测。
• 虽然触发器可以提高安全性，但如果实现不当，它们也可能带来安全风险。输入验证不足或触发器中用户权限处理不当可能导致注入攻击或未经授权的数据访问等漏洞。

结论

在本文中，我们学习了如何使用 MySQL 触发器。触发器是用户定义的 SQL 语句，它会在 INSERT、DELETE 或 UPDATE 等事件的响应下自动执行。你已经了解了 MySQL 触发器的语法和结构。你已经掌握了使用触发器的必要性以及触发器的局限性。你已经知道有两种触发器：AFTER 触发器和 BEFORE 触发器。你已经理解了如何正确使用触发器的示例。