Implementing DDL Triggers
This topic provides information to help you create DDL triggers, modify DDL triggers, and disable or drop DDL triggers.
Creating DDL Triggers
DDL triggers are created by using the Transact-SQL CREATE TRIGGER statement for DDL triggers.
To create a DDL trigger
To drop a DDL trigger
Important
The ability to return result sets from triggers will be removed in a future version of SQL 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. To prevent triggers from returning result sets in SQL Server 2008, set the disallow results from triggers Option to 1. The default setting of this option will be 1 in a future version of SQL Server.
Note
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. Database-scoped DDL triggers appear in the Database Triggers folder. This folder is located under the Programmability folder of the corresponding database.
Modifying DDL Triggers
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.
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
Disabling and Dropping DDL Triggers
When a DDL trigger is no longer needed, you can disable it or delete it.
Disabling a DDL trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger will not fire when any Transact-SQL statements on which it was programmed are run. DDL triggers that are disabled can be reenabled. Enabling a DDL trigger causes it to fire in the same way the trigger did when it was originally created. When DDL triggers are created, they are enabled by default.
When a DDL trigger is deleted, it is dropped from the current database. Any objects or data upon which the DDL trigger is scoped are not affected.
To disable a DDL trigger
To enable a DDL trigger
To delete a DDL trigger