SQL触发器：基本指南

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

这本必备指南将为您提供关于sql触发器的深入信息，这在您的职业中可能非常有用。让我们开始吧！

什么是sql触发器？

“触发器”一词描述了服务器自动执行查询的语句，每当数据库中的内容发生变化时。

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

例如，当向特定表添加新列或更改特定记录时，可以激活触发器。

根据微软开发者网络的说法，触发器是一类存储过程。在触发器语句中，我们首先定义触发器应该何时运行，然后提供触发器激活后应采取的操作。

语法：

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数据库操作中使用触发器的一些好处如下：

1. 在向受影响表插入、更新或删除数据时执行额外的检查。
2. 减少响应时间，有助于提高计算费用。
3. 可以编码不同于初始约束的复杂默认参数。

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

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

触发器参数的组合

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

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

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

before update：在对数据库执行update操作之前，对行执行操作。

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

before delete：它甚至在数据库或表被删除操作之前对行执行某些操作。

after delete：这些触发器在每个delete事务之后对行执行操作。

sql触发器的类型

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

dml触发器 – dml代表数据操纵语言。使用dml触发器可以在数据修改时执行代码。当执行dml命令（如insert，update和delete）时，将激活此触发器。这些也称为“表级触发器”。

ddl触发器 – ddl代表数据定义语言。ddl触发器使我们能够根据数据库模式更改（如添加或删除表）或服务器事件（如用户签入时）运行代码。这些称为“数据库级触发器”。

当在活动数据库中执行某些ddl语句（如create，alter或drop）时，可以激活这些触发器。它们还可以用于监视和管理执行的活动。

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

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

clr触发器 – clr代表公共语言运行时。clr触发器实际上是在.net技术中主要基于clr构建的一种独特子集触发器。如果触发器需要执行大量计算或需要与sql以外的实体相关联，则这些触发器很有帮助。

可以通过启用.net技术（包括visual basic、c#和f-sharp）中支持clr触发器的编码来构建dml和ddl触发器。

示例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之后使用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。