SQL 中的触发器

在本文中，您将了解触发器及其实现方式和示例。

SQL 中的触发器是在数据库发生更改时自动执行的语句。当我们执行诸如添加、更改和删除等操作时，会在此时间触发触发器。它们用于维护完整性、跟踪更改以及强制执行业务规则，而无需用户干预。

触发器是一个特殊的命令，存储在数据库中。当数据库中发生任何特殊事件时，它会自动调用。借助触发器，开发人员可以自动化任务、确保数据完整性并维护数据库的真实记录。触发器会在对数据库进行更改之前或之后自动验证数据。

在 SQL 中，这个概念与现实生活中的触发器相同。例如，当我们扣动枪扳机时，子弹就会发射。

要理解 SQL 中触发器的概念，让我们来看一下下面的假设情况：

假设 **Andrew** 是一家跨国公司的人力资源经理。当数据库中录入新员工的记录时，他必须向每位新员工发送“祝贺”消息。如果有四五名员工，Andrew 可以手动完成，但如果新员工人数超过一千人，那么在这种情况下，他将不得不使用数据库中的触发器。

因此，Andrew 现在必须在表中创建一个触发器，一旦新员工的记录插入数据库，该触发器将自动向新员工发送“祝贺”消息。

触发器始终与数据库中的特定表一起执行。如果删除该表，则与该表关联的所有触发器也将自动删除。

语法

参数

CREATE TRIGGER trigger_name: 它会创建一个具有唯一名称的新触发器。

BEFORE|AFTER: 它说明触发器何时在事件 **之前** 或 **之后** 触发。

ON table_name: 它将选择要连接触发器的表。

FOR EACH ROW:此语句使触发器成为行级触发器，这意味着它为每一行运行一次。

BEGIN: 当触发器命中时，代码块从此处开始执行。

END: 它将结束触发器的 SQL 块。SQL 语句写在 **BEGIN** 和 **END** 关键字之间。

示例

步骤 1：创建学生表。

步骤 2：创建学生日志表以存储更改。

步骤 3：创建触发器

步骤 4：插入示例数据

步骤 5：检查日志表

说明

在上面的示例中，我们创建了一个名为 **students** 的表和另一个名为 **student_log** 的表。学生表用于保存原始数据。**student_log** 表用于存储临时数据。

**触发器**在第 3 步中创建，在将新行插入学生表 **之后** 触发。当新学生数据添加到 **students** 表时，**触发器**会自动运行并将日志条目插入 **student_logs 表**。

要检查触发器是否正常工作，我们检索 student_logs 表中的所有记录。如果输出正确，则表明触发器工作正常。

SQL 触发器的用途

使用 SQL 表中的触发器有一些好处，如下所示：

自动验证：它自动处理插入、删除和更新等重复任务，从而节省我们的时间和精力。

数据完整性：SQL 触发器确保数据的准确性和一致性。

审计和日志记录：它通过维护更改记录来跟踪所有更改。它存储有关谁何时更改的所有记录。

维护安全性：它控制和监控对敏感数据的访问，这有助于维护数据库的安全性。

级联更改：如果我们在一个表中更改数据，级联会自动帮助在另一个表中进行更改，例如更新或删除。

SQL 触发器类型

触发器的类型很多，根据它们执行的操作进行分类，如下所示：

DDL 触发器

数据定义语言 (**DDL**) 是 **SQL** 中的一类命令，用于 **CREATE**、**DROP**、**ALTER** 以及其他修改数据库中数据的命令。当发生 DDL 事件时，将激活 **触发器**。这些触发器用于 **跟踪**、**审计** 或 **防止** 这些更改。

示例

在此示例中，我们看到触发器阻止删除存储过程。

SQL 查询

输出

 
Dropping stored procedures is not allowed in this database.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

说明

在上面的示例中，SQL 中的 DDL 触发器阻止在数据库中删除存储过程。当我们尝试删除存储数据库触发器时，它会自动启动并显示消息“**在此数据库中不允许删除存储过程**”。之后，它使用 ROLLBACK 命令取消操作。

注意：DDL 触发器在 SQL Server、Oracle 和 PostgreSQL 等数据库中受支持，但在 MySQL 和 SQLite 中不受支持。

DML 触发器

数据操作语言 (**DML**) 触发器在我们尝试在表中 **INSERT**、**UPDATE** 和 **DELETE** 数据时运行。这些触发器对于维护数据完整性和自动化任务很有用。在插入数据之前验证数据很有帮助。

示例

SQL 查询

DELETE FROM employees WHERE employee_id = 101;

输出

You cannot delete records from the employee's table.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

说明

在上面的示例中，我们在员工表上创建了一个触发器来防止删除记录。当我们想删除一行时，它会自动触发并显示消息“**您不能从员工表中删除记录**”，并使用 ROLLBACK 撤销该操作。

注意：DML 触发器在 MySQL、SQLite、SQL Server、Oracle 和 PostgreSQL 等数据库中受支持，但 SQL Server 数据库仅支持 AFTER 触发器。

BEFORE 和 AFTER 触发器

BEFORE 和 AFTER 触发器是在特定事件发生之前和之后触发的操作。

BEFORE 触发器在从表中插入、更新和删除数据之前开始。在数据存储到表中之前验证和修改数据很有帮助。

AFTER 触发器在事件完成后开始。它用于日志记录和审计更改。它也有助于在其他表中级联更新和删除。

SQL 中有六种 BEFORE 和 AFTER 触发器类型：

1. **AFTER INSERT 触发器**

在向表中插入数据后调用此触发器。

2. **AFTER UPDATE 触发器**

在修改表中的数据后，在 SQL 中调用此触发器。

3. **AFTER DELETE 触发器**

在从表中删除数据后调用此触发器。

4. **BEFORE INSERT 触发器**

在向表中插入记录之前调用此触发器。

5. **BEFORE UPDATE 触发器**

在更新表中的记录之前调用此触发器。

6. **BEFORE DELETE 触发器**

在从表中删除记录之前调用此触发器。

现在，我们将创建一个 **books** 和 **book_log** 表，然后将所有 AFTER 和 BEFORE 触发器应用于它们。

创建“books”表

创建“book_log”表

我们已经创建了这两个表，但它们仍然是空的。我们已经创建了表的结构，并在理解触发器的同时插入数据。

示例 1：AFTER INSERT 触发器

在此示例中，我们正在创建一个 AFTER INSERT 触发器。

创建触发器

添加一本新书

检查日志表

输出

表 book_log

说明

在上面的示例中，我们创建了一个 **after_insert_books** 触发器。此触发器在新行插入 **books** 表后自动触发。当新书添加到 books 表时，**触发器**运行并将日志条目插入 **book_log** 表。

正如我们在输出中看到的，日志条目确认书名“**炼金术士**”已成功添加，并附有时间戳。

示例 2：AFTER UPDATE 触发器

在此示例中，我们正在创建一个 AFTER UPDATE 触发器。

创建触发器

更新书籍价格

检查日志表

输出

表 book_log

说明

在上面的示例中，我们创建了一个 **after_update_books** 触发器，该触发器在更新后将日志输入 **book_log** 表。在我们更新书籍价格后，**触发器**运行并将日志条目插入 **book_log** 表。

在输出中，**log_id 1** 是在 **示例 1** 中由 AFTER INSERT 触发器创建的。**log_id 2** 是在此示例中由 **book_log** 表中的 **AFTER UPDATE 触发器**生成的，这证实了书籍“**炼金术士**”的价格已更新为 **349.00**。

示例 3：AFTER DELETE 触发器

在此示例中，我们正在创建一个 AFTER DELETE 触发器。

创建触发器

删除一本书

检查日志表

输出

表 book_log

说明

在上面的示例中，我们创建了一个 **after_delete_books** 触发器，该触发器在删除行后将日志输入 **book_log** 表。当我们从 **books** 表中删除一行时，**触发器**运行并将日志条目插入 **book_log** 表。

正如我们在输出中看到的，**log_id 1** 和 **log_id 2** 是由前面示例中的 AFTER INSERT 和 AFTER UPDATE 触发器创建的。**log_id 3** 是在此示例中由 **AFTER DELETE 触发器**创建的，这证实了书籍“**炼金术士**”已从 **books** 表中删除。

示例 4：BEFORE INSERT 触发器

在此示例中，我们正在创建一个 BEFORE INSERT 触发器。

创建触发器

添加一本新书

检查日志表

表 book_log

说明

在上面的示例中，我们创建了一个 **before_update_books** 触发器。此触发器在新行插入 **books** 表 **之前** 自动触发。当一本书即将添加到 books 表时，**触发器**运行并将日志条目插入 book_log 表。

正如我们在输出中看到的，**log_id 1、log_id 2** 和 log_id 3 是由前面示例中的 AFTER INSERT、AFTER UPDATE 和 AFTER DELETE 触发器创建的。**log_id 4** 是在此示例中由 **BEFORE INSERT** 触发器生成的，它证实了名为“**思考成就伟大的魔力**”的书即将添加到 **books** 表。

由于我们已在**示例 1** 中创建了 **AFTER INSERT 触发器**，因此它会自动生成 **log_id 5**，确认新的插入。

示例 5：BEFORE UPDATE 触发器

在此示例中，我们正在创建一个 BEFORE UPDATE 触发器。

创建触发器

更新书籍价格

检查日志表

输出

表 book_log

说明

在上面的示例中，我们创建了一个 **before_update_books** 触发器。此触发器在进行更新 **之前** 自动触发。当 **books** 表中即将发生更新时，**触发器**运行并将有关更新的日志条目插入 **book_log** 表。

正如我们在输出中看到的，**log_id 1、log_id 2、log_id 3、log_id 4** 和 **log_id 5** 是在之前的示例中创建的。**log_id 6** 是在此示例中由 **BEFORE UPDATE 触发器**生成的，它证实了名为“**思考成就伟大的魔力**”的书的价格即将更改。

由于我们已在**示例 2** 中创建了 **AFTER UPDATE 触发器**，因此它会自动生成 **log_id 7**，确认新的更新。

示例 6：BEFORE DELETE 触发器

在此示例中，我们正在创建一个 BEFORE DELETE 触发器。

创建触发器

删除一本书

检查日志表

输出

表 book_log

说明

在上面的示例中，我们创建了一个 **before_delete_books** 触发器。此触发器在从 **books** 表删除数据 **之前** 自动触发。当即将发生删除操作时，**触发器**运行并将日志条目插入 **book_log** 表。

在输出中，**log_id 1、log_id 2、log_id 3、log_id 4、log_id 5、log_id 6** 和 **log_id 7** 是在之前的示例中创建的。**log_id 8** 是在此示例中由 **BEFORE DELETE 触发器**生成的，它证实了从 **books 表**中删除了该行。

由于我们已在**示例 3** 中创建了 **AFTER DELETE 触发器**，因此它会自动生成 **log_id 9**，确认删除。

SQL 触发器的优点

自动执行：触发器在插入、更新和删除等任何事件发生时自动执行。我们不需要手动调用它们。

维护数据完整性：为了维护数据完整性和一致性，触发器会在数据更改之前和之后验证数据。

自动级联：当我们对一个表执行删除或插入等操作时。触发器会自动在另一个表中执行这些操作。

节省时间：触发器处理重复性任务，并有助于数据库高效运行。

SQL 触发器的缺点

隐藏逻辑：触发器的逻辑是隐藏的，因为它在后台自动执行。将很难跟踪更改发生的位置。这使得代码的调试和维护变得困难。

测试困难：触发器代码是隐藏的，并在数据库内部工作，很难直接在代码中找到错误。

复杂性增加：由于隐藏执行，触发器增加了复杂性，使其难以追踪。这增加了出错的风险。

可移植性问题：不同类型数据库的触发器语法不同，这降低了代码的可移植性。用其他数据库运行相同的代码很困难。

结论

SQL 触发器是在数据库中发生事件时自动执行的语句。它们有助于维护数据完整性、自动化重复性任务和维护审计日志。它通过跟踪数据更改来在触发器 **之前** 和 **之后** 验证数据。触发器节省时间并在数据更改时执行自动级联。

有各种类型的触发器，如 **DDL、DML** 和 **BEFORE & AFTER** 触发器。用户有效地验证数据并跟踪更改。它使数据库系统自动化，这有助于提高性能。