BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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, 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:
CREATE TRIGGER trgNoNewTables
PRINT 'No more tables, please'
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
PRINT 'No more tables, please'
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
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
PRINT 'You cannot create, alter or drop tables'
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 a database-scoped trigger
DROP TRIGGER trgNoNewTables ON DATABASE
--Drop a server-scoped trigger
DROP TRIGGER trgNoNewTables ON ALL SERVER
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 a database-scoped trigger
DISABLE TRIGGER trgNoNewTables ON DATABASE
--Enable a database-scoped trigger
ENABLE TRIGGER trgNoNewTables ON DATABASE
--Disable a server-scoped trigger
DISABLE TRIGGER trgNoNewTables ON ALL SERVER
--Enable a server-scoped trigger
ENABLE TRIGGER trgNoNewTables ON ALL SERVER
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
--Enable all database-scoped triggers
ENABLE TRIGGER ALL ON DATABASE
--Disable all server-scoped triggers
DISABLE TRIGGER ALL ON ALL SERVER
--Enable all server-scoped triggers
ENABLE TRIGGER ALL ON ALL SERVER
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
@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.
The next part of this series covers the final type of trigger in SQL Server; the Logon trigger.