SQL Server 触发器_sql server 触发器 条件

触发器是一种存储数据库过程，当数据库中发生特定事件（如插入、更新或删除操作）时，会自动调用该触发器。审核SQL Server实例的方法有很多，其中一种方法是使用审核触发器，触发器在SQL Server数据库中扮演着关键的角色，是执行业务规则、维护数据完整性和简化重复操作的不可或缺的工具。

SQL触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。触发器可以响应INSERT、UPDATE或DELETE语句，并在这些操作前后执行预定义的操作，如查询其他表、执行复杂的Transact-SQL语句等。触发器的执行不是由程序直接调用，而是由数据库事件触发。它们常用于加强数据完整性约束和业务规则的实现。

触发器的语法通常包括以下几个部分：

• 触发器名称
• 触发时机：BEFORE或AFTER，指定触发器在DML操作之前或之后执行
• 操作类型：INSERT、UPDATE或DELETE，指定触发器响应的操作类型
• 关联表：指定触发器关联的表名
• 触发条件：可选，用于指定触发器执行的特定条件
• 触发器代码：包含实际要执行的SQL语句或逻辑

如何在 SQL Server 中创建触发器

若要在 SQL Server 中创建触发器，请使用由以下元素组成的 CREATE TRIGGER 语句：

• 触发器名称
• 关联表
• 触发事件（INSERT、UPDATE、DELETE 等）
• 触发器触发时要执行的 SQL 语句

CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT, UPDATE
AS
BEGIN
---SQL statements here---
END;
1
2
3
4
5
6
7

触发器的工作原理

• 事件激活：触发器绑定到特定表或视图，并侦听指定事件（INSERT、UPDATE、DELETE、CREATE 等），当这些事件之一发生时，触发器被激活。
• 代码执行：激活后，触发器将执行一组预定义的 SQL 语句，这些可能包括涉及条件语句、循环和调用其他过程的复杂逻辑。
• 执行范围：触发器可以在行级和语句级别上操作。
  • 行级触发器：行级触发器对受触发事件影响的每一行执行一次，确保触发器的操作在每个受影响行的粒度上应用。
  • 语句级触发器：语句级触发器对触发语句执行一次，而不管它影响的行数。

触发器中的操作

• 删除触发器：可以使用 DROP 命令从数据库中删除触发器。

Drop TRIGGER TriggerName;
1

• 显示触发器：可以使用 SHOW 命令显示数据库中的触发器。

SHOW TRIGGERS
In database_ name;
1
2

• 插入触发器：SQL Server 中的插入触发器是一种特殊类型的存储过程，它自动执行以响应数据库中的 INSERT 事件。

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
AFTER INSERT
AS
BEGIN
-- Insert your trigger logic here.
-- Specify the SQL statements to be executed upon trigger activation.
END
1
2
3
4
5
6
7
8

在这里，schema_name指定触发器的模式，如果没有提供，则使用默认模式(通常是dbo)。接下来，trigger_name是触发器的名称，table_name是触发器操作的表的名称，AFTER INSERT指定应该在向表中插入行之后调用触发器。SQL Server不像其他数据库系统那样支持BEFORE INSERT触发器。接下来，开始…END包含了定义触发器功能的SQL语句。这个块可以包含复杂的逻辑，包括调用其他过程、将数据插入到其他表中等等。

SQL Server 中存在多少种类型的触发器

触发器有四种类型：

• 数据定义语言（DDL）触发器
• 数据操作语言（DML）触发器
• 公共语言运行时（CLR）触发器
• 登录触发器

数据定义语言（DDL）触发器

DDL 触发器为响应 DDL 事件而触发，CREATE、ALTER、DROP、GRANT、DENY 和 REVOKE Transact-SQL 语句可以调用 DDL 触发器。调用的 DDL 触发器可以执行以下功能之一：

• 当数据库架构发生修改时，它们会执行一组过程。
• 它们在另一个表中记录使用这些语句执行的更改。

数据操作语言（DML）触发器

DML 触发器是在执行 DML 语句（如 INSERT、UPDATE 和 DELETE）时执行的过程。这些触发器通过自动创建名为 INSERTED 和 DELETED 的表来帮助进行审核，以记录修改数据库之前和之后的值。

DML 触发器根据其执行时间分为两种类型：

• AFTER 触发器：它们在关联的 DML 操作成功完成后执行，这些用于数据修改后需要执行的操作。
• 代替触发器：它们代替预期的DML操作执行，它们非常适合修改操作行为，例如在将数据写入数据库之前对其进行验证或转换。

公共语言运行时（CLR）触发器

CLR 触发器允许管理员在 SQL Server 实例中创建具有 SQL Server 环境外部引用对象的对象，这是一种特殊类型的触发器，可以直接插入到 .NET 语言代码中，并且在 SQL Server 2008 及更高版本中可用。

登录触发器

发生 SQL Server LOGON 事件时，将触发登录触发器，登录触发器可用于跟踪登录活动、控制登录尝试以及限制对关键 SQL Server 实例的登录。

在 SQL Server 中使用触发器的最佳实践

触发器是SQL Server中的一个强大功能，可以自动执行任务、执行业务规则和维护数据完整性。但是，由于它们对数据库性能和复杂性的潜在影响，应该谨慎使用它们。以下是有效利用触发器的一些关键指导方针：

• 最小化触发器中的逻辑：使触发器中的逻辑尽可能简单高效。
• 文档触发器：维护所有触发器的完整文档，包括其用途和效果，以帮助维护和调试。
• 监控性能：定期检查触发器对性能的影响，并在必要时对其进行优化或重构。

使用 SQL 触发器的好处

• SQL Server 中的触发器可自动执行繁琐的任务，例如记录更改和更新相关数据，从而节省宝贵的时间和精力。
• 触发器执行规则和约束，并确保数据库保持准确和可靠，从而避免数据不一致的困扰。
• 借助触发器，管理员可以将业务规则直接无缝集成到数据库层中，从而确保整个系统的行为一致。
• 触发器可以强制访问控制和审计，保护数据库免受未经授权的访问和恶意活动。

是否有效地审核了 SQL Server 环境

尽管使用触发器审计 SQL Server 在某种程度上是有效的，但是这种方法有很大的局限性，只能为单个表创建触发器。因此，如果有 100 个需要监控的表，管理员则需要创建 100 个触发器，这是一个主要的操作瓶颈。创建审核触发器所需的时间和精力是相当可观的，这意味着审核不能仅仅依赖于触发器。手动审核数据库的管理员应仔细考虑选择 SQL Server。此外，SQL Server 触发器记录的信息不会提供对 SQL Server 环境中可能发生的事件或攻击的见解。

除了使用 SQL 触发器之外，还可以采用其他方法，例如SQL Trace和 SQL Server Profiler、数据库审计规范、服务器审计规范以及命令和控制审计。或者，可以使用日志管理解决方案（如EventLog Analyzer）来有效地审SQL Server日志。

EventLog Analyzer是一种有效的日志管理解决方案，可收集所有SQL Server日志，并为数据库中发生的所有 DDL、DML 和安全相关事件提供直观的报告。它可以为特权滥用、敏感数据泄露、帐户锁定、存储介质暴露、SQL 注入和 DoS 攻击等关键事件提供报告，管理员可以为这些事件设置警报，以便通过电子邮件和短信实时通知。