DDL 触发器DDL Triggers

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

DDL 触发器将激发,以响应各种数据定义语言 (DDL) 事件。DDL triggers fire in response to a variety of Data Definition Language (DDL) events. 这些事件主要与以关键字 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 开头的 Transact-SQLTransact-SQL 语句对应。These events primarily correspond to Transact-SQLTransact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS. 执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.

如果要执行以下操作,请使用 DDL 触发器:Use DDL triggers when you want to do the following:

  • 防止对数据库架构进行某些更改。Prevent certain changes to your database schema.

  • 希望数据库中发生某种情况以响应数据库架构的更改。Have something occur in the database in response to a change in your database schema.

  • 记录数据库架构的更改或事件。Record changes or events in the database schema.

重要

测试您的 DDL 触发器以确定它们是否响应运行的系统存储过程。Test your DDL triggers to determine their responses to system stored procedures that are run. 例如,CREATE TYPE 语句和 sp_addtype 存储过程都将激发针对 CREATE_TYPE 事件创建的 DDL 触发器。For example, the CREATE TYPE statement and the sp_addtype stored procedure will both fire a DDL trigger that is created on a CREATE_TYPE event.

DDL 触发器的类型Types of DDL Triggers

Transact-SQL DDL 触发器Transact-SQL DDL Trigger

用于执行一个或多个 Transact-SQLTransact-SQL 语句以响应服务器范围或数据库范围事件的一种特殊类型的 Transact-SQLTransact-SQL 存储过程。A special type of Transact-SQLTransact-SQL stored procedure that executes one or more Transact-SQLTransact-SQL statements in response to a server-scoped or database-scoped event. 例如,如果执行某个语句(如 ALTER SERVER CONFIGURATION)或者使用 DROP TABLE 删除某个表,则激发 DDL 触发器。For example, a DDL Trigger may fire if a statement such as ALTER SERVER CONFIGURATION is executed or if a table is deleted by using DROP TABLE.

CLR DDL 触发器CLR DDL Trigger

CLR 触发器将执行在托管代码(在 .NET Framework 中创建并在 Transact-SQLTransact-SQL 中上载的程序集的成员)中编写的方法,而不用执行 SQL ServerSQL Server存储过程。Instead of executing a Transact-SQLTransact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL ServerSQL Server.

仅在运行触发 DDL 触发器的 DDL 语句后,DDL 触发器才会激发。DDL triggers fire only after the DDL statements that trigger them are run. DDL 触发器无法作为 INSTEAD OF 触发器使用。DDL triggers cannot be used as INSTEAD OF triggers. 对于影响局部或全局临时表和存储过程的事件,不会触发 DDL 触发器。DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

DDL 触发器不会创建特殊的 inserteddeleted 表。DDL triggers do not create the special inserted and deleted tables.

可以使用 EVENTDATA 函数捕获有关激发 DDL 触发器的事件以及触发器导致的后续更改的信息。The information about an event that fires a DDL trigger, and the subsequent changes caused by the trigger, is captured by using the EVENTDATA function.

为每个 DDL 事件创建多个触发器。Multiple triggers to be created for each DDL event.

与 DML 触发器不同,DDL 触发器的作用域不是架构。Unlike DML triggers, DDL triggers are not scoped to schemas. 因此,不能将 OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 之类的函数用于查询有关 DDL 触发器的元数据。Therefore, functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX cannot be used for querying metadata about DDL triggers. 请改用目录视图。Use the catalog views instead.

服务器范围的 DDL 触发器显示在 SQL Server Management Studio 对象资源管理器的“触发器”文件夹中。Server-scoped DDL triggers appear in the SQL 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.

重要

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

DDL 触发器作用域DDL Trigger Scope

在响应当前数据库或服务器上处理的 Transact-SQLTransact-SQL 事件时,可以触发 DDL 触发器。DDL triggers can fire in response to a Transact-SQLTransact-SQL event processed in the current database, or on the current server. 触发器的作用域取决于事件。The scope of the trigger depends on the event. 例如,每当数据库中或服务器实例上发生 CREATE_TABLE 事件时,都会激发为响应 CREATE_TABLE 事件创建的 DDL 触发器。For example, a DDL trigger created to fire in response to a CREATE_TABLE event can do so whenever a CREATE_TABLE event occurs in the database, or on the server instance. 仅当服务器实例上发生 CREATE_LOGIN 事件时,才能激发为响应 CREATE_LOGIN 事件创建的 DDL 触发器。A DDL trigger created to fire in response to a CREATE_LOGIN event can do so only when a CREATE_LOGIN event occurs in the server instance.

在下面的示例中,每当数据库中发生 safetyDROP_TABLE 事件时,都会激发 DDL 触发器 ALTER_TABLEIn the following example, DDL trigger safety will fire whenever a DROP_TABLE or ALTER_TABLE event occurs in the database.

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'   
   ROLLBACK;  

在下面的示例中,如果当前服务器实例上发生任何 CREATE_DATABASE 事件,DDL 触发器将输出消息。In the following example, a DDL trigger prints a message if any CREATE_DATABASE event occurs on the current server instance. 此示例使用 EVENTDATA 函数检索相应的 Transact-SQLTransact-SQL 语句的文本。The example uses the EVENTDATA function to retrieve the text of the corresponding Transact-SQLTransact-SQL statement. 有关如何将 EVENTDATA 与 DDL 触发器配合使用的详细信息,请参阅 使用 EVENTDATA 函数For more information about how to use EVENTDATA with DDL triggers, see Use the EVENTDATA Function.

IF EXISTS (SELECT * FROM sys.server_triggers  
    WHERE name = 'ddl_trig_database')  
DROP TRIGGER ddl_trig_database  
ON ALL SERVER;  
GO  
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  
  

本主题后面的“选择触发 DDL 触发器的特定 DDL 语句”一部分中提供了一些链接,通过这些链接可以找到将 Transact-SQLTransact-SQL 语句映射到为它们指定的作用域的列表。The lists that map the Transact-SQLTransact-SQL statements to the scopes that can be specified for them are available through the links provided in the section "Selecting a Particular DDL Statement to Fire a DDL Trigger," later in this topic.

数据库范围内的 DDL 触发器都作为对象存储在创建它们的数据库中。Database-scoped DDL triggers are stored as objects in the database in which they are created. 可以在 master 数据库中创建 DDL 触发器,这些触发器的行为与在用户设计的数据库中创建的 DDL 触发器的行为类似。DDL triggers can be created in the master database and behave just like those created in user-designed databases. 可以通过查询 sys.triggers 目录视图获取有关 DDL 触发器的信息。You can obtain information about DDL triggers by querying the sys.triggers catalog view. 可以在创建触发器的数据库上下文中或通过指定数据库名称作为标识符(例如 master.sys.triggers ),查询 sys.triggersYou can query sys.triggers within the database context in which the triggers are created or by specifying the database name as an identifier, such as master.sys.triggers.

服务器范围内的 DDL 触发器作为对象存储在 master 数据库中。Server-scoped DDL triggers are stored as objects in the master database. 然而,可以通过在任何数据库上下文中查询 sys.server_triggers 目录视图,获取有关服务器范围内的 DDL 触发器的信息。However, you can obtain information about server-scoped DDL triggers by querying the sys.server_triggers catalog view in any database context.

指定 Transact-SQL 语句或语句组Specifying a Transact-SQL Statement or Group of Statements

选择触发 DDL 触发器的特定 DDL 语句Selecting a Particular DDL Statement to Fire a DDL Trigger

可以安排在运行一个或多个特定 Transact-SQLTransact-SQL 语句后触发 DDL 触发器。DDL triggers can be designed to fire after one or more particular Transact-SQLTransact-SQL statements are run. 在前面的示例中,在发生任何 safetyDROP_TABLE 事件后触发 ALTER_TABLE 触发器。In the previous example, trigger safety fires after any DROP_TABLE or ALTER_TABLE event. 有关可指定激发 DDL 触发器的 Transact-SQLTransact-SQL 语句的列表,请参阅 DDL 事件For lists of the Transact-SQLTransact-SQL statements that can be specified to fire a DDL trigger, and the scope at which the trigger can fire, see DDL Events.

选择触发 DDL 触发器的一组预定义的 DDL 语句Selecting a Predefined Group of DDL Statements to Fire a DDL Trigger

可以在执行属于一组预定义的相似事件的任何 Transact-SQLTransact-SQL 事件后激发 DDL 触发器。A DDL trigger can fire after execution of any Transact-SQLTransact-SQL event that belongs to a predefined grouping of similar events. 例如,如果希望在运行 CREATE TABLE、ALTER TABLE 或 DROP TABLE 语句后触发 DDL 触发器,则可以在 CREATE TRIGGER 语句中指定 FOR DDL_TABLE_EVENTS。For example, if you want a DDL trigger to fire after any CREATE TABLE, ALTER TABLE, or DROP TABLE statement is run, you can specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER statement. 运行 CREATE TRIGGER 后,事件组涵盖的事件都添加到 sys.trigger_events 目录视图中。After CREATE TRIGGER is run, the events that are covered by an event group are added to the sys.trigger_events catalog view.

SQL Server 2005 (9.x)SQL Server 2005 (9.x)中,如果对事件组创建触发器,则 sys.trigger_events 不包括有关该事件组的信息,而 sys.trigger_events 仅包括有关该组涵盖的个别事件的信息。In SQL Server 2005 (9.x)SQL Server 2005 (9.x), if a trigger is created on an event group, sys.trigger_events does not include information about the event group, sys.trigger_events includes information only about the individual events covered by that group. SQL Server 2008SQL Server 2008 和更高版本中, sys.trigger_events 保留有关创建触发器的事件组的元数据,以及有关该事件组涵盖的个别事件的元数据。In SQL Server 2008SQL Server 2008 and higher, sys.trigger_events persists metadata about the event group on which the triggers is created, and also about the individual events that the event group covers. 因此,对 SQL Server 2008SQL Server 2008 和更高版本中事件组所涵盖的事件的更改并不适用于在 SQL Server 2005 (9.x)SQL Server 2005 (9.x)中对这些事件组创建的 DDL 触发器。Therefore, changes to the events that are covered by event groups in SQL Server 2008SQL Server 2008 and higher do not apply to DDL triggers that are created on those event groups in SQL Server 2005 (9.x)SQL Server 2005 (9.x).

有关可用于 DDL 触发器的预定义 DDL 语句组、事件组所涵盖的特定语句以及可以对这些事件组进行编程的作用域的列表,请参阅 DDL Event GroupsFor a list of the predefined groups of DDL statements that are available for DDL triggers, the particular statements the event groups cover, and the scopes at which these event groups can be programmed, see DDL Event Groups.

任务Task 主题Topic
说明如何创建、修改、删除或禁用 DDL 触发器。Describes how to create, modify, delete or disable DDL triggers. 实现 DDL 触发器Implement DDL Triggers
说明如何创建 CLR DDL 触发器。Describes how to create a CLR DDL trigger. 创建 CLR 触发器Create CLR Triggers
说明如何返回有关 DDL 触发器的信息。Describes how to return information about DDL triggers. 获取有关 DDL 触发器的信息Get Information About DDL Triggers
说明如何返回有关使用 EVENTDATA 函数激发 DDL 触发器的事件的信息。Describes how to return information about an event that fires a DDL trigger by using the EVENTDATA function. 使用 EVENTDATA 函数Use the EVENTDATA Function
说明如何管理触发器安全性。Describes how to manage trigger security. 管理触发器安全性Manage Trigger Security

另请参阅See Also

DML 触发器 DML Triggers
登录触发器 Logon Triggers
CREATE TRIGGER (Transact-SQL)CREATE TRIGGER (Transact-SQL)