Implement 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

• CREATE TRIGGER (Transact-SQL)

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, 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.

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

• ALTER TRIGGER (Transact-SQL)

To view the dependencies of a trigger

• sys.sql_expression_dependencies (Transact-SQL)

• sys.dm_sql_referenced_entities (Transact-SQL)

• sys.dm_sql_referencing_entities (Transact-SQL)

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

• DISABLE TRIGGER (Transact-SQL)

• ALTER TABLE (Transact-SQL)

To enable a DDL trigger

• ENABLE TRIGGER (Transact-SQL)

• ALTER TABLE (Transact-SQL)

To delete a DDL trigger

• DROP TRIGGER (Transact-SQL)