Implement DDL Triggers
Applies To: SQL Server 2016
This topic provides information to help you create DDL triggers, modify DDL triggers, and disable or drop DDL triggers.
DDL triggers are created by using the Transact-SQL CREATE TRIGGER statement for DDL triggers.
To create a DDL trigger
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
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