DDL Triggers

DDL, or Data Definition Language triggers are executed when objects in your database are changed in some way. The main SQL keywords to which a DDL trigger can be attached are CREATE, ALTER and DROP.

When DDL Triggers Run

DDL triggers always run after the event to which they are attached.  There's no equivalent of the INSTEAD OF type of DML trigger.  There is a long list of DDL events at the Microsoft Developer Network site.

DDL Trigger Scope

DDL triggers can be scoped to either a single database, or to every database on a server.  You can find DDL triggers in the relevant folders in the Object Explorer window, as shown in the diagrams below:

Triggers scoped to a database will be found in the Database Triggers folder within the Programmability folder of the database.	Server-scoped triggers are stored in the Triggers folder within the Server Objects folder of the server.

Creating a DDL Trigger in the Object Explorer

You can create a database-scoped DDL trigger from the Object Explorer window.  To do this, right-click the Database Triggers folder within the Programmability folder of the database and choose New Database Trigger...

Right-click on the folder shown here and choose New Database Trigger...

Once you've chosen the option shown above you'll be presented with a page of system-generated code which you'll then have to adapt to create your trigger.

A sample of the system-generated code for creating a DDL trigger.

Creating a DDL Trigger in Code

Rather than adapting the system-generated code shown above, you could write the code from scratch.  The example shown below creates a simple DDL trigger, scoped to a single database:

Triggers that are scoped to the database appear in the Database Triggers folder within the database.

Expand the Programmability folder in the database to find the Database Triggers folder.

The trigger is designed to prevent the creation of new tables in the database.  If somebody attempts to execute code to create a new table they will see an error message, as shown in the diagram below:

This is the error message shown when a new table is created.

Creating a Server-Scoped Trigger

To create a trigger which affects all of the databases on a server you can exchange the word DATABASE with the words ALL SERVER in the code which creates your trigger:

You'll find this type of trigger in the Triggers folder of the server.

Expand the Server Objects folder within the server to find the Triggers folder.

Modifying a DDL Trigger

You can change the way a trigger works by writing an ALTER TRIGGER statement.  The example shown below extends our example trigger to respond to the ALTER_TABLE and DROP_TABLE events:

Removing a DDL Trigger

You can remove a DDL trigger using a couple of different methods. You can use the Object Explorer, as shown below:

Right-click on the trigger and choose to Delete it. This works for triggers scoped to the database and to the server.

You can also write code to delete a trigger; the example code below shows how to do this for both a database-scoped and server-scoped trigger:

Disabling and Enabling a DDL Trigger

You can disable and enable a DDL trigger using either the Object Explorer or by writing code.  To use the Object Explorer to disable and enable a DDL trigger:

Right-click the trigger and choose this option to disable it.	Right-click the trigger and choose this option to enable it.

You can also use code to enable and disable DDL triggers:

You can also choose to disable all of the triggers within a certain scope, as shown in the examples below:

Setting the Order of DDL Triggers

Just as with DML triggers you can set the order of DDL triggers using the sp_settriggerorder system stored procedure.  The example below shows how to do this:

The first three parameters are the same as for DML triggers. The fourth parameter, @namespace, refers to the scope of the trigger and can be either 'DATABASE' or 'SERVER'.

What's Next?

The next part of this series covers the final type of trigger in SQL Server; the Logon trigger.