sql触发器的使用及语法



适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例

创建 DML、DDL 或登录触发器。 触发器是一种特殊类型的存储过程，在数据库服务器中发生事件时自动运行。 如果用户尝试通过数据操作语言 (DML) 事件修改数据，DML 触发器运行。 DML 事件是针对表或视图的 INSERT、UPDATE 或 DELETE 语句。 此类触发器在任何有效事件触发时触发，无论表行是否受影响。 有关详细信息，请参阅 DML Triggers。

DDL 触发器是为了响应各种数据定义语言 (DDL) 事件而运行。 这些事件主要对应于 Transact-SQL CREATE、ALTER 和 DROP 语句，以及执行类似 DDL 操作的某些系统存储过程。

登录触发器是为了响应在建立用户会话时触发的 LOGON 事件而触发。 可以直接使用 Transact-SQL 语句创建触发器，也可以使用程序集方法，它们是在 Microsoft .NET Framework 公共语言运行时 (CLR) 中创建，并上传到 SQL Server 实例中。 使用 SQL Server，可以为任何特定语句创建多个触发器。

Transact-SQL 语法约定

OR ALTER
适用对象：Azure SQL 数据库、SQL Server（从 SQL Server 2016 (13.x) SP1 开始）。

只有在触发器已存在时才对其进行有条件地更改。

schema_name
DML 触发器所属架构的名称。 DML 触发器的范围限定为，对其创建此类触发器的表或视图的架构。 不能为 DDL 或登录触发器指定 schema_name。

将 DDL 或登录触发器的作用域应用于当前服务器。 如果指定了此参数，则只要当前服务器中的任何位置出现 event_type 或 event_group，就会激发该触发器 。

让 CREATE TRIGGER 语句的文本复杂难懂。 使用 WITH ENCRYPTION 可以防止将触发器作为 SQL Server 复制的一部分进行发布。 无法为 CLR 触发器指定 WITH ENCRYPTION。

内存优化表上的触发器需要使用此选项。

有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。

内存优化表上的触发器需要使用此选项。

对于内存优化表上的触发器，此为必需选项，但传统表上的触发器不支持此选项。

无法对视图定义 AFTER 触发器。

最多可以对表或视图定义，每 INSERT、UPDATE 或 DELETE 语句一个 INSTEAD OF 触发器。 还可以对每个都有自己的 INSTEAD OF 触发器的视图定义视图。

无法对使用 WITH CHECK OPTION 的可更新视图定义 INSTEAD OF 触发器。 如果这样做，在将 INSTEAD OF 触发器添加到 WITH CHECK OPTION 指定的可更新视图中时，会导致错误。 先使用 ALTER VIEW 删除此选项，再定义 INSTEAD OF 触发器。

对于 INSTEAD OF 触发器，无法对有引用关系（指定级联操作 ON DELETE）的表使用 DELETE 选项。 同样，也不得对有引用关系（指定级联操作 ON UPDATE）的表使用 UPDATE 选项。

指定应该再添加一个现有类型的触发器。 WITH APPEND 无法与 INSTEAD OF 触发器一起使用，或在显式声明 AFTER 触发器后也无法使用。 为了实现后向兼容性，仅在指定了 FOR（但没有指定 INSTEAD OF 或 AFTER）时，才使用 WITH APPEND。 如果使用的是 EXTERNAL NAME（即触发器是 CLR 触发器），无法指定 WITH APPEND。

CREATE TRIGGER 运行完成后，event_group 还将充当宏，将它涉及的事件类型添加到 sys.trigger_events 目录视图中。

指明触发器不得在复制代理修改触发器涉及的表时运行。

尝试上述操作时，将执行 Transact-SQL 语句中指定的触发器操作。

触发器可以包含任意数量和类型的 Transact-SQL 语句，但也有例外。 有关详细信息，请参阅“备注”。 触发器旨在根据数据修改或定义语句来检查或更改数据；不得向用户返回数据。 触发器中的 Transact-SQL 语句常常包含控制流语言。

DML 触发器使用 deleted 和 inserted 逻辑（概念）表。 它们在结构上类似于定义了触发器的表，即尝试对其执行用户操作的表。 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。 例如，若要检索 表中的所有值，则使用：

有关详细信息，请参阅使用插入的和删除的表。

DDL 和登录触发器使用 EVENTDATA (Transact-SQL) 函数来捕获有关触发事件的信息。 有关详细信息，请参阅使用 EVENTDATA 函数。

使用 SQL Server，可以通过表或视图上的 INSTEAD OF 触发器来更新 text、ntext 或 image 列。

对于内存优化表中的触发器，顶层允许的唯一 sql_statement 是 ATOMIC 块。 ATOMIC 块内允许的 T-SQL 由本地进程内允许的 T-SQL 决定。

< method_specifier >适用于：SQL Server 2008 (10.0.x) 及更高版本。

对于 CLR 触发器，指定程序集与触发器绑定的方法。 该方法不能带有任何参数，并且必须返回空值。 class_name 必须是有效的 SQL Server 标识符，并且它必须作为类存在于可见程序集中。 如果该类有一个使用“.”来分隔命名空间部分的命名空间限定名称，则类名必须用 [] 或“ ”分隔符分隔。 此类不得为嵌套类。

DML 触发器经常用于强制执行业务规则和数据完整性。 SQL Server 通过 ALTER TABLE 和 CREATE TABLE 语句来提供声明性引用完整性 (DRI)。 不过，DRI 不提供跨数据库引用完整性。 引用完整性是指有关表的主键和外键之间的关系的规则。 若要强制实现引用完整性，请在 ALTER TABLE 和 CREATE TABLE 中使用 PRIMARY KEY 和 FOREIGN KEY 约束。 如果触发器表存在约束，便在 INSTEAD OF 触发器运行后且 AFTER 触发器运行前检查这些约束。 如果违反了约束，INSTEAD OF 触发器操作回滚，且 AFTER 触发器不触发。

可使用 sp_settriggerorder 指定，要对表运行的第一个和最后一个 AFTER 触发器。 只能为表中的每个 INSERT、UPDATE 和 DELETE 操作指定一个第一个和最后一个 AFTER 触发器。 如果同一个表上还有其他 AFTER 触发器，这些触发器随机运行。

如果 ALTER TRIGGER 语句更改了第一个或最后一个触发器，那么已修改触发器上设置的第一个或最后一个属性遭删除，必须使用 sp_settriggerorder 重置顺序值。

只有在触发 SQL 语句成功运行后，AFTER 触发器才运行。 判断执行成功的标准是：执行了所有与已更新对象或已删除对象相关联的引用级联操作和约束检查。 AFTER 不会以递归方式触发同一个表上的 INSTEAD OF 触发器。

如果对表定义的 INSTEAD OF 触发器对表运行通常会再次触发 INSTEAD OF 触发器的语句，不会以递归方式调用触发器。 而是处理此语句，就像表中没有 INSTEAD OF 触发器一样，并启动一系列约束操作和 AFTER 触发器执行。 例如，如果将触发器定义为表的 INSTEAD OF INSERT 触发器， 且触发器对同一个表运行 INSERT 语句，那么 INSTEAD OF 触发器启动的 INSERT 语句不会再次调用触发器。 触发器启动的 INSERT 启动以下过程：运行约束操作，并触发为表定义的任何 AFTER INSERT 触发器。

如果对视图定义的 INSTEAD OF 触发器对视图运行通常会再次触发 INSTEAD OF 触发器的语句，不会以递归方式调用触发器。 而是将该语句解析为对视图所依存的基本表进行的修改。 在这种情况下，视图定义必须满足可更新视图的所有约束。 有关可更新视图的定义，请参阅通过视图修改数据。

例如，如果将触发器定义为视图的 INSTEAD OF UPDATE 触发器， 且触发器运行引用同一个视图的 UPDATE 语句，那么 INSTEAD OF 触发器启动的 UPDATE 语句不会再次调用触发器。 而是对视图处理触发器启动的 UPDATE 语句，就像视图中没有 INSTEAD OF 触发器一样。 由 UPDATE 更改的列必须解析到一个基表。 对基表的每次修改都将应用约束并触发为该表定义的 AFTER 触发器。

可以将 Transact-SQL 触发器设计为，根据对具体列的 UPDATE 或 INSERT 修改来执行特定操作。 可在触发器的主体中使用 UPDATE() 或 COLUMNS_UPDATED 来达到此目的。 UPDATE() 可以测试对某个列的 UPDATE 或 INSERT 尝试。 COLUMNS_UPDATED 测试是否有对多列运行的 UPDATE 或 INSERT 操作。 此函数返回指明已插入或已更新哪些列的位模式。

CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用于一个表。

触发器只能在当前的数据库中创建，但是可以引用当前数据库的外部对象。

如果指定了触发器架构名称来限定触发器，则将以相同的方式限定表名称。

在同一条 CREATE TRIGGER 语句中，可以为多种用户操作（如 INSERT 和 UPDATE）定义相同的触发器操作。

如果表有外键定义了级联 DELETE/UPDATE 操作，便无法对此表定义 INSTEAD OF DELETE/UPDATE 触发器。

在触发器内可以指定任意的 SET 语句。 选择的 SET 选项在触发器执行期间保持有效，然后恢复为原来的设置。

如果触发了一个触发器，结果将返回给执行调用的应用程序，就像使用存储过程一样。 为了避免由于触发器触发而向应用程序返回结果，请不要添加返回结果的 SELECT 语句，也不要添加在触发器中执行变量赋值的语句。 如果触发器包含将结果返回给用户的 SELECT 语句或执行变量赋值的语句，需要特殊处理触发器。 必须将返回的结果写入所有允许修改触发器表的应用程序中。 如果必须在触发器中进行变量赋值，则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。

虽然 TRUNCATE TABLE 语句实际上就是 DELETE 语句，但它不会激活触发器，因为操作不记录各个行删除。 不过，只有有权运行 TRUNCATE TABLE 语句的用户，才需要担心是否会无意中这样规避 DELETE 触发器。

无论是否记录，WRITETEXT 语句都不激活触发器。

不得在 DML 触发器中使用下列 Transact-SQL 语句：

• ALTER DATABASE
• CREATE DATABASE
• DROP DATABASE
• RESTORE DATABASE
• RESTORE LOG
• RECONFIGURE

另外，如果对作为触发操作目标的表或视图使用 DML 触发器，也不得在 DML 触发器的主体中使用下列 Transact-SQL 语句。

• CREATE INDEX（包括 CREATE SPATIAL INDEX 和 CREATE XML INDEX）
• ALTER INDEX
• DROP INDEX
• DROP TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• 用于执行以下操作的 ALTER TABLE：
  • 添加、修改或删除列。
  • 切换分区。
  • 添加或删除 PRIMARY KEY 或 UNIQUE 约束。

触发器在事务（隐式或非隐式）中运行，当事务待处理时，它们会锁定资源。 除非事务被确认（使用 COMMIT）或拒绝（使用 ROLLBACK），否则锁定一直存在。 触发器运行时间越长，另一进程被锁定的可能性就越大。 因此，尽可能通过写入触发器来减少持续时间。 缩短持续时间的一种方法是，在 DML 语句更改 0 行时释放触发器。

若要为不更改任何行的命令释放触发器，请使用系统变量 ROWCOUNT_BIG。

下面的 T-SQL 代码片段展示了如何为不更改任何行的命令释放触发器。 此代码应位于每个 DML 触发器的开头：

DDL 触发器启动存储过程来响应事件，就像标准触发器一样。 但与标准触发器不同的是，DDL 触发器并不会为了响应表或视图中的 UPDATE、INSERT 或 DELETE 语句而运行。 相反，它们主要是为了响应数据定义语言 (DDL) 语句而运行。 这些语句类型包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS。 执行类似 DDL 操作的特定系统存储过程也可以触发 DDL 触发器。

有关 DDL 触发器的详细信息，请参阅 DDL 触发器。

DDL 触发器不会为了响应影响本地或全局临时表和存储过程的事件而触发。

与 DML 触发器不同，DDL 触发器的范围不限定为架构。 因此，无法使用 OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 等函数来查询 DDL 触发器的元数据。 请改用目录视图。 有关详细信息，请参阅获取有关 DDL 触发器的信息。

登录触发器是为了响应 LOGON 事件而执行存储过程。 此事件在用户会话通过 SQL Server 实例建立时发生。 登录触发器在登录的身份验证阶段完成后且用户会话建立前触发。 因此，所有源自触发器内部且通常会传递给用户的消息（如错误消息和来自 PRINT 语句的消息）会转移到 SQL Server 错误日志。 有关详细信息，请参阅登录触发器。

如果身份验证失败，登录触发器不会触发。

登录触发器不支持分布式事务。 当包含分布式事务的登录触发器触发时，3969 错误返回。

登录触发器可以有效地阻止所有用户（包括 数据库引擎 sysadmin 固定服务器角色的成员）与 的成功连接。 在登录触发器正在阻止连接时， sysadmin 固定服务器角色的成员可通过使用专用管理员连接，或者通过以最小配置模式 (-f) 启动 数据库引擎 ，来进行连接。 有关详细信息，请参阅 Database Engine Service Startup Options。

SQL Server 的未来版本中将删除从触发器返回结果的功能。 返回结果集的触发器可能会导致无法处理结果集的应用程序出现意外行为。 避免在新的开发工作中从触发器返回结果集，并计划修改当前这样做的应用程序。 若要防止触发器返回结果集，请将 disallow results from triggers 选项设置为 1。

登录触发器始终禁止返回结果集，这种行为不可配置。 如果登录触发器生成了结果集，此触发器会无法启动，且触发了此触发器的登录尝试会遭拒。

使用 SQL Server，可以为每个 DML、DDL 或 LOGON 事件创建多个触发器。 例如，如果 CREATE TRIGGER FOR UPDATE 对已有 UPDATE 触发器的表运行，则会再创建一个 UPDATE 触发器。 在 SQL Server 早期版本中，对于每个表，每个 INSERT、UPDATE 或 DELETE 数据修改事件只允许有一个触发器。

如果使用 ALTER DATABASE 启用了 RECURSIVE_TRIGGERS 设置，SQL Server 还支持以递归方式调用触发器。

递归触发器可以采用下列递归类型：

• 间接递归

  在间接递归中，一个应用程序更新了表 T1。 这触发了触发器 TR1，从而更新了表 T2。 然后，触发器 T2 触发，并更新表 T1。

• 直接递归

  在直接递归中，应用程序更新表 T1。 这触发了触发器 TR1，从而更新了表 T1。 由于表 T1 被更新，将再次触发触发器 TR1，依此类推。

以下示例同时使用了间接和直接触发器递归。假设对表 T1 定义了两个更新触发器 TR1 和 TR2。 触发器 TR1 以递归方式更新表 T1。 UPDATE 语句运行 TR1 和 TR2 各一次。 另外，启动 TR1 也会触发执行 TR1（以递归方式）和 TR2。 指定触发器的 inserted 和 deleted 表包含仅与调用触发器的 UPDATE 语句对应的行。

禁用 RECURSIVE_TRIGGERS 的设置只能阻止直接递归。 若要同时禁用间接递归，请使用 sp_configure 将 nested triggers 服务器选项设置为 0。

如果任一触发器执行了 ROLLBACK TRANSACTION，无论嵌套级别如何，都不会再运行其他任何触发器。

最多可以将触发器嵌套到 32 个级别。 如果一个触发器更改了包含另一个触发器的表，那么第二个触发器激活，然后又可以调用第三个触发器，依此类推。 如果链中任意一个触发器引发了无限循环，则会超出嵌套级限制，从而导致取消触发器。 如果 Transact-SQL 触发器通过引用 CLR 例程、类型或聚合来启动托管代码，此引用作为一级计入 32 级嵌套限制。 从托管代码内部调用的方法不计入此限制。

若要禁用嵌套触发器，请用 sp_configure 将 nested triggers 选项设置为 0（关闭）。 默认配置支持嵌套触发器。 如果禁用嵌套触发器，递归触发器也遭禁用，不管使用 ALTER DATABASE 设置的 RECURSIVE_TRIGGERS 设置如何。

即使“嵌套触发器”服务器配置选项为 0，嵌套在 INSTEAD OF 触发器内的第一个 AFTER 触发器也会触发。 不过，在此设置下，后面的 AFTER 触发器不会触发。 当“嵌套触发器”服务器配置选项设置为 0 时，检查应用程序中是否有嵌套触发器，以确定应用程序是否遵循业务规则。 如果不遵循，请进行适当修改。

使用 SQL Server，Transact-SQL 存储过程、触发器和批处理可以引用在编译时不存在的表。 这种功能称为延迟名称解析。

必须对要为其创建触发器的表或视图拥有 ALTER 权限，才能创建 DML 触发器。

必须对服务器拥有 CONTROL SERVER 权限，才能创建具有服务器范围 (ON ALL SERVER) 的 DDL 触发器或登录触发器。 必须在当前数据库中拥有 ALTER ANY DATABASE DDL TRIGGER 权限，才能创建具有数据库范围 (ON DATABASE) 的 DDL 触发器。

如果有人试图在 AdventureWorks2022 数据库的 表中添加或更改数据，以下 DML 触发器将向客户端显示一条消息。

如果 表发生更改，以下示例将向指定人员 () 发送电子邮件。

由于 CHECK 约束只引用定义了列级别或表级别约束的列，因此必须将任何跨表约束（在本例中是业务规则）定义为触发器。

以下示例在 数据库中创建 DML 触发器。 此触发器会进行检查，以确保在有人试图将新采购订单插入 表时，供应商的信用分级良好（不为 5）。 必须引用 表，才能获取供应商的信用分级。 如果信用分级太低，便会显示消息，且不执行插入操作。

下面的示例使用 DDL 触发器来防止从数据库中删除任何同义词。

在以下示例中，如果当前服务器实例上出现任何 CREATE DATABASE 事件，则使用 DDL 触发器输出一条消息，并使用 函数检索对应 Transact-SQL 语句的文本。 有关在 DDL 触发器中使用 EVENTDATA 的更多示例，请参阅使用 EVENTDATA 函数。

适用于：SQL Server 2008 (10.0.x) 及更高版本。

下面的登录触发器示例拒绝了作为 login_test 登录名的成员登录 SQL Server 的尝试（如果在此登录名下已运行三个用户会话）。

适用于：SQL Server 2008 (10.0.x) 及更高版本。

以下示例将查询 和 目录视图，以确定是哪个 Transact-SQL 语言事件导致触发了 。 在示例“D”中创建了触发器 （可在上面找到）。

ALTER TABLE (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
COLUMNS_UPDATED (Transact-SQL)
CREATE TABLE (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
TRIGGER_NESTLEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)
sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL)
sp_helptext (Transact-SQL)
sp_rename (Transact-SQL)
sp_settriggerorder (Transact-SQL)
UPDATE() (Transact-SQL)
获取有关 DML 触发器的信息
获取有关 DDL 触发器的信息
sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)

版权声明：

本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。

本文网址：https://www.mushiming.com/mjsbk/1316.html