实现 DDL 触发器Implement 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 触发器的信息。This topic provides information to help you create DDL triggers, modify DDL triggers, and disable or drop DDL triggers.

创建 DDL 触发器Creating DDL Triggers

DDL 触发器是使用 DDL 触发器的 Transact-SQLTransact-SQL CREATE TRIGGER 语句创建的。DDL triggers are created by using the Transact-SQLTransact-SQL CREATE TRIGGER statement for DDL triggers.

创建 DDL 触发器To create a DDL trigger

重要

SQL ServerSQL Server的未来版本中将删除从触发器返回结果集的功能。The ability to return result sets from triggers will be removed in a future version of SQL ServerSQL Server. 返回结果集的触发器可能会导致应用程序出现意外的行为,而这些行为并不符合它们的设计意图。Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. 避免在新的开发工作中从触发器返回结果集,并计划修改当前执行此操作的应用程序。Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. 若要防止触发器在 SQL ServerSQL Server中返回结果集,请将 disallow results from triggers 选项 设置为 1。To prevent triggers from returning result sets in SQL ServerSQL Server, set the disallow results from triggers Option to 1. 在后续版本的 SQL ServerSQL Server中,此选项的默认设置为 1。The default setting of this option will be 1 in a future version of SQL ServerSQL Server.

修改 DDL 触发器Modifying DDL Triggers

如果必须修改 DDL 触发器的定义,只需一个操作即可删除并重新创建触发器,或重新定义现有触发器。If you have to modify the definition of a DDL trigger, you can either drop and re-create the trigger or redefine the existing trigger in a single step.

如果更改了由 DDL 触发器引用的对象的名称,则必须修改触发器,以使其文本反映新的名称。If you change the name of an object that is referenced by a DDL trigger, you must modify the trigger so that its text reflects the new name. 因此,在重命名对象之前,需要先显示该对象的依赖关系,以确定所建议的更改是否会影响任何触发器。Therefore, before renaming an object, display the dependencies of the object first to determine whether any triggers are affected by the proposed change.

也可将修改触发器,对其定义进行加密。A trigger can also be modified to encrypt its definition.

修改触发器To modify a trigger

查看触发器的依赖关系To view the dependencies of a trigger

禁用和删除 DDL 触发器Disabling and Dropping DDL Triggers

当不再需要某个 DDL 触发器时,可以禁用或删除该触发器。When a DDL trigger is no longer needed, you can disable it or delete it.

禁用 DDL 触发器不会将其删除。Disabling a DDL trigger does not drop it. 该触发器仍然作为对象存在于当前数据库中。The trigger still exists as an object in the current database. 但是,当运行编写触发器程序所用的任何 Transact-SQLTransact-SQL 语句时,不会激发触发器。However, the trigger will not fire when any Transact-SQLTransact-SQL statements on which it was programmed are run. 可以重新启用禁用的 DDL 触发器。DDL triggers that are disabled can be reenabled. 在启用 DDL 触发器后,该触发器的激发方式与其最初创建时的激发方式相同。Enabling a DDL trigger causes it to fire in the same way the trigger did when it was originally created. 创建的 DDL 触发器默认处于启用状态。When DDL triggers are created, they are enabled by default.

删除 DDL 触发器时,该触发器将从当前数据库中删除。When a DDL trigger is deleted, it is dropped from the current database. DDL 触发器范围内的任何对象或数据均不受影响。Any objects or data upon which the DDL trigger is scoped are not affected.

禁用 DDL 触发器To disable a DDL trigger

启用 DDL 触发器To enable a DDL trigger

删除 DDL 触发器To delete a DDL trigger