SQL 触发器：基本指南

已发表: 2022-10-12

触发器是 SQL Server 中的数据库实体。从技术上讲，它们是响应特定数据库操作的特定类函数调用。

本基本指南将为您提供有关 SQL 触发器的深入信息，这些信息对您的职业非常有用。让我们开始吧！

什么是 SQL 触发器？

“触发器”一词描述了每次更改数据库中的内容时服务器自动执行查询的语句。

触发器是存储在内存空间中的一组特殊命名的 SQL 查询。它是一种特定类型的函数调用，每当发生任何数据库事件时都会立即调用它。每个触发器都有一个分配给它的表。

例如，只要将新列添加到特定表或更改特定记录，就可以激活触发器。

根据 Microsoft Developer Network，触发器是一类特殊的存储过程。在触发器语句中，我们最初定义触发器应该何时运行，然后我们提供触发器激活后应该采取的操作。

句法：

 CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];

各参数说明

CREATE TRIGGER trigger_name – 用于构造触发器或更改现有触发器的名称。
BEFORE/AFTER – 此查询用于定义触发器的执行时间（在某个事件之前或之后）。
INSERT/UPDATE/DELETE - 这描述了我们希望对表执行的操作。
ON tableName – 在这里，我们定义表名来设置触发器。
FOR EACH ROW – 此语句与行触发器相关，这意味着只要更改行，就会执行触发器。
trigger_body – 它指定触发器被激活时要采取的操作。

触发器是具有独特身份的存储函数，使我们能够重用已经执行并安全存储在内存中的查询。现在让我们试着理解为什么 SQL 需要它们。

触发器主要用于在事件发生时规范代码的部署。换句话说，如果您需要不断运行特定的代码片段以响应指定的事件，那么使用触发器是理想的选择。

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

在受影响的表中插入、更新或删除数据时执行附加检查。
缩短响应时间，这有助于提高计算费用。
启用初始约束无法访问的复杂默认参数的编码。

参照完整性是关系数据库系统的关键核心属性。这意味着保存在数据库系统中的数据对于每个事务和每个操作都必须始终准确。

如果两个表位于不同的数据库或系统上，则无法使用约束值确保其中的数据验证。在这种情况下，触发器是唯一的执行选项。

触发参数的组合

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

BEFORE INSERT ：这些触发器在对指定表或数据库执行任何 INSERT 操作之前对行执行操作。

AFTER INSERT ：它在任何数据库 INSERT 活动之后立即对行执行操作。

BEFORE UPDATE ：使用这些触发器，在对数据库执行 UPDATE 操作之前对行执行一个函数。

AFTER UPDATE ：它在任何数据库或特定表 UPDATE 活动之后立即对行执行操作。

BEFORE DELETE ：它甚至在数据库或表受到 DELETE 操作之前对行执行特定操作。

AFTER DELETE ：这些触发器在每个 DELETE 事务之后的行上执行操作。

SQL 触发器的类型

SQL 触发器是在特定事件发生时立即运行的存储函数。它类似于事件驱动的调度。后续情况可以开始执行触发器。

DML 触发器——DML 代表数据操作语言。使用 DML 触发器可以执行代码以响应数据修改。当执行 INSERT、UPDATE 和 DELETE 等 DML 命令时，将激活此触发器。这些也称为“表级触发器”。

DDL 触发器——DDL 代表数据定义语言。 DDL 触发器使我们能够运行代码以响应数据库架构更改（例如添加或删除表）或服务器事件（例如用户签入时）。这些称为“数据库级触发器”。

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

登录触发器– 每当发生任何登录（启动、登录、注销、关闭）事件时，都会立即调用登录触发器。即使在用户事务启动之前，它们也仅在用户身份验证过程之后执行。如果授权失败，则不会触发 LOGON 触发器。

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

CLR 触发器——CLR 代表公共语言运行时。 CLR 触发器确实是主要基于 .NET 技术中的 CLR 构建的触发器的独特子集。如果触发器需要执行大量计算或需要与 SQL 以外的实体相关联，这些触发器会很有帮助。

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;

由于我们需要替换行数据而不是使用旧数据，因此我们使用新的类名定义了“total”，并且所有后续表达式都使用点运算符在 total 之后使用新关键字作为前缀。现在，我们将为每一行添加值并查看结果。最初，每个学生的总分都是 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);

在这种情况下，当向学生表中插入数据时，将自动触发触发器语句。每个学生的总分将由触发器计算。现在，让我们看看是否使用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] = 'Trigger_name'

查询以显示触发器

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

 SHOW TRIGGERS;

查询禁用触发器

以下命令停用工作数据库中的触发器。

 DISABLE TRIGGER trigger_name ON DATABASE;

您还可以指定某个表名来禁用触发器。

 DISABLE TRIGGER trigger_name ON table_name;

查询以启用触发器

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

 ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name

在尝试启用触发器之前，必须禁用触发器，

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

使用上面的 SQL 语句，我们可以通过用“ALL”代替特定的触发器名称来一次停用或激活所有表触发器。

 ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL

查询删除或删除触发器

可以通过删除触发器或整个表来消除触发器。当一个表被删除时，每个相关的触发器也会被删除。

 DROP TRIGGER [trigger_name];

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

触发器的优点

构建触发器很简单，触发器本身可以调用存储的函数和方法。
用户可以使用触发器实现简单的审计。
可悲的是，您无法使用 SQL Server 在数据库系统中创建跨实体的约束，尽管您可以通过使用触发器来模拟约束的操作。
完整性约束可以使用触发器跨数据库实现。
当需要组验证而不是对新输入或更改的数据进行逐行验证时，触发器会很有帮助。

触发器的缺点

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

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

结论

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

递归可以通过触发器启用。每当表上的触发器在父表上执行命令时，触发器的第二次迭代就会被触发，这称为递归触发器。这在尝试解决身份相关性时有所帮助。

此外，触发器规定了允许数据库接受的更新模式。如果 SQL 约束键主要不存在主键和外键，那么在数据库系统中保持数据完整性约束是非常有益的。

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

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