如何使用MySQL触发器

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

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

MySQL触发器是什么意思？

MySQL触发器是存储程序，它们在数据库表中发生特定事件时自动执行。这些特定事件可以是插入（INSERT）、更新（UPDATE）或删除（DELETE）记录。触发器由三个主要部分组成：触发事件（定义事件）、触发操作（定义需要执行的操作）和触发时间（指示触发器何时执行）。

MySQL触发器的语法和结构：

DELIMITER CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
    -- trigger action statements
END
DELIMITER ;

你需要使用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。要创建该表，请使用提供的命令：

CREATE TABLE passengers (
          Id INT,
          Name VARCHAR(40),
          Address VARCHAR(40),
          Charges INT
        );

已经创建了表格，并且它将如下所示：

INSERT INTO passengers
VALUES(205, 'Rekha', 'Jammu', 5000),
(210, 'Lakhan', 'Lucknow', 7000),
(356, 'Mehek', 'Hyderabad', 9000),
(359, 'Bharti', 'Mumbai', 7500);

在插入’passengers’表中的值后，它将如下所示：

现在我们将创建一个名为’flight’的触发器，当在表中插入新记录时，它将从新的费用中扣除1500。使用给定的语句创建触发器：

BEFORE INSERT ON passengers
FOR EACH ROW
    SET new.charges = new.charges - 1500;

创建触发器之后，执行它。

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

INSERT INTO passengers
VALUES(432, 'Shikha', 'Kanpur', 6500);

使用以下语句来查看结果:

SELECT * FROM passengers;

正如您在下面看到的，从新费用中已经扣除了1500个费用。

示例2：

让我们创建两个表：’college_student’，它有诸如Id、Name、Age和Address等字段，以及’college_audit’，它有诸如Id和audit_description等字段。

使用给定的命令来创建’college_student’表：

CREATE TABLE college_student (
          Id INT PRIMARY KEY,
          Name VARCHAR(40),
          Age INT,
          Address VARCHAR(40)
        );

表已创建，它将如下所示:

现在，我们将在’college_student’表中插入值,

INSERT INTO college_student
VALUES(101, 'Maya', 19, 'Noida'),
(102, 'Arnav', 18, 'Noida'),
(103, 'Krishna', 19, 'Mathura');

在将值插入’college_student’表后，它将会如下所示:

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

CREATE TABLE college_audit (
          Id INT PRIMARY KEY,
          audit_description VARCHAR(100)
        );

表已创建，它将如下所示：

现在我们将创建一个名为’afterInsertCollege’的触发器，它将在将新行添加到’college_student’表时将审核详细信息插入到’college_audit’表中。使用以下语句创建触发器：

CREATE TRIGGER afterInsertCollege
AFTER INSERT ON college_student
FOR EACH ROW
    INSERT INTO college_audit VALUES(1, concat("A new row has been inserted", date_format(now(), '%d, %m, %y %h:%i:%s %p')));

创建触发器后，执行它。

现在，我们将插入值到’college_student’表中，

INSERT INTO college_student
VALUES(104, 'Arushi', 18, 'Bhopal');

在将值插入到“college_student”表后，它将显示如下：

当我们在’college_student’表上应用触发器后，每插入一行新的记录到’college_student’表中，日期和时间就会被添加到’college_audit’表中。

我们将使用以下语句来查看’college_audit’表：

SELECT * FROM college_audit;

正如您可以在下面看到的那样，日期和时间会自动添加到“college_audit”表中。

示例3：

让我们创建一个名为’workers’的表，该表包括Id、Name、Age、Address和Salary等字段。

使用给定的命令来创建’workers’表：

CREATE TABLE workers (
          Id INT PRIMARY KEY,
          Name VARCHAR(40),
          Age INT,
          Address VARCHAR(40),
          Salary INT
        );

表格已创建，并且它看起来如下所示：

现在，我们将使用给定的命令向 ‘workers’ 表格中插入值：

INSERT INTO workers
VALUES(1, 'Hansika', 25, 'Bhopal', 10000),
(2, 'Jaya', 30, 'Lucknow', 12000),
(3, 'Nisha', 29, 'Agra', 15000),
(4, 'Virat', 32, 'Agra', 18000);

在将值插入“workers”表后，它的样子如下所示：

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

DELIMITER //
CREATE TRIGGER salary_update
BEFORE INSERT ON workers
FOR EACH ROW
IF new.salary<15000 THEN SET new.salary=15000;
END IF; //

创建触发器后，执行它。

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

INSERT INTO workers
VALUES(5, 'Rohan', 24, 'Agra', 12500);

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

SELECT * FROM workers;

正如你在下面所看到的，新添加到表中的工人的薪水已更新为15000。

MySQL触发器的限制：

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

总结:

在本文中，我们学习了如何使用MySQL触发器。触发器是自定义的SQL语句，在发生INSERT、DELETE或UPDATE等事件时自动执行。您已理解MySQL触发器的语法和结构。您已了解使用触发器的必要性和触发器的限制。您已了解到有两种类型的触发器：AFTER触发器和BEFORE触发器。您已理解通过示例如何正确使用触发器。