CREATE TRIGGER （Transact-SQL）

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

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

DDL 触发器在响应各种数据定义语言 （DDL） 事件时运行。 这些事件主要对应于 Transact-SQL CREATE、 ALTER语句以及 DROP 执行类似 DDL 的作的某些系统存储过程。

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

重要

触发器内部的恶意代码可以在升级后的权限下运行。 有关如何缓解此威胁的详细信息，请参阅 “管理触发器安全性”。

注意

本文介绍了将 .NET Framework CLR 集成到 SQL Server。 CLR 集成不适用于 Azure SQL 数据库。

Transact-SQL 语法约定

语法

SQL Server 语法

对INSERT表或DELETE视图的触发器或UPDATE语句（DML 触发器）：

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME <method_specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

对表的INSERT触发器（UPDATEDELETE内存优化表上的 DML 触发器）：

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement  [ ; ] [ , ...n ] }

<dml_trigger_option> ::=
    [ NATIVE_COMPILATION ]
    [ SCHEMABINDING ]
    [ EXECUTE AS Clause ]

在 、ALTER、、DROP、GRANTDENY、 REVOKE或UPDATE语句上CREATE触发 （DDL 触发器）：

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

事件上的 LOGON 触发器（登录触发器）：

CREATE [ OR ALTER ] TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ , ...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Azure SQL 数据库语法

对INSERT表或DELETE视图的触发器或UPDATE语句（DML 触发器）：

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
 [ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
  AS { sql_statement  [ ; ] [ , ...n ] [ ; ] > }

<dml_trigger_option> ::=
        [ EXECUTE AS Clause ]

在 、ALTER、、DROP、GRANTDENY、 REVOKE或UPDATE STATISTICS语句上CREATE触发 （DDL 触发器）：

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { DATABASE }
 [ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ]  [ ; ] }

<ddl_trigger_option> ::=
    [ EXECUTE AS Clause ]

参数

或 ALTER

适用于：SQL Server 2016 （13.x） SP1 及更高版本以及 Azure SQL 数据库

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

schema_name

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

trigger_name

触发器的名称。 trigger_name必须遵循标识符规则，但trigger_name不能以#或开头##。

tableview

对其运行 DML 触发器的表或视图。 此表或视图有时称为“触发器表”或“触发器视图”。 可以根据需要指定表或视图的完全限定名称。 只能按 INSTEAD OF 触发器引用视图。 无法对本地或全局临时表定义 DML 触发器。

数据库

将 DDL 触发器的作用域应用于当前数据库。 如果指定了此参数，则只要当前数据库中出现 event_type 或 event_group，就会激发该触发器 。

ALL SERVER

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

WITH ENCRYPTION

遮盖语句的文本 CREATE TRIGGER 。 使用 WITH ENCRYPTION 可防止触发器作为 SQL Server 复制的一部分发布。 WITH ENCRYPTION 不能为 CLR 触发器指定。

以...身份执行

指定用于执行该触发器的安全上下文。 允许您控制 SQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。

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

有关详细信息，请参阅 EXECUTE AS 子句。

本地编译

指示触发器已本机编译。

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

SCHEMABINDING

确保无法删除或更改触发器引用的表。

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

FOR |后

FOR 或 AFTER 指定仅当触发 SQL 语句中指定的所有作已成功启动时，DML 触发器才会触发。 所有引用级联操作和约束检查也必须在此触发器触发前成功启动。

不能在视图上定义 AFTER 触发器。

而不是

指定 DML 触发器（而不是触发 SQL 语句）启动，因此替代触发语句的操作。 不能为 DDL 或登录触发器指定 INSTEAD OF 。

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

不能在使用的WITH CHECK OPTION可更新视图上定义INSTEAD OF触发器。 这样做会导致将 INSTEAD OF 触发器添加到指定的可更新视图 WITH CHECK OPTION 时出错。 在定义INSTEAD OF触发器之前，ALTER VIEW可以使用该选项删除该选项。

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

指定数据修改语句，用于在 DML 触发器尝试对此表或视图触发时激活触发器。 至少指定一个选项。 在触发器定义中使用这些选项的任意顺序组合。

对于 INSTEAD OF 触发器，不能对具有引用关系的表使用 DELETE 选项，指定级联作 ON DELETE。 同样， UPDATE 不允许对具有引用关系的表使用此选项，并指定级联作 ON UPDATE。

WITH APPEND

适用于：SQL Server 2008 (10.0.x) 至 SQL Server 2008 R2 (10.50.x)。

指定应该再添加一个现有类型的触发器。 WITH APPEND 不能与触发器一起使用 INSTEAD OF ，也不能 AFTER 将触发器显式声明。 为了向后兼容，仅在指定时FOR使用，而不INSTEAD OF使用WITH APPEND或AFTER不使用 。 不能指定 WITH APPEND 是否使用 EXTERNAL NAME （即触发器是 CLR 触发器）。

event_type

启动后触发 DDL 触发器的 Transact-SQL 语言事件的名称。 DDL 事件中列出了 DDL 触发器的有效事件。

event_group

预定义的 Transact-SQL 语言事件分组的名称。 DDL 触发器在任何属于 event_group 的 Transact-SQL 语言事件启动后触发。 DDL 事件组中列出了 DDL 触发器的有效事件组。

CREATE TRIGGER运行完成后，event_group还可以通过将它涵盖的事件类型添加到sys.trigger_events目录视图来充当宏。

NOT FOR REPLICATION

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

sql_statement

触发条件和操作。 触发器条件指定其他用于确定尝试的 DML、DDL 或 LOGON 事件是否导致触发器操作运行的条件。

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

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

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

SELECT * FROM deleted;

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

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

SQL Server 允许通过INSTEAD OF表或视图上的触发器更新文本、ntext 或图像列。

重要

Microsoft SQL Server 的未来版本中将删除 ntext、text 和 image 数据类型。 请避免在新开发工作中使用这些数据类型，并考虑修改当前使用这些数据类型的应用程序。 请改用 nvarchar（max）、varchar（max）和 varbinary（max）。 AFTER触发器INSTEAD OF都支持插入和删除的表中的 varchar（max）、nvarchar（max）和 varbinary（max） 数据。

对于内存优化表上的触发器，在顶级允许的唯 一sql_statement 是块 ATOMIC 。 块中 ATOMIC 允许的 T-SQL 受本机 procs 中允许的 T-SQL 的限制。

<method_specifier>

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

注意

默认情况下，SQL Server 无法运行 CLR 代码。 可以创建、修改和删除引用托管代码模块的数据库对象，但这些引用不会在 SQL Server 实例中运行，除非启用了 clr 的选项 启用了 sp_configure。

DML 触发器的备注

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

可以使用 指定要在表上运行的第一个和最后一个触发器。 只能为每个INSERT触发器指定一个第一个和一个最后AFTER一个触发器，UPDATE对DELETE表执行作。 如果同一表上有其他 AFTER 触发器，则会随机运行它们。

如果语句更改了第一个或最后一个触发器，则删除修改后的触发器上设置的第一个或最后一个属性，并且必须使用 <a0/> 重置订单值。

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

INSTEAD OF如果对表定义的触发器针对通常再次触发INSTEAD OF触发器的表运行语句，则不会以递归方式调用触发器。 相反，语句会像表没有 INSTEAD OF 触发器一样处理，并启动约束作和 AFTER 触发器执行链。 例如，如果将触发器定义为 INSTEAD OF INSERT 表的触发器。 并且，如果触发器在同一个表上运行语句 INSERT ， INSERT 则触发器启动的 INSTEAD OF 语句不会再次调用触发器。 触发器 INSERT 启动的进程将启动运行约束作并触发为表定义的任何 AFTER INSERT 触发器。

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

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

测试特定列的 UPDATE 或 INSERT作

可以设计 Transact-SQL 触发器，以便根据 UPDATE 特定列或修改 INSERT 某些作。 为此，请使用触发器正文中的 UPDATE 或 COLUMNS_UPDATED 。 UPDATE() 对一列进行测试 UPDATE 或 INSERT 尝试。 COLUMNS_UPDATED UPDATE测试或INSERT针对多个列运行的作。 此函数返回指明已插入或已更新哪些列的位模式。

触发器限制

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

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

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

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

INSTEAD OF DELETE / INSTEAD OF UPDATE 不能在具有外键且具有定义的作级联的表上 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 KEYUNIQUE约束。

注意

由于 SQL Server 不支持系统表上的用户定义的触发器，因此建议不要在系统表上创建用户定义的触发器。

优化 DML 触发器

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

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

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

IF (ROWCOUNT_BIG() = 0)
RETURN;

DDL 触发器的备注

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

重要

测试 DDL 触发器，以确定它们对执行系统存储过程的响应。 例如，CREATE TYPE语句和sp_addtypesp_rename存储过程触发在事件上CREATE_TYPE创建的 DDL 触发器。

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

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

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

注意

服务器范围的 DDL 触发器显示在 SQL Server Management Studio 对象资源管理器的“触发器”文件夹中。 此文件夹位于 “服务器对象” 文件夹下。 数据库范围的 DDL 触发器显示在 “数据库触发器” 文件夹中。 此文件夹位于相应数据库的 “可编程性” 文件夹下。

登录触发器

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

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

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

禁用登录触发器

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

常规触发器注意事项

返回结果

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

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

多个触发器

SQL Server 允许为每个 DML、DDL 或 LOGON 事件创建多个触发器。 例如，如果 CREATE TRIGGER FOR UPDATE 针对已具有 UPDATE 触发器的表运行，则会创建一个额外的更新触发器。 在早期版本的 SQL Server 中，每个表只允许一个触发器INSERTUPDATE或DELETE数据修改事件。

递归触发器

启用设置ALTER DATABASE时RECURSIVE_TRIGGERS，SQL Server 还支持对触发器进行递归调用。

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

• 间接递归：使用间接递归时，应用程序会更新表 T1。 这会触发触发器 TR1，更新表 T2。 然后触发 T2 并更新表 T1。

• 直接递归：在直接递归中，应用程序会更新表 T1。 这会触发触发器 TR1，更新表 T1。 由于表 T1 已更新，触发器 TR1 会再次触发，依此等。

以下示例同时使用间接和直接触发器递归假设两个更新触发器， TR1 并在 TR2表 T1上定义。 以递归方式触发 TR1 更新表 T1 。 语句 UPDATE 每次 TR1 运行 TR2 一次。 此外，启动 TR1 触发器 TR1 执行（递归）和 TR2。 特定触发器的插入和删除表包含仅对应于调用触发器的 UPDATE 语句的行。

注意

仅当使用ALTER DATABASE此设置启用时RECURSIVE_TRIGGERS，才会发生上述行为。 为特定事件定义的多个触发器没有指定的运行顺序。 每个触发器都应是自包含的。

禁用此设置 RECURSIVE_TRIGGERS 只会阻止直接递归。 若要同时禁用间接递归，请使用 sp_configure 将嵌套触发器服务器选项设置为 0。

如果任一触发器执行一个 ROLLBACK TRANSACTION，而不考虑嵌套级别，则不会再运行任何触发器。

嵌套触发器

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

若要禁用嵌套触发器，请将嵌套触发器选项 sp_configure 设置为 0（关闭）。 默认配置支持嵌套触发器。 如果嵌套触发器处于关闭状态，则递归触发器也会被禁用，尽管 RECURSIVE_TRIGGERS 使用 ALTER DATABASE此设置设置。

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

延迟名称解析

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

权限

若要创建 DML 触发器，它需要 ALTER 对要为其创建触发器的表或视图具有权限。

若要创建具有服务器范围 （ON ALL SERVER） 或登录触发器的 DDL 触发器，需要 CONTROL SERVER 对服务器具有权限。 若要创建具有数据库范围的 DDL 触发器（ON DATABASE），需要 ALTER ANY DATABASE DDL TRIGGER 当前数据库中的权限。

示例

答： 将 DML 触发器与提醒消息配合使用

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

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. 将 DML 触发器与提醒电子邮件配合使用

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

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
    EXECUTE msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2022 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C. 使用 DML AFTER 触发器在 PurchaseOrderHeader 和 Vendor 表之间强制实施业务规则

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

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

USE AdventureWorks2022;
GO

IF OBJECT_ID('Purchasing.LowCredit', 'TR') IS NOT NULL
    DROP TRIGGER Purchasing.LowCredit;
GO

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit
ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
    IF (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    RETURN;
END
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
    VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (2, 3, 261, 1652, 4, GETDATE(), GETDATE(), 44594.55, 3567.564, 1114.8638);
GO

D. 使用数据库范围的 DDL 触发器

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

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

E. 使用服务器范围的 DDL 触发器

以下示例使用 DDL 触发器在当前服务器实例上发生任何 CREATE DATABASE 事件时打印消息，并使用 EVENTDATA 函数检索相应 Transact-SQL 语句的文本。 有关在 DDL 触发器中使用的 EVENTDATA 更多示例，请参阅 “使用 EVENTDATA 函数”。

CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS PRINT 'Database Created.';
       SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
GO

DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
GO

F. 使用登录触发器

以下登录触发器示例拒绝尝试以登录名成员 login_test 身份登录到 SQL Server（如果该登录名下已运行三个用户会话）。 更改为 <password> 强密码。

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' MUST_CHANGE, CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
    ON ALL SERVER
    WITH EXECUTE AS 'login_test'
    FOR LOGON
    AS BEGIN
           IF ORIGINAL_LOGIN() = 'login_test'
              AND (SELECT COUNT(*)
                   FROM sys.dm_exec_sessions
                   WHERE is_user_process = 1
                         AND original_login_name = 'login_test') > 3
               ROLLBACK;
       END

G. 查看导致触发器触发的事件

以下示例将查询 sys.triggers 和 sys.trigger_events 目录视图，以确定是哪个 Transact-SQL 语言事件导致触发了 safety。 safety触发器在示例 D 中创建。使用数据库范围的 DDL 触发器。

SELECT TE.*
FROM sys.trigger_events AS TE
     INNER JOIN sys.triggers AS T
         ON T.object_id = TE.object_id
WHERE T.parent_class = 0
      AND T.name = 'safety';
GO

相关内容

• 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
• sys.dm_sql_referencing_entities
• sys.sql_expression_dependencies
• sp_help
• sp_helptrigger
• sp_helptext
• sp_rename
• sp_settriggerorder
• UPDATE - 触发器函数 （Transact-SQL）
• 获取有关 DML 触发器的信息
• 获取有关 DDL 触发器的信息
• sys.triggers
• sys.trigger_events
• sys.sql_modules
• sys.assembly_modules
• sys.server_triggers
• sys.server_trigger_events
• sys.server_sql_modules
• sys.server_assembly_modules