564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
This blog is part of our free SQL Server tutorial.
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.
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 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.|
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.
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.
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.
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'
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
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
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.
|Parts of this blog|
25 Aytoun Street