极客笔记MySQL Trigger的使用方法？
MySQL 触发器是强大的数据库对象，可以根据数据库中的特定事件自动执行任务。通过定义触发器，可以提高数据完整性、应用业务规则以及简化数据管理过程。
在本文中，我们将了解如何有效地使用MySQL触发器。我们将学习触发器的概念、触发器的语法以及不同类型的触发器的示例。
MySQL触发器的含义是什么？
MySQL触发器是存储的程序，根据发生在数据库表中的特定事件自动执行。这些特定事件可以是插入、更新或删除记录。触发器由三个主要部分组成：触发事件，定义事件；触发动作，定义需要执行的操作；触发时间，告诉触发器何时执行。
MySQL触发器的语法和结构：
CREATE TRIGGER triggerName
triggerTime triggerEvent
ON tableName
FOR EACH ROW
BEGIN
-- Trigger action statements
END;
您需要使用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
);
表格已创建，预计显示效果如下所示：
| Id | Name | Address | Charges |
|---|---|---|---|
INSERT INTO passengers
VALUES(205, 'Rekha', 'Jammu', 5000),
(210, 'Lakhan', 'Lucknow', 7000),
(356, 'Mehek', 'Hyderabad', 9000),
(359, 'Bharti', 'Mumbai', 7500);
将值插入“乘客”表后，它将如下所示：
| Id | Name | Address | Charges |
|---|---|---|---|
| 205 | Rekha | Jammu | 500 |
| 210 | Lakhan | Lucknow | 7000 |
| 356 | Mehek | Hyderabad | 6900 |
| 359 | Bharti | Mumbai | 7500 |
现在我们将创建一个名为’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)
);
表格已创建，将看起来如下所示：
| Id | Name | Address | Charges |
|---|---|---|---|
现在，我们将在’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)
);
表格已创建，它将如下所示：
| Id | audit_description |
|---|---|
现在我们将创建一个名为’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
);
表格已创建，并且它将如下所示：
| Id | Name | Age | Address | Salary |
|---|---|---|---|---|
现在，我们将使用给定的命令在’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”表后，它将如下所示：
| Id | Name | Age | Address | Salary |
|---|---|---|---|---|
| 1 | Hansika | 25 | Bhopal | 10000 |
| 2 | Jaya | 30 | Lucknow | 12000 |
| 3 | Nisha | 29 | Agra | 15000 |
| 4 | Virat | 32 | Agra | 18000 |
我们将创建一个名为’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 支持的特定语法和特性。如果计划将数据库迁移到不同的数据库管理系统，则可能需要重写或修改触发器以匹配新的数据库管理系统的语法和行为。
- MySQL 中的触发器设计用于处理单个行级操作。它们不适用于处理批量操作或在多行上执行复杂聚合。尝试在这些情况下使用触发器可能导致性能不佳和行为不可预测。
- 虽然触发器可以增加安全性，但如果实施不当，也可能存在安全风险。触发器内部不充分验证输入或不正确处理用户权限可能会导致注入攻击或未经授权的数据访问漏洞。
结论：
在本文中，我们学习了如何使用 MySQL 触发器。触发器是一个用户定义的 SQL 语句，它会自动根据插入、删除或更新等事件进行执行。您已经了解了 MySQL 触发器的语法和结构。您已经了解了使用触发器的必要性和触发器的局限性。您已经了解到触发器分为 AFTER 触发器和 BEFORE 触发器两种。您已经理解了示例，说明了如何正确使用触发器。