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.
There are several other keywords that can fire a DDL trigger, including
GRANT, DENY, REVOKE and UPDATE STATISTICS. It's also worth noting that some
system stored procedures can cause a DDL trigger to fire.
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.
If you want to find out which events have caused your DDL triggers to fire
you can use the
EVENTDATA function.
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:
USE Movies
GO
CREATE TRIGGER trgNoNewTables
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
PRINT 'No more tables, please'
ROLLBACK
END
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:
CREATE TRIGGER trgNoNewTables
ON ALL SERVER
FOR CREATE_TABLE
AS
BEGIN
PRINT 'No more tables, please'
ROLLBACK
END
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:
ALTER TRIGGER trgNoNewTables
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'You cannot create, alter or drop tables'
ROLLBACK
END
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:
DROP TRIGGER trgNoNewTables ON DATABASE
GO
DROP TRIGGER trgNoNewTables ON ALL SERVER
GO
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:
DISABLE TRIGGER trgNoNewTables ON DATABASE
GO
ENABLE TRIGGER trgNoNewTables ON DATABASE
GO
DISABLE TRIGGER trgNoNewTables ON ALL SERVER
GO
ENABLE TRIGGER trgNoNewTables ON ALL SERVER
GO
You can also choose to disable all of the triggers within a certain scope, as
shown in the examples below:
DISABLE TRIGGER ALL ON DATABASE
GO
ENABLE TRIGGER ALL ON DATABASE
GO
DISABLE TRIGGER ALL ON ALL SERVER
GO
ENABLE TRIGGER ALL ON ALL SERVER
GO
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:
EXEC sp_settriggerorder
@triggername = 'trgNoNewTables'
,@order = 'first'
,@stmttype = 'CREATE_TABLE'
,@namespace = 'DATABASE'
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'.
You can have both a server-scoped and database-scoped trigger handling the
same DDL event. Server-scoped triggers will always fire before any of the
database-scoped ones.
What's Next?
The next part of this series covers the final type of trigger in SQL Server;
the Logon trigger.
