SQL 触发器：基本指南

0 Shares

触发器是 SQL Server 数据库中的重要组成部分。从本质上讲，它们是预先定义好的函数，当数据库中发生特定的操作时，这些函数会被自动调用。

本文将深入探讨 SQL 触发器的概念，这对于您的职业生涯至关重要。让我们开始吧！

什么是 SQL 触发器？

简单来说，“触发器”是指数据库中发生数据变更时，服务器自动执行的一段 SQL 代码。

触发器是一组特殊的、预先命名的 SQL 查询语句，它们存储在数据库的内存空间中。每当数据库中发生特定的事件时，它们就会像函数一样被调用。每个触发器都与特定的表相关联。

例如，当向某个表中添加新的列或修改某条记录时，可以激活相应的触发器。

根据微软开发人员网络（Microsoft Developer Network）的定义，触发器是一种特殊的存储过程。在触发器的定义中，我们首先指定触发器何时应该运行，然后定义触发器激活后应该执行的操作。

语法：

触发器的基本语法如下：

CREATE TRIGGER 触发器名称
BEFORE/AFTER
INSERT/UPDATE/DELETE
ON 表名称
FOR EACH ROW SET 操作 [触发器主体];

参数解释：

CREATE TRIGGER 触发器名称：用于创建新的触发器或修改已有的触发器。
BEFORE/AFTER：定义触发器执行的时间，即在事件发生之前还是之后。
INSERT/UPDATE/DELETE：指定触发器响应的操作类型，例如插入、更新或删除数据。
ON 表名称：指定触发器所关联的表。
FOR EACH ROW：表示这是一个行级触发器，即每次对表中的一行数据进行更改时都会触发。
trigger_body：定义触发器被激活时要执行的具体操作。

触发器是一种具有唯一标识的存储函数，它使我们可以复用已经执行并安全存储在内存中的查询。那么，为什么我们需要使用触发器呢？

触发器的主要用途是在特定事件发生时，规范代码的执行。换句话说，如果您需要响应特定的数据库事件而自动运行一段代码，那么触发器是一个理想的选择。

以下是在 SQL 数据库操作中使用触发器的一些好处：

在对表进行插入、更新或删除操作时，可以执行额外的检查。
可以缩短响应时间，从而有助于提高计算效率。
可以实现初始约束无法实现的复杂默认参数设置。

参照完整性是关系数据库系统的关键属性。这意味着数据库中的数据在每次事务和操作中都必须保持准确性。

当两个表位于不同的数据库或系统上时，不能使用约束值来保证数据验证。在这种情况下，触发器是唯一可行的选择。

触发参数的组合

对于每个表，我们可以指定六种不同的触发器类型。这些是 SQL 行级触发器中包含的触发参数组合。

BEFORE INSERT：在对指定表执行任何 INSERT 操作之前，对行执行操作。

AFTER INSERT：在任何 INSERT 操作之后，立即对行执行操作。

BEFORE UPDATE：在对数据库执行 UPDATE 操作之前，对行执行操作。

AFTER UPDATE：在任何 UPDATE 操作之后，立即对行执行操作。

BEFORE DELETE：在删除操作发生之前，对行执行特定操作。

AFTER DELETE：在每个 DELETE 事务之后，对行执行操作。

SQL 触发器的类型

SQL 触发器是在特定事件发生时自动运行的存储函数，类似于事件驱动的调度机制。触发器可以由各种情况触发执行。

DML 触发器：DML 代表数据操作语言。DML 触发器用于响应数据修改操作。当执行 INSERT、UPDATE 和 DELETE 等 DML 命令时，DML 触发器会被激活。这些触发器也被称为“表级触发器”。

DDL 触发器：DDL 代表数据定义语言。DDL 触发器允许我们响应数据库架构更改，例如添加或删除表，或者响应服务器事件，例如用户签入。这些触发器被称为“数据库级触发器”。

当在活动数据库中执行某些 DDL 语句（例如 CREATE、ALTER 或 DROP）时，DDL 触发器会被激活。它们也可用于监视和管理正在执行的数据库活动。

登录触发器：每当发生登录（启动、登录、注销、关闭）事件时，登录触发器就会被调用。它们在用户身份验证过程之后，甚至在用户事务启动之前执行。如果授权失败，则不会触发登录触发器。

登录触发器可用于记录登录历史、为特定登录建立事件限制以及进行其他服务器连接的审计和身份管理。

CLR 触发器：CLR 代表公共语言运行时。CLR 触发器是一种基于 .NET 技术构建的特殊触发器。如果触发器需要执行大量的计算或需要与 SQL 以外的实体进行交互，那么 CLR 触发器会非常有用。

DML 和 DDL 触发器都可以通过启用 .NET 技术（包括 Visual Basic、C# 和 F-sharp）支持的 CLR 触发器的编码来构建。

示例 SQL Server 触发器

让我们通过一个例子来理解触发器的概念。

首先，使用 SQL 语句创建一个数据库。

CREATE DATABASE testdb;
use testdb;

这里，我们创建了一个名为“testdb”的数据库。接下来，我们创建一个表。

CREATE TABLE student(
  name varchar(25),
  id int(2),
  maths int(2),
  physics int(2),
  biology int(2),
  social int(2),
  total int(2)
 );

我们创建了一个用于存储学生信息的表。这个表名为“student”。

DESC student;

这是我们创建的表的结构。

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(25) | YES  |     | NULL    |       |
| id      | int         | YES  |     | NULL    |       |
| maths   | int         | YES  |     | NULL    |       |
| physics | int         | YES  |     | NULL    |       |
| biology | int         | YES  |     | NULL    |       |
| social  | int         | YES  |     | NULL    |       |
| total   | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

创建表后，下一步是设置触发器。让我们尝试使用 BEFORE INSERT 参数。

我们创建的触发器名为“marks”。当学生成绩被添加到表中时，此触发器将自动计算学生的总成绩。

CREATE TRIGGER marks
BEFORE INSERT
ON
student
FOR EACH ROW
set new.total=new.maths+new.physics+new.biology+new.social;

由于我们需要替换行数据而不是使用旧数据，我们使用 new 关键字来引用新插入的数据。现在，我们将为每行添加值并查看结果。最初，每个学生的总分都是 0。

INSERT INTO student VALUES("George",02,99,87,92,91,0);
INSERT INTO student VALUES("James",03,91,81,94,90,0);
INSERT INTO student VALUES("Harry",04,86,70,73,88,0);
INSERT INTO student VALUES("John",05,73,89,78,92,0);
INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);

当数据插入 student 表时，触发器语句会自动执行，并计算每个学生的总分。现在，让我们使用 SELECT 语句来查看触发器是否被成功调用。

SELECT * FROM table_name;

以下是最终的输出结果：

mysql> select * from student;
+--------+------+-------+---------+---------+--------+-------+
| name   | id   | maths | physics | biology | social | total |
+--------+------+-------+---------+---------+--------+-------+
| George |    2 |    91 |      81 |      94 |     90 |   356 |
| James  |    3 |    86 |      70 |      73 |     88 |   317 |
| Harry  |    4 |    73 |      89 |      78 |     92 |   332 |
| John   |    5 |    94 |      75 |      69 |     79 |   317 |
| Lisa   |    1 |    99 |      87 |      92 |     91 |   369 |
+--------+------+-------+---------+---------+--------+-------+
5 rows in set (0.00 sec)

在上面的结果中，您可以看到每个学生的各科目分数被自动累加，得出总分。这说明我们的触发器被成功调用了。

附加触发操作

我们可以使用触发器执行许多操作，有些操作很简单，有些操作则稍微复杂一些。使用 Transact-SQL 语句，可以使用以下命令启用、禁用或删除触发器。

查询以检查特定的触发器是否存在

以下命令用于检查整个数据库中是否存在指定的触发器。

SELECT * FROM [sys].[triggers] WHERE [name] = '触发器名称'

查询以显示触发器

以下语句用于显示当前活动数据库中可用的所有触发器。

SHOW TRIGGERS;

查询禁用触发器

以下命令用于禁用当前活动数据库中的触发器。

DISABLE TRIGGER 触发器名称 ON DATABASE;

也可以指定表名来禁用特定表的触发器。

DISABLE TRIGGER 触发器名称 ON 表名称;

查询以启用触发器

以下命令首先禁用在活动数据库中指定表上定义的触发器，然后再重新启用它。

ALTER TABLE 表名称 DISABLE TRIGGER 触发器名称

ALTER TABLE 表名称 ENABLE TRIGGER 触发器名称

在尝试启用触发器之前，必须先将其禁用。

查询以启用或禁用表中的所有触发器

使用上述 SQL 语句，可以通过将特定的触发器名称替换为 “ALL” 来一次禁用或启用所有表触发器。

ALTER TABLE 表名称 DISABLE TRIGGER ALL

ALTER TABLE 表名称 ENABLE TRIGGER ALL

查询删除或删除触发器

可以通过删除触发器或整个表来消除触发器。当一个表被删除时，与该表相关联的所有触发器也会被自动删除。

DROP TRIGGER [触发器名称];

每当删除触发器时，相关数据也会从 sys.objects 数据表中删除。

触发器的优点

触发器的创建很简单，并且触发器本身可以调用存储的函数和方法。
用户可以使用触发器来实现简单的审计功能。
虽然不能直接使用 SQL Server 在数据库系统中创建跨实体的约束，但您可以通过触发器来模拟约束的操作。
可以使用触发器跨数据库实现完整性约束。
当需要执行数据组的验证，而不是对每个新输入或更改的数据进行逐行验证时，触发器非常有用。

触发器的缺点

由于一些固有的局限性，在某些情况下，SQL 触发器可能不是最佳选择。

触发器必须被准确地记录和维护。
由于应用程序组件可能无法访问同时执行的数据库，触发器可能难以调试。
当使用触发器时，DML 语句可能会变得更加复杂。
即使是轻微的触发器问题，也可能导致语句出现逻辑错误。

结论

触发器是 Transact-SQL 和 SQL 中非常有用的组件，在 Oracle 数据库中也可以使用它们。当需要调用存储方法时，触发器的使用至关重要。这些 SQL 触发器允许我们分析活动时间线并决定如何响应它们。我们还可以检查与触发器关联的表以获取相关数据。

触发器可以实现递归操作。当父表上的触发器执行命令时，表上的触发器可能会被第二次调用，这被称为递归触发器。这在尝试解决身份相关性问题时很有帮助。

此外，触发器还规定了数据库可以接受的更新模式。如果 SQL 约束键主要不包含主键和外键，那么在数据库系统中维护数据完整性约束就非常重要。

希望这篇文章对您理解 SQL 触发器有所帮助。

如果您想更深入地学习数据库，这里有一些学习 SQL 和 NoSQL 的优质资源。