CREATE TRIGGER (Transact-SQL)CREATE TRIGGER (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

创建 DML、DDL 或登录触发器。Creates a DML, DDL, or logon trigger. 触发器是一种特殊类型的存储过程,在数据库服务器中发生事件时自动运行。A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. 如果用户尝试通过数据操作语言 (DML) 事件修改数据,DML 触发器运行。DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML 事件是针对表或视图的 INSERT、UPDATE 或 DELETE 语句。DML events are INSERT, UPDATE, or DELETE statements on a table or view. 此类触发器在任何有效事件触发时触发,无论表行是否受影响。These triggers fire when any valid event fires, whether table rows are affected or not. 有关详细信息,请参阅 DML TriggersFor more information, see DML Triggers.

DDL 触发器是为了响应各种数据定义语言 (DDL) 事件而运行。DDL triggers run in response to a variety of data definition language (DDL) events. 这些事件主要对应于 Transact-SQLTransact-SQL CREATE、ALTER 和 DROP 语句,以及执行类似 DDL 操作的某些系统存储过程。These events primarily correspond to Transact-SQLTransact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

登录触发器是为了响应在建立用户会话时触发的 LOGON 事件而触发。Logon triggers fire in response to the LOGON event that's raised when a user's session is being established. 可以直接使用 Transact-SQLTransact-SQL 语句创建触发器,也可以使用程序集方法,它们是在 MicrosoftMicrosoft .NET Framework.NET Framework 公共语言运行时 (CLR) 中创建,并上传到 SQL ServerSQL Server 实例中。You can create triggers directly from Transact-SQLTransact-SQL statements or from methods of assemblies that are created in the MicrosoftMicrosoft .NET Framework.NET Framework common language runtime (CLR) and uploaded to an instance of SQL ServerSQL Server. 使用 SQL ServerSQL Server,可以为任何特定语句创建多个触发器。SQL ServerSQL Server lets you create multiple triggers for any specific statement.

重要

触发器内部的恶意代码可以在升级后的权限下运行。Malicious code inside triggers can run under escalated privileges. 有关如何缓解此威胁的详细信息,请参阅管理触发器安全For more information on how to mitigate this threat, see Manage Trigger Security.

备注

本文介绍了将 .NET Framework CLR 集成到 SQL Server。The integration of .NET Framework CLR into SQL Server is discussed in this article. CLR 集成不适用于 Azure SQL 数据库。CLR integration does not apply to Azure SQL Database.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
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  
  
-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
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 ]  
  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE or UPDATE statement (DDL Trigger)  
  
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 ]  
  
-- Trigger on a LOGON event (Logon Trigger)  
  
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 ]  
  

语法Syntax

-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
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 ]  
  
-- Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   
  
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 ]  

参数Arguments

OR ALTEROR ALTER
适用对象:Azure SQL 数据库SQL DatabaseSQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 开始)。Applies to: Azure SQL 数据库SQL Database, SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

只有在触发器已存在时才对其进行有条件地更改。Conditionally alters the trigger only if it already exists.

schema_nameschema_name
DML 触发器所属架构的名称。The name of the schema to which a DML trigger belongs. DML 触发器的范围限定为,对其创建此类触发器的表或视图的架构。DML triggers are scoped to the schema of the table or view on which they're created. 不能为 DDL 或登录触发器指定 schema_name 。schema_name can't be specified for DDL or logon triggers.

trigger_name trigger_name
触发器的名称。The name of the trigger. trigger_name 必须遵循标识符规则,但 trigger_name 不得以 # 或 ## 开头。A trigger_name must follow the rules for identifiers, except that trigger_name can't start with # or ##.

table | view table | view
对其运行 DML 触发器的表或视图。The table or view on which the DML trigger runs. 此表或视图有时称为“触发器表”或“触发器视图”。This table or view is sometimes referred to as the trigger table or trigger view. 可以根据需要指定表或视图的完全限定名称。Specifying the fully qualified name of the table or view is optional. 只有 INSTEAD OF 触发器才能引用视图。You can only reference a view by an INSTEAD OF trigger. 无法对本地或全局临时表定义 DML 触发器。You can't define DML triggers on local or global temporary tables.

DATABASEDATABASE
将 DDL 触发器的作用域应用于当前数据库。Applies the scope of a DDL trigger to the current database. 如果指定了此参数,则只要当前数据库中出现 event_type 或 event_group,就会激发该触发器 。If specified, the trigger fires whenever event_type or event_group occurs in the current database.

ALL SERVERALL SERVER
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

将 DDL 或登录触发器的作用域应用于当前服务器。Applies the scope of a DDL or logon trigger to the current server. 如果指定了此参数,则只要当前服务器中的任何位置出现 event_type 或 event_group,就会激发该触发器 。If specified, the trigger fires whenever event_type or event_group occurs anywhere in the current server.

WITH ENCRYPTIONWITH ENCRYPTION
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

让 CREATE TRIGGER 语句的文本复杂难懂。Obscures the text of the CREATE TRIGGER statement. 使用 WITH ENCRYPTION 可以防止将触发器作为 SQL ServerSQL Server 复制的一部分进行发布。Using WITH ENCRYPTION prevents the trigger from being published as part of SQL ServerSQL Server replication. 无法为 CLR 触发器指定 WITH ENCRYPTION。WITH ENCRYPTION can't be specified for CLR triggers.

EXECUTE ASEXECUTE AS
指定用于执行该触发器的安全上下文。Specifies the security context under which the trigger is executed. 允许您控制 SQL ServerSQL Server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。Enables you to control which user account the instance of SQL ServerSQL Server uses to validate permissions on any database objects that are referenced by the trigger.

内存优化表上的触发器需要使用此选项。This option is required for triggers on memory-optimized tables.

有关详细信息,请参阅 EXECUTE AS 子句 (Transact-SQL)For more information, seeEXECUTE AS Clause (Transact-SQL).

NATIVE_COMPILATIONNATIVE_COMPILATION
指示触发器已本机编译。Indicates that the trigger is natively compiled.

内存优化表上的触发器需要使用此选项。This option is required for triggers on memory-optimized tables.

SCHEMABINDINGSCHEMABINDING
确保无法删除或更改触发器引用的表。Ensures that tables referenced by a trigger can't be dropped or altered.

对于内存优化表上的触发器,此为必需选项,但传统表上的触发器不支持此选项。This option is required for triggers on memory-optimized tables and isn't supported for triggers on traditional tables.

FOR | AFTERFOR | AFTER
AFTER 指定仅当触发 SQL 语句中指定的所有操作都已成功启动时,DML 触发器才触发。AFTER specifies that the DML trigger fires only when all operations specified in the triggering SQL statement have launched successfully. 所有引用级联操作和约束检查也必须在此触发器触发前成功启动。All referential cascade actions and constraint checks must also succeed before this trigger fires.

如果仅指定 FOR 关键字,则 AFTER 为默认值。AFTER is the default when FOR is the only keyword specified.

无法对视图定义 AFTER 触发器。You can't define AFTER triggers on views.

INSTEAD OFINSTEAD OF
指定 DML 触发器(而不是 触发 SQL 语句)启动,因此替代触发语句的操作。Specifies that the DML trigger launches instead of the triggering SQL statement, thus, overriding the actions of the triggering statements. 无法为 DDL 触发器或登录触发器指定 INSTEAD OF。You can't specify INSTEAD OF for DDL or logon triggers.

最多可以对表或视图定义,每 INSERT、UPDATE 或 DELETE 语句一个 INSTEAD OF 触发器。At most, you can define one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement on a table or view. 还可以对每个都有自己的 INSTEAD OF 触发器的视图定义视图。You can also define views on views where each view has its own INSTEAD OF trigger.

无法对使用 WITH CHECK OPTION 的可更新视图定义 INSTEAD OF 触发器。You can't define INSTEAD OF triggers on updatable views that use WITH CHECK OPTION. 如果 SQL ServerSQL Server 这样做,便会在 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中时生成错误。SQL ServerSQL Server Doing so results in an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. 先使用 ALTER VIEW 删除此选项,再定义 INSTEAD OF 触发器。You remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

{ [DELETE] [,] [INSERT] [,] [UPDATE] }{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
指定数据修改语句,用于在 DML 触发器尝试对此表或视图触发时激活触发器。Specifies the data modification statements that activate the DML trigger when it's tried against this table or view. 至少指定一个选项。Specify at least one option. 在触发器定义中使用这些选项的任意顺序组合。Use any combination of these options in any order in the trigger definition.

对于 INSTEAD OF 触发器,无法对有引用关系(指定级联操作 ON DELETE)的表使用 DELETE 选项。For INSTEAD OF triggers, you can't use the DELETE option on tables that have a referential relationship, specifying a cascade action ON DELETE. 同样,也不得对有引用关系(指定级联操作 ON UPDATE)的表使用 UPDATE 选项。Similarly, the UPDATE option isn't allowed on tables that have a referential relationship, specifying a cascade action ON UPDATE.

WITH APPENDWITH APPEND
适用范围SQL Server 2008SQL Server 2008SQL Server 2008 R2SQL Server 2008 R2Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2008 R2SQL Server 2008 R2.

指定应该再添加一个现有类型的触发器。Specifies that an additional trigger of an existing type should be added. WITH APPEND 无法与 INSTEAD OF 触发器一起使用,或在显式声明 AFTER 触发器后也无法使用。WITH APPEND can't be used with INSTEAD OF triggers or if an AFTER trigger is explicitly stated. 为了实现后向兼容性,仅在指定了 FOR(但没有指定 INSTEAD OF 或 AFTER)时,才使用 WITH APPEND。For backward compatibility, only use WITH APPEND when FOR is specified, without INSTEAD OF or AFTER. 如果使用的是 EXTERNAL NAME(即触发器是 CLR 触发器),无法指定 WITH APPEND。You can't specify WITH APPEND if using EXTERNAL NAME (that is, if the trigger is a CLR trigger).

event_type event_type
启动后触发 DDL 触发器的 Transact-SQLTransact-SQL 语言事件的名称。The name of a Transact-SQLTransact-SQL language event that, after launch, causes a DDL trigger to fire. DDL 事件中列出了 DDL 触发器的有效事件。Valid events for DDL triggers are listed in DDL Events.

event_group event_group
预定义的 Transact-SQLTransact-SQL 语言事件分组的名称。The name of a predefined grouping of Transact-SQLTransact-SQL language events. DDL 触发器在任何属于 event_group 的 Transact-SQLTransact-SQL 语言事件启动后触发。The DDL trigger fires after launch of any Transact-SQLTransact-SQL language event that belongs to event_group. DDL 事件组中列出了 DDL 触发器的有效事件组。Valid event groups for DDL triggers are listed in DDL Event Groups.

CREATE TRIGGER 运行完成后,event_group 还将充当宏,将它涉及的事件类型添加到 sys.trigger_events 目录视图中。After the CREATE TRIGGER has finished running, event_group also acts as a macro by adding the event types it covers to the sys.trigger_events catalog view.

NOT FOR REPLICATIONNOT FOR REPLICATION
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

指明触发器不得在复制代理修改触发器涉及的表时运行。Indicates that the trigger shouldn't be run when a replication agent modifies the table that's involved in the trigger.

sql_statement sql_statement
触发条件和操作。The trigger conditions and actions. 触发器条件指定其他用于确定尝试的 DML、DDL 或 LOGON 事件是否导致触发器操作运行的条件。Trigger conditions specify additional criteria that determine whether the tried DML, DDL, or logon events cause the trigger actions to be run.

尝试上述操作时,将执行 Transact-SQLTransact-SQL 语句中指定的触发器操作。The trigger actions specified in the Transact-SQLTransact-SQL statements go into effect when the operation is tried.

触发器可以包含任意数量和类型的 Transact-SQLTransact-SQL 语句,但也有例外。Triggers can include any number and type of Transact-SQLTransact-SQL statements, with exceptions. 有关详细信息,请参阅“备注”。For more information, see Remarks. 触发器旨在根据数据修改或定义语句来检查或更改数据;不得向用户返回数据。A trigger is designed to check or change data based on a data modification or definition statement; it should't return data to the user. 触发器中的 Transact-SQLTransact-SQL 语句常常包含控制流语言The Transact-SQLTransact-SQL statements in a trigger frequently include control-of-flow language.

DML 触发器使用 deleted 和 inserted 逻辑(概念)表。DML triggers use the deleted and inserted logical (conceptual) tables. 它们在结构上类似于定义了触发器的表,即尝试对其执行用户操作的表。They're structurally similar to the table on which the trigger is defined, that is, the table on which the user action is tried. deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action. 例如,若要检索 deleted 表中的所有值,则使用:For example, to retrieve all values in the deleted table, use:

SELECT * FROM deleted;  

有关详细信息,请参阅使用插入的和删除的表For more information, see Use the inserted and deleted Tables.

DDL 和登录触发器使用 EVENTDATA (Transact-SQL) 函数来捕获有关触发事件的信息。DDL and logon triggers capture information about the triggering event by using the EVENTDATA (Transact-SQL) function. 有关详细信息,请参阅使用 EVENTDATA 函数For more information, see Use the EVENTDATA Function.

使用 SQL ServerSQL Server,可以通过表或视图上的 INSTEAD OF 触发器来更新 text 、ntext 或 image 列。SQL ServerSQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

重要

MicrosoftMicrosoftSQL ServerSQL Server 的未来版本中将删除 ntexttextimage 数据类型。ntext, text, and image data types will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新开发工作中使用这些数据类型,并考虑修改当前使用这些数据类型的应用程序。Avoid using these data types in new development work, and plan to modify applications that currently use them. 请改用 nvarchar(max)varchar(max)varbinary(max)Use nvarchar(max), varchar(max), and varbinary(max) instead. AFTER 和 INSTEAD OF 触发器均支持插入和删除的表中的 varchar(MAX)nvarchar(MAX)varbinary(MAX) 数据。Both AFTER and INSTEAD OF triggers support varchar(MAX), nvarchar(MAX), and varbinary(MAX) data in the inserted and deleted tables.

对于内存优化表中的触发器,顶层允许的唯一 sql_statement 是 ATOMIC 块 。For triggers on memory-optimized tables, the only sql_statement allowed at the top level is an ATOMIC block. ATOMIC 块内允许的 T-SQL 由本地进程内允许的 T-SQL 决定。The T-SQL allowed inside the ATOMIC block is limited by the T-SQL allowed inside native procs.

< method_specifier > 适用于SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017< method_specifier > Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

对于 CLR 触发器,指定程序集与触发器绑定的方法。For a CLR trigger, specifies the method of an assembly to bind with the trigger. 该方法不能带有任何参数,并且必须返回空值。The method must take no arguments and return void. class_name 必须是有效的 SQL ServerSQL Server 标识符,并且它必须作为类存在于可见程序集中 。class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly with assembly visibility. 如果该类有一个使用“.”来分隔命名空间部分的命名空间限定名称,则类名必须用 [] 或“ ”分隔符分隔。If the class has a namespace-qualified name that uses '.' to separate namespace parts, the class name must be delimited by using [ ] or " " delimiters. 此类不得为嵌套类。The class can't be a nested class.

备注

默认情况下,SQL ServerSQL Server 无法运行 CLR 代码。By default, the ability of SQL ServerSQL Server to run CLR code is off. 可以创建、修改和删除引用托管代码模块的数据库对象,但除非使用 sp_configure 启用了 clr enabled 选项,否则这些引用不会在 SQL ServerSQL Server 实例中运行。You can create, modify, and drop database objects that reference managed code modules, but these references don't run in an instance of SQL ServerSQL Server unless the clr enabled Option is enabled by using sp_configure.

DML 触发器的注释Remarks for DML Triggers

DML 触发器经常用于强制执行业务规则和数据完整性。DML triggers are frequently used for enforcing business rules and data integrity. SQL ServerSQL Server 通过 ALTER TABLE 和 CREATE TABLE 语句来提供声明性引用完整性 (DRI)。provides declarative referential integrity (DRI) through the ALTER TABLE and CREATE TABLE statements. 不过,DRI 不提供跨数据库引用完整性。However, DRI doesn't provide cross-database referential integrity. 引用完整性是指有关表的主键和外键之间的关系的规则。Referential integrity refers to the rules about the relationships between the primary and foreign keys of tables. 若要强制实现引用完整性,请在 ALTER TABLE 和 CREATE TABLE 中使用 PRIMARY KEY 和 FOREIGN KEY 约束。To enforce referential integrity, use the PRIMARY KEY and FOREIGN KEY constraints in ALTER TABLE and CREATE TABLE. 如果触发器表存在约束,便在 INSTEAD OF 触发器运行后且 AFTER 触发器运行前检查这些约束。If constraints exist on the trigger table, they're checked after the INSTEAD OF trigger runs and before the AFTER trigger runs. 如果违反了约束,INSTEAD OF 触发器操作回滚,且 AFTER 触发器不触发。If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger isn't fired.

可使用 sp_settriggerorder 指定,要对表运行的第一个和最后一个 AFTER 触发器。You can specify the first and last AFTER triggers to be run on a table by using sp_settriggerorder. 只能为表中的每个 INSERT、UPDATE 和 DELETE 操作指定一个第一个和最后一个 AFTER 触发器。You can specify only one first and one last AFTER trigger for each INSERT, UPDATE, and DELETE operation on a table. 如果同一个表上还有其他 AFTER 触发器,这些触发器随机运行。If there are other AFTER triggers on the same table, they're randomly run.

如果 ALTER TRIGGER 语句更改了第一个或最后一个触发器,那么已修改触发器上设置的第一个或最后一个属性遭删除,必须使用 sp_settriggerorder 重置顺序值。If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and you must reset the order value by using sp_settriggerorder.

只有在触发 SQL 语句成功运行后,AFTER 触发器才运行。An AFTER trigger is run only after the triggering SQL statement has run successfully. 判断执行成功的标准是:执行了所有与已更新对象或已删除对象相关联的引用级联操作和约束检查。This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted. AFTER 不会以递归方式触发同一个表上的 INSTEAD OF 触发器。An AFTER does not recursively fire an INSTEAD OF trigger on the same table.

如果对表定义的 INSTEAD OF 触发器对表运行通常会再次触发 INSTEAD OF 触发器的语句,不会以递归方式调用触发器。If an INSTEAD OF trigger defined on a table runs a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger isn't called recursively. 而是处理此语句,就像表中没有 INSTEAD OF 触发器一样,并启动一系列约束操作和 AFTER 触发器执行。Instead, the statement processes as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions. 例如,如果将触发器定义为表的 INSTEAD OF INSERT 触发器,For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table. 且触发器对同一个表运行 INSERT 语句,那么 INSTEAD OF 触发器启动的 INSERT 语句不会再次调用触发器。And, the trigger runs an INSERT statement on the same table, the INSERT statement launched by the INSTEAD OF trigger doesn't call the trigger again. 触发器启动的 INSERT 启动以下过程:运行约束操作,并触发为表定义的任何 AFTER INSERT 触发器。The INSERT launched by the trigger starts the process of running constraint actions and firing any AFTER INSERT triggers defined for the table.

如果对视图定义的 INSTEAD OF 触发器对视图运行通常会再次触发 INSTEAD OF 触发器的语句,不会以递归方式调用触发器。When an INSTEAD OF trigger defined on a view runs a statement against the view that would ordinarily fire the INSTEAD OF trigger again, it's not called recursively. 而是将该语句解析为对视图所依存的基本表进行的修改。Instead, the statement is resolved as modifications against the base tables underlying the view. 在这种情况下,视图定义必须满足可更新视图的所有约束。In this case, the view definition must meet all the restrictions for an updatable view. 有关可更新视图的定义,请参阅通过视图修改数据For a definition of updatable views, see Modify Data Through a View.

例如,如果将触发器定义为视图的 INSTEAD OF UPDATE 触发器,For example, if a trigger is defined as an INSTEAD OF UPDATE trigger for a view. 且触发器运行引用同一个视图的 UPDATE 语句,那么 INSTEAD OF 触发器启动的 UPDATE 语句不会再次调用触发器。And, the trigger runs an UPDATE statement referencing the same view, the UPDATE statement launched by the INSTEAD OF trigger doesn't call the trigger again. 而是对视图处理触发器启动的 UPDATE 语句,就像视图中没有 INSTEAD OF 触发器一样。The UPDATE launched by the trigger is processed against the view as if the view didn't have an INSTEAD OF trigger. 由 UPDATE 更改的列必须解析到一个基表。The columns changed by the UPDATE must be resolved to a single base table. 对基表的每次修改都将应用约束并触发为该表定义的 AFTER 触发器。Each modification to an underlying base table starts the chain of applying constraints and firing AFTER triggers defined for the table.

测试对指定列的 UPDATE 或 INSERT 操作Testing for UPDATE or INSERT Actions to Specific Columns

可以将 Transact-SQLTransact-SQL 触发器设计为,根据对具体列的 UPDATE 或 INSERT 修改来执行特定操作。You can design a Transact-SQLTransact-SQL trigger to do certain actions based on UPDATE or INSERT modifications to specific columns. 可在触发器的主体中使用 UPDATE()COLUMNS_UPDATED 来达到此目的。Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() 可以测试对某个列的 UPDATE 或 INSERT 尝试。UPDATE() tests for UPDATE or INSERT attempts on one column. COLUMNS_UPDATED 测试是否有对多列运行的 UPDATE 或 INSERT 操作。COLUMNS_UPDATED tests for UPDATE or INSERT actions that run on multiple columns. 此函数返回指明已插入或已更新哪些列的位模式。This function returns a bit pattern that indicates which columns were inserted or updated.

触发器限制Trigger Limitations

CREATE TRIGGER 必须是批处理中的第一条语句,并且只能应用于一个表。CREATE TRIGGER must be the first statement in the batch and can apply to only one table.

触发器只能在当前的数据库中创建,但是可以引用当前数据库的外部对象。A trigger is created only in the current database; however, a trigger can reference objects outside the current database.

如果指定了触发器架构名称来限定触发器,则将以相同的方式限定表名称。If the trigger schema name is specified to qualify the trigger, qualify the table name in the same way.

在同一条 CREATE TRIGGER 语句中,可以为多种用户操作(如 INSERT 和 UPDATE)定义相同的触发器操作。The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.

如果表有外键定义了级联 DELETE/UPDATE 操作,便无法对此表定义 INSTEAD OF DELETE/UPDATE 触发器。INSTEAD OF DELETE/UPDATE triggers can't be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.

在触发器内可以指定任意的 SET 语句。Any SET statement can be specified inside a trigger. 选择的 SET 选项在触发器执行期间保持有效,然后恢复为原来的设置。The SET option selected remains in effect during the execution of the trigger and then reverts to its former setting.

如果触发了一个触发器,结果将返回给执行调用的应用程序,就像使用存储过程一样。When a trigger fires, results are returned to the calling application, just like with stored procedures. 为了避免由于触发器触发而向应用程序返回结果,请不要添加返回结果的 SELECT 语句,也不要添加在触发器中执行变量赋值的语句。To prevent results being returned to an application because of a trigger firing, don't include either SELECT statements that return results or statements that carry out variable assignment in a trigger. 如果触发器包含将结果返回给用户的 SELECT 语句或执行变量赋值的语句,需要特殊处理触发器。A trigger that includes either SELECT statements that return results to the user or statements that do variable assignment, requires special handling. 必须将返回的结果写入所有允许修改触发器表的应用程序中。You'd have to write the returned results into every application in which modifications to the trigger table are allowed. 如果必须在触发器中进行变量赋值,则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the start of the trigger to prevent the return of any result sets.

虽然 TRUNCATE TABLE 语句实际上就是 DELETE 语句,但它不会激活触发器,因为操作不记录各个行删除。Although a TRUNCATE TABLE statement is in effect a DELETE statement, it doesn't activate a trigger because the operation doesn't log individual row deletions. 不过,只有有权运行 TRUNCATE TABLE 语句的用户,才需要担心是否会无意中这样规避 DELETE 触发器。However, only those users with permissions to run a TRUNCATE TABLE statement need be concerned about inadvertently circumventing a DELETE trigger this way.

无论是否记录,WRITETEXT 语句都不激活触发器。The WRITETEXT statement, whether logged or unlogged, doesn't activate a trigger.

不得在 DML 触发器中使用下列 Transact-SQLTransact-SQL 语句:The following Transact-SQLTransact-SQL statements aren't allowed in a DML trigger:

ALTER DATABASEALTER DATABASE CREATE DATABASECREATE DATABASE DROP DATABASEDROP DATABASE
RESTORE DATABASERESTORE DATABASE RESTORE LOGRESTORE LOG RECONFIGURERECONFIGURE

另外,如果对作为触发操作目标的表或视图使用 DML 触发器,也不得在 DML 触发器的主体中使用下列 Transact-SQLTransact-SQL 语句。Additionally, the following Transact-SQLTransact-SQL statements aren't allowed inside the body of a DML trigger when it's used against the table or view that's the target of the triggering action.

CREATE INDEX(包括 CREATE SPATIAL INDEX 和 CREATE XML INDEX)CREATE INDEX (including CREATE SPATIAL INDEX and CREATE XML INDEX) ALTER INDEXALTER INDEX DROP INDEXDROP INDEX
DBCC DBREINDEXDBCC DBREINDEX ALTER PARTITION FUNCTIONALTER PARTITION FUNCTION DROP TABLEDROP TABLE
用于执行以下操作的 ALTER TABLE:ALTER TABLE when used to do the following:

添加、修改或删除列。Add, modify, or drop columns.

切换分区。Switch partitions.

添加或删除 PRIMARY KEY 或 UNIQUE 约束。Add or drop PRIMARY KEY or UNIQUE constraints.

备注

因为 SQL ServerSQL Server 不支持针对系统表的用户定义的触发器,因此我们建议不要为系统表创建用户定义触发器。Because SQL ServerSQL Server does not support user-defined triggers on system tables, we recommend that you do not create user-defined triggers on system tables.

优化 DML 触发器Optimizing DML Triggers

触发器在事务(隐式或非隐式)中运行,当事务待处理时,它们会锁定资源。Triggers work in transactions (implied or otherwise) and while they're open, they lock resources. 除非事务被确认(使用 COMMIT)或拒绝(使用 ROLLBACK),否则锁定一直存在。The lock remains in place until the transaction is confirmed (with COMMIT) or rejected (with a ROLLBACK). 触发器运行时间越长,另一进程被锁定的可能性就越大。The longer a trigger runs, the higher the probability that another process is then blocked. 因此,尽可能通过写入触发器来减少持续时间。So, write triggers to lessen their duration whenever possible. 缩短持续时间的一种方法是,在 DML 语句更改 0 行时释放触发器。One way to achieve shorter duration is to release a trigger when a DML statement changes zero rows.

若要为不更改任何行的命令释放触发器,请使用系统变量 ROWCOUNT_BIGTo release the trigger for a command that doesn't change any rows, employ the system variable ROWCOUNT_BIG.

下面的 T-SQL 代码片段展示了如何为不更改任何行的命令释放触发器。The following T-SQL code snippet shows how to release the trigger for a command that doesn't change any rows. 此代码应位于每个 DML 触发器的开头:This code should be present at the beginning of each DML trigger:

IF (ROWCOUNT_BIG() = 0)
RETURN;

DDL 触发器的注释Remarks for DDL Triggers

DDL 触发器启动存储过程来响应事件,就像标准触发器一样。DDL triggers, like standard triggers, launch stored procedures in response to an event. 但与标准触发器不同的是,DDL 触发器并不会为了响应表或视图中的 UPDATE、INSERT 或 DELETE 语句而运行。But, unlike standard triggers, they don't run in response to UPDATE, INSERT, or DELETE statements on a table or view. 相反,它们主要是为了响应数据定义语言 (DDL) 语句而运行。Instead, they primarily run in response to data definition language (DDL) statements. 这些语句类型包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS。The statement types include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS. 执行类似 DDL 操作的特定系统存储过程也可以触发 DDL 触发器。Certain system stored procedures that carry out DDL-like operations can also fire DDL triggers.

重要

测试 DDL 触发器,以确定它们对执行系统存储过程的响应。Test your DDL triggers to determine their responses to system stored procedure execution. 例如,CREATE TYPE 语句以及 sp_addtype 和 sp_rename 存储过程都会触发针对 CREATE_TYPE 事件创建的 DDL 触发器。For example, the CREATE TYPE statement and the sp_addtype and sp_rename stored procedures fire a DDL trigger that's created on a CREATE_TYPE event.

有关 DDL 触发器的详细信息,请参阅 DDL 触发器For more information about DDL triggers, see DDL Triggers.

DDL 触发器不会为了响应影响本地或全局临时表和存储过程的事件而触发。DDL triggers don't fire in response to events that affect local or global temporary tables and stored procedures.

与 DML 触发器不同,DDL 触发器的范围不限定为架构。Unlike DML triggers, DDL triggers aren't scoped to schemas. 因此,无法使用 OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 等函数来查询 DDL 触发器的元数据。So, you can't use functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX for querying metadata about DDL triggers. 请改用目录视图。Use the catalog views instead. 有关详细信息,请参阅获取有关 DDL 触发器的信息For more information, see Get Information About DDL Triggers.

备注

服务器范围内的 DDL 触发器显示在 SQL Server Management StudioSQL Server Management Studio 对象资源管理器的“触发器” 文件夹中。Server-scoped DDL triggers appear in the SQL Server Management StudioSQL Server Management Studio Object Explorer in the Triggers folder. 此文件夹位于 “服务器对象” 文件夹下。This folder is located under the Server Objects folder. 数据库范围内的 DDL 触发器显示在“数据库触发器” 文件夹中。Database-scoped DDL Triggers appear in the Database Triggers folder. 此文件夹位于相应数据库的 “可编程性” 文件夹下。This folder is located under the Programmability folder of the corresponding database.

登录触发器Logon Triggers

登录触发器是为了响应 LOGON 事件而执行存储过程。Logon triggers carry out stored procedures in response to a LOGON event. 此事件在用户会话通过 SQL ServerSQL Server 实例建立时发生。This event happens when a user session is established with an instance of SQL ServerSQL Server. 登录触发器在登录的身份验证阶段完成后且用户会话建立前触发。Logon triggers fire after the authentication phase of logging in finishes, but before the user session is established. 因此,所有源自触发器内部且通常会传递给用户的消息(如错误消息和来自 PRINT 语句的消息)会转移到 SQL ServerSQL Server 错误日志。So, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL ServerSQL Server error log. 有关详细信息,请参阅登录触发器For more information, see Logon Triggers.

如果身份验证失败,登录触发器不会触发。Logon triggers don't fire if authentication fails.

登录触发器不支持分布式事务。Distributed transactions aren't supported in a logon trigger. 当包含分布式事务的登录触发器触发时,3969 错误返回。Error 3969 returns when a logon trigger that contains a distributed transaction fire.

禁用登录触发器Disabling a Logon Trigger

登录触发器可以有效地阻止所有用户(包括 数据库引擎Database Engine sysadmin 固定服务器角色的成员)与 的成功连接。A logon trigger can effectively prevent successful connections to the 数据库引擎Database Engine for all users, including members of the sysadmin fixed server role. 在登录触发器正在阻止连接时, sysadmin 固定服务器角色的成员可通过使用专用管理员连接,或者通过以最小配置模式 (-f) 启动 数据库引擎Database Engine ,来进行连接。When a logon trigger is preventing connections, members of the sysadmin fixed server role can connect by using the dedicated administrator connection, or by starting the 数据库引擎Database Engine in minimal configuration mode (-f). 有关详细信息,请参阅 Database Engine Service Startup OptionsFor more information, see Database Engine Service Startup Options.

常规触发器注意事项General Trigger Considerations

返回结果Returning Results

SQL Server 的未来版本中将删除从触发器返回结果的功能。The ability to return results from triggers will be removed in a future version of SQL Server. 返回结果集的触发器可能会导致无法处理结果集的应用程序出现意外行为。Triggers that return result sets may cause unexpected behavior in applications that aren't designed to work with them. 避免在新的开发工作中从触发器返回结果集,并计划修改当前这样做的应用程序。Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do. 若要防止触发器返回结果集,请将 disallow results from triggers 选项设置为 1。To prevent triggers from returning result sets, set the disallow results from triggers option to 1.

登录触发器始终禁止返回结果集,这种行为不可配置。Logon triggers always disallow the return of results sets and this behavior isn't configurable. 如果登录触发器生成了结果集,此触发器会无法启动,且触发了此触发器的登录尝试会遭拒。If a logon trigger generates a result set, the trigger fails to launch and the login attempt that fired the trigger is denied.

多个触发器Multiple Triggers

使用 SQL ServerSQL Server,可以为每个 DML、DDL 或 LOGON 事件创建多个触发器。SQL ServerSQL Server lets you create multiple triggers for each DML, DDL, or LOGON event. 例如,如果 CREATE TRIGGER FOR UPDATE 对已有 UPDATE 触发器的表运行,则会再创建一个 UPDATE 触发器。For example, if CREATE TRIGGER FOR UPDATE is run for a table that already has an UPDATE trigger, an additional update trigger is created. SQL ServerSQL Server 早期版本中,对于每个表,每个 INSERT、UPDATE 或 DELETE 数据修改事件只允许有一个触发器。In earlier versions of SQL ServerSQL Server, only one trigger for each INSERT, UPDATE, or DELETE data modification event is allowed for each table.

递归触发器Recursive Triggers

如果使用 ALTER DATABASE 启用了 RECURSIVE_TRIGGERS 设置,SQL ServerSQL Server 还支持以递归方式调用触发器。SQL ServerSQL Server also supports recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.

递归触发器可以采用下列递归类型:Recursive triggers enable the following types of recursion to occur:

  • 间接递归Indirect recursion

    在间接递归中,一个应用程序更新了表 T1。With indirect recursion, an application updates table T1. 这触发了触发器 TR1,从而更新了表 T2。This fires trigger TR1, updating table T2. 然后,触发器 T2 触发,并更新表 T1。Trigger T2 then fires and updates table T1.

  • 直接递归Direct recursion

    在直接递归中,应用程序更新表 T1。In direct recursion, the application updates table T1. 这触发了触发器 TR1,从而更新了表 T1。This fires trigger TR1, updating table T1. 由于表 T1 被更新,将再次触发触发器 TR1,依此类推。Because table T1 was updated, trigger TR1 fires again, and so on.

以下示例同时使用了间接和直接触发器递归。假设对表 T1 定义了两个更新触发器 TR1 和 TR2。The following example uses both indirect and direct trigger recursion Assume that two update triggers, TR1 and TR2, are defined on table T1. 触发器 TR1 以递归方式更新表 T1。Trigger TR1 updates table T1 recursively. UPDATE 语句运行 TR1 和 TR2 各一次。An UPDATE statement runs each TR1 and TR2 one time. 另外,启动 TR1 也会触发执行 TR1(以递归方式)和 TR2。Additionally, the launch of TR1 triggers the execution of TR1 (recursively) and TR2. 指定触发器的 inserted 和 deleted 表包含仅与调用触发器的 UPDATE 语句对应的行。The inserted and deleted tables for a specific trigger contain rows that correspond only to the UPDATE statement that invoked the trigger.

备注

仅当使用 ALTER DATABASE 启用了 RECURSIVE_TRIGGERS 设置时,才能发生前述行为。The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is enabled by using ALTER DATABASE. 为特定事件定义的多个触发器没有指定的运行顺序。There's no defined order in which multiple triggers defined for a specific event are run. 每个触发器都应是自包含的。Each trigger should be self-contained.

禁用 RECURSIVE_TRIGGERS 的设置只能阻止直接递归。Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. 若要同时禁用间接递归,请使用 sp_configure 将 nested triggers 服务器选项设置为 0。To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.

如果任一触发器执行了 ROLLBACK TRANSACTION,无论嵌套级别如何,都不会再运行其他任何触发器。If any one of the triggers carries out a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are run.

嵌套触发器Nested Triggers

最多可以将触发器嵌套到 32 个级别。You can nest triggers to a maximum of 32 levels. 如果一个触发器更改了包含另一个触发器的表,那么第二个触发器激活,然后又可以调用第三个触发器,依此类推。If a trigger changes a table on which there's another trigger, the second trigger activates and can then call a third trigger, and so on. 如果链中任意一个触发器引发了无限循环,则会超出嵌套级限制,从而导致取消触发器。If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. 如果 Transact-SQLTransact-SQL 触发器通过引用 CLR 例程、类型或聚合来启动托管代码,此引用作为一级计入 32 级嵌套限制。When a Transact-SQLTransact-SQL trigger launches managed code by referencing a CLR routine, type, or aggregate, this reference counts as one level against the 32-level nesting limit. 从托管代码内部调用的方法不计入此限制。Methods invoked from within managed code don't count against this limit.

若要禁用嵌套触发器,请用 sp_configure 将 nested triggers 选项设置为 0(关闭)。To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). 默认配置支持嵌套触发器。The default configuration supports nested triggers. 如果禁用嵌套触发器,递归触发器也遭禁用,不管使用 ALTER DATABASE 设置的 RECURSIVE_TRIGGERS 设置如何。If nested triggers are off, recursive triggers are also disabled, despite the RECURSIVE_TRIGGERS setting that's set by using ALTER DATABASE.

即使“嵌套触发器” 服务器配置选项为 0,嵌套在 INSTEAD OF 触发器内的第一个 AFTER 触发器也会触发。The first AFTER trigger nested inside an INSTEAD OF trigger fires even if the nested triggers server configuration option is 0. 不过,在此设置下,后面的 AFTER 触发器不会触发。But, under this setting, the later AFTER triggers don't fire. 当“嵌套触发器” 服务器配置选项设置为 0 时,检查应用程序中是否有嵌套触发器,以确定应用程序是否遵循业务规则。Review your applications for nested triggers to determine if the applications follow your business rules when the nested triggers server configuration option is set to 0. 如果不遵循,请进行适当修改。If not, make the appropriate modifications.

延迟名称解析Deferred Name Resolution

使用 SQL ServerSQL ServerTransact-SQLTransact-SQL 存储过程、触发器和批处理可以引用在编译时不存在的表。SQL ServerSQL Server allows for Transact-SQLTransact-SQL stored procedures, triggers, and batches to refer to tables that don't exist at compile time. 这种功能称为延迟名称解析。This ability is called deferred name resolution.

权限Permissions

必须对要为其创建触发器的表或视图拥有 ALTER 权限,才能创建 DML 触发器。To create a DML trigger, it requires ALTER permission on the table or view on which the trigger is being created.

必须对服务器拥有 CONTROL SERVER 权限,才能创建具有服务器范围 (ON ALL SERVER) 的 DDL 触发器或登录触发器。To create a DDL trigger with server scope (ON ALL SERVER) or a logon trigger, requires CONTROL SERVER permission on the server. 必须在当前数据库中拥有 ALTER ANY DATABASE DDL TRIGGER 权限,才能创建具有数据库范围 (ON DATABASE) 的 DDL 触发器。To create a DDL trigger with database scope (ON DATABASE), requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

示例Examples

A.A. 使用包含提醒消息的 DML 触发器Using a DML trigger with a reminder message

如果有人试图在 AdventureWorks2012AdventureWorks2012 数据库的 Customer 表中添加或更改数据,下列 DML 触发器将向客户端显示一条消息。The following DML trigger prints a message to the client when anyone tries to add or change data in the Customer table in the AdventureWorks2012AdventureWorks2012 database.

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

B.B. 使用包含提醒电子邮件的 DML 触发器Using a DML trigger with a reminder e-mail message

如果 MaryM 表发生更改,以下示例将向指定人员 (Customer) 发送电子邮件。The following example sends an e-mail message to a specified person (MaryM) when the Customer table changes.

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

C.C. 使用 DML AFTER 触发器在 PurchaseOrderHeader 和 Vendor 表之间强制实现业务规则Using a DML AFTER trigger to enforce a business rule between the PurchaseOrderHeader and Vendor tables

由于 CHECK 约束只引用定义了列级别或表级别约束的列,因此必须将任何跨表约束(在本例中是业务规则)定义为触发器。Because CHECK constraints reference only the columns on which the column-level or table-level constraint is defined, you must define any cross-table constraints (in this case, business rules) as triggers.

以下示例在 AdventureWorks2012 数据库中创建 DML 触发器。The following example creates a DML trigger in the AdventureWorks2012 database. 此触发器会进行检查,以确保在有人试图将新采购订单插入 PurchaseOrderHeader 表时,供应商的信用分级良好(不为 5)。This trigger checks to make sure the credit rating for the vendor is good (not 5) when there's an attempt to insert a new purchase order into the PurchaseOrderHeader table. 必须引用 Vendor 表,才能获取供应商的信用分级。To get the credit rating of the vendor, the Vendor table must be referenced. 如果信用分级太低,便会显示消息,且不执行插入操作。If the credit rating is too low, a message appears and the insertion doesn't happen.

-- 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 *  
           FROM Purchasing.PurchaseOrderHeader AS p   
           JOIN inserted AS i   
           ON p.PurchaseOrderID = i.PurchaseOrderID   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = p.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
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.D. 使用具有数据库范围的 DDL 触发器Using a database-scoped DDL trigger

下面的示例使用 DDL 触发器来防止从数据库中删除任何同义词。The following example uses a DDL trigger to prevent any synonym in a database from being dropped.

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.E. 使用具有服务器范围的 DDL 触发器Using a server-scoped DDL trigger

在以下示例中,如果当前服务器实例上出现任何 CREATE DATABASE 事件,则使用 DDL 触发器输出一条消息,并使用 EVENTDATA 函数检索对应 Transact-SQLTransact-SQL 语句的文本。The following example uses a DDL trigger to print a message if any CREATE DATABASE event occurs on the current server instance, and uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQLTransact-SQL statement. 有关在 DDL 触发器中使用 EVENTDATA 的更多示例,请参阅使用 EVENTDATA 函数For more examples that use EVENTDATA in DDL triggers, see Use the EVENTDATA Function.

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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.F. 使用登录触发器Using a logon trigger

下面的登录触发器示例拒绝了作为 login_test 登录名的成员登录 SQL ServerSQL Server 的尝试(如果在此登录名下已运行三个用户会话)。The following logon trigger example denies an attempt to log in to SQL ServerSQL Server as a member of the login_test login if there are already three user sessions running under that login.

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

USE master;  
GO  
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' 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.G. 查看导致触发器触发的事件Viewing the events that cause a trigger to fire

以下示例将查询 sys.triggerssys.trigger_events 目录视图,以确定是哪个 Transact-SQLTransact-SQL 语言事件导致触发了 safetyThe following example queries the sys.triggers and sys.trigger_events catalog views to determine which Transact-SQLTransact-SQL language events cause trigger safety to fire. 在示例“D”中创建了触发器 safety(可在上面找到)。The trigger, safety, is created in example 'D', found above.

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

另请参阅See Also

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