Triggers in SQL Server
Part three of a four-part series of blogs

Triggers are special stored procedures that execute automatically in response to certain events in a database. This blog explains how to create three types of trigger in SQL Server: DML, DDL and Logon triggers.

  1. Triggers in SQL Server
  2. DML Triggers in SQL Server
  3. DDL Triggers (this blog)
  4. Logon Triggers

This blog is part of our free SQL Server tutorial.

Posted by Andrew Gould on 27 November 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Database scoped triggers Server-scoped triggers
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...

Adding trigger in Object Explorer

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.

System-generated code

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.

Created database trigger

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:

Error when creating table

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.

Server trigger

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:

Deleting database trigger

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 a database-scoped trigger

DROP TRIGGER trgNoNewTables ON DATABASE

GO

--Drop a server-scoped trigger

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:

Disabling a trigger Right menu to enable 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 a database-scoped trigger

DISABLE TRIGGER trgNoNewTables ON DATABASE

GO

--Enable a database-scoped trigger

ENABLE TRIGGER trgNoNewTables ON DATABASE

GO

--Disable a server-scoped trigger

DISABLE TRIGGER trgNoNewTables ON ALL SERVER

GO

--Enable a server-scoped trigger

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 all database-scoped triggers

DISABLE TRIGGER ALL ON DATABASE

GO

--Enable all database-scoped triggers

ENABLE TRIGGER ALL ON DATABASE

GO

--Disable all server-scoped triggers

DISABLE TRIGGER ALL ON ALL SERVER

GO

--Enable all server-scoped triggers

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:

--Set this trigger to be the first one executed

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.

This blog has 0 threads Add post