Dela via


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