什么是触发器？

在 SQL Server 数据库管理中，触发器能够在特定的数据库操作发生时自动执行预先定义的 SQL 语句。触发器可以用于实现数据完整性约束、审计跟踪、复杂的业务逻辑等多种功能。本文将深入探讨 SQL Server 触发器(trigger)的概念、类型、工作原理以及实际应用，并通过代码示例进行演示。

触发器的概念

触发器(trigger)是SQL server提供给程序员和数据分析员用来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发。当对一个表进行插入（INSERT）、更新（UPDATE）或删除（DELETE）操作时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

触发器与存储过程的区别是运行方式的不同,触发器不能执行EXECUTE语句调用,而是在用户执行 Transact-SQL 语句时自动触发执行，而存储过程需要用户,应用程序或者触发器显示地调用并执行，这是两者最明显的区别。

例如，当一个新的订单被插入到订单表中时，可以使用触发器自动更新库存表中的库存数量，以确保库存不会出现负数。或者，当一个员工的工资被更新时，可以使用触发器记录工资变更的历史记录，以便进行审计。

触发器的作用

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性,它能够对数据库中的相关表进行级联修改,提高比CHECK约束更复杂的的数据完整性,并自定义错误消息。触发器的主要作用主要有以下几个方面:

1. 强制数据库间的引用完整性
2. 级联修改数据库中所有相关的表,自动触发其它与之相关的操作
3. 跟踪变化,撤销或回滚违法操作,防止非法修改数据
4. 返回自定义的错误消息,约束无法返回信息,而触发器可以
5. 触发器可以调用更多的存储过程

触发器的类型

Sql Server 通常包括三种常规类型的触发器: DML触发器、DDL触发器和登录触发器。

DML 触发器

DML(数据操作语言,Data Manipulation Language)触发器是一些附加在特定表或视图上的操作代码,当数据库服务器中发生数据操作语言事件（如 INSERT、UPDATE 和 DELETE）时执行这些操作。

当遇到下列情形时,应考虑使用DML触发器:

1. 通过数据库中的相关表实现级联更改
2. 防止恶意或者错误的insert、update和delete操作,并强制执行check约束定义的限制更为复杂的其他限制。
3. 评估数据修改前后表的状态,并根据该差异才去措施。

根据触发的时机，DML 触发器又可以分为以下两种：

1. AFTER 触发器：在触发 DML 操作（INSERT、UPDATE 或 DELETE）完成后触发。AFTER 触发器可以用于检查数据的完整性、执行额外的业务逻辑或记录数据变更的历史记录。
2. INSTEAD OF 触发器：在触发 DML 操作被执行之前触发，并替代原始的 DML 操作。INSTEAD OF 触发器可以用于实现复杂的业务逻辑，例如在特定条件下阻止数据的插入、更新或删除，或者将数据插入到其他表中。

DDL 触发器

DDL(数据定义语言,Data Definition Language)触发器是在服务器或者数据库中数据定义语言(主要是以

CREATE,DROP,ALTER开头的语句)作用于数据库对象（如表、视图、存储过程等）时触发的,使用DDL触发器可以防止意外的数据库结构变更或记录数据中的更改或事件操作。

登录触发器

登录触发器将为响应 LOGIN 事件而激发存储过程。SQL Server身份验证阶段完成之后且用户会话实际建立之前激发。因此,来自触发器内部且通常将到达用户的所有消息(例如错误消息和来自 PRINT 语句的消息)会传送到 SQL Server 错误日志。如果身份验证失败,将不激发登录触发器。

按照特殊的执行方式又可以分为：嵌套触发器、递归触发器。

嵌套触发器

概念：

嵌套触发器是指一个触发器在执行过程中触发了另一个触发器的情况。SQL Server 允许触发器进行嵌套调用，但可以通过数据库选项来控制嵌套的层数。

工作原理：

当一个数据操作（如 INSERT、UPDATE 或 DELETE）触发了一个表上的触发器时，如果这个触发器的执行又导致了另一个表上的数据操作，并且该表上也有触发器，那么就可能会发生嵌套触发。

例如，有两个表 TableA 和 TableB，在 TableA 上有一个触发器 TriggerA，当对 TableA 进行数据操作时触发了 TriggerA。而 TriggerA 的执行过程中对 TableB 进行了数据操作，TableB 上有触发器 TriggerB，此时就发生了嵌套触发。

配置与限制：

在 SQL Server 中，可以通过数据库选项“nested triggers”来控制是否允许嵌套触发器以及嵌套的层数。默认情况下，该选项是开启的，允许嵌套触发器，但可以设置为关闭以禁止嵌套触发或限制嵌套的层数。

如果嵌套层数过多，可能会导致性能问题和复杂的调试情况。

递归触发器

概念

递归触发器是一种特殊的触发器，它允许一个表上的触发器在执行过程中再次触发自身。这可以用于处理一些具有递归性质的数据操作。

工作原理

当对一个表进行数据操作时，触发了该表上的触发器。如果这个触发器的执行又导致了对同一个表的相同类型的数据操作，并且数据库选项“recursive triggers”是开启的，那么就会再次触发这个触发器，形成递归调用。

例如，有一个表 TableC，上面有一个触发器 TriggerC。当对 TableC 进行数据操作时触发了 TriggerC，而 TriggerC 的执行过程中又对 TableC 进行了数据操作，此时如果递归触发器选项开启，TriggerC 会再次被触发。

配置与应用场景：

可以通过数据库选项“recursive triggers”来控制是否允许递归触发器。默认情况下，该选项是关闭的。

递归触发器的应用场景相对较少，通常用于处理一些具有层次结构或递归关系的数据，例如组织架构表中上下级关系的更新等。但在使用递归触发器时需要特别小心，因为如果递归没有正确控制，可能会导致无限循环，从而消耗大量的系统资源甚至使数据库陷入死锁。

在使用嵌套触发器和递归触发器时需要谨慎考虑其性能影响和潜在的复杂性，确保数据库的稳定和高效运行。

触发器的工作原理

触发器触发时

1. 系统自动在内存中创建deleted表或inserted表;
2. 只读,不允许修改,触发器执行完成后,自动删除。

inserted表

1. 临时保存了插入或更新后的记录行;
2. 可以从inserted表中检查插入的数据是否满足业务需求;
3. 如果不满足,则向用户发送报告错误消息,并回滚插入操作。

deleted表

1. 临时保存了删除或更新前的记录行;
2. 可以从deleted表中检查被删除的数据是否满足业务需求;
3. 如果不满足,则向用户报告错误消息,并回滚插入操作。

Update操作时

新的记录存入inserted表,旧的记录存入deleted表。

触发器的创建和使用

创建 DML 触发器

以下是创建一个 AFTER INSERT DML 触发器的示例代码，用于在订单表中插入新订单后自动更新库存表中的库存数量：

商品1的原始库存为751

向订单表中插入一条订单，下单10个商品1

执行完插入语句后，查看库存表可以发现商品1的数量自动减10

在这个示例中，我们创建了一个名为UpdateInventory的触发器，它在Orders表上的 INSERT 操作完成后触发。触发器中的 SQL 语句用于更新库存表中的库存数量，将库存数量减去新插入订单中的数量。

创建 DDL 触发器

以下是创建一个 DDL 触发器的示例代码，用于记录数据库结构变更的历史记录：

在这个示例中，我们创建了一个名为AuditDDLChanges的触发器，它在数据库级别上的CREATE_TABLE、ALTER_TABLE和DROP_TABLE操作发生时触发。触发器中的 SQL 语句用于将数据库结构变更的事件类型、对象名称和事件发生的日期插入到一个名为DDLChangeLog的表中，以便进行审计跟踪。

使用 INSTEAD OF 触发器

以下是创建一个 INSTEAD OF INSERT 触发器的示例代码。请注意：MySQL 中没有直接等同于 SQL Server 的INSTEAD OF INSERT触发器，我这里使用BEFORE INSERT触发器来实现类似的逻辑控制。用于在特定条件下阻止数据的插入：

在这个示例中，我们创建了一个名为PreventInsert的触发器，它在orders表上的 INSERT 操作被执行之前触发。触发器中的 SQL 语句用于检查插入的订单中的数量是否小于等于零，如果是，则抛出一个错误消息，阻止数据的插入；如果不是，则将插入的数据插入到订单表中。

触发器的优点和缺点

优点

1. 数据完整性：触发器可以在数据库层面实现数据完整性约束，确保数据的一致性和准确性。例如，可以使用触发器检查插入或更新的数据是否符合特定的业务规则，如必填字段、数据范围等。
2. 自动化业务逻辑：触发器可以自动执行复杂的业务逻辑，减少手动编码的工作量。例如，可以使用触发器在数据插入、更新或删除时自动更新相关表中的数据，或者发送通知邮件等。
3. 审计跟踪：触发器可以用于记录数据变更的历史记录，以便进行审计跟踪。例如，可以使用触发器将数据变更的操作类型、操作时间、操作人员等信息记录到一个审计表中。

缺点

1. 性能影响：触发器的执行会增加数据库的负担，特别是在频繁发生数据修改操作的情况下，可能会影响数据库的性能。因此，在设计触发器时，需要考虑其对性能的影响，并尽量减少不必要的触发器。
2. 复杂性增加：触发器的使用会增加数据库的复杂性，特别是在多个触发器相互作用的情况下，可能会导致难以调试的问题。因此，在设计触发器时，需要考虑其对数据库架构的影响，并尽量保持数据库的简洁性。
3. 可维护性降低：触发器的修改和维护可能会比较困难，特别是在触发器的逻辑比较复杂的情况下。因此，在设计触发器时，需要考虑其可维护性，并尽量使用简单、清晰的逻辑。

总结

触发器是可以在特定的数据库事件发生时自动执行预先定义的 SQL 语句。通常用于实现数据完整性约束、审计跟踪、复杂的业务逻辑等多种功能。然而，触发器的使用也需要谨慎，因为它们可能会增加数据库的复杂性和性能负担。在设计和使用触发器时，需要考虑其对数据库架构、性能和可维护性的影响。

Chat2DB 文档：https://docs.chat2db.ai/zh-CN/docs/start-guide/getting-started

Chat2DB 官网：https://chat2db.ai/zh-CN

Chat2DB GitHub：https://github.com/codePhiliaX/Chat2DB