DDL Triggers in SQL Server 2005

Database Triggers

 As specified before, DDL triggers are executed whenever you create, drop or alter an object at the database level. Users, tables, stored procedures,views, service broker objects like queues , functions and schemas are the objects which fall into the database objects.

In a DDL trigger you can specify the trigger options (ie the operations that need to be triggered). In the above example, it is specified to execute the triggers when a new table is created. However, rather than specify each operation, there are DDL event groups that you can specify. In that case the trigger will be executed for all the operations in that event group. For example, if you specified DDL_DATABASE_LEVEL_EVENTS instead of CREATE_TABLE all the events for CREATE_TABLE, ALTER_TALBE and DROP_TABLE that trigger will be executed hence all the events will be logged.

That trigger will look like below;

CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG] ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

/* Your code goes here */

At the end of the article, you will find the all the existing trigger events with it's highrachy. If you specificed an event, the trigger will be excuted for all the subsequent events.

EVENTDATA is an important function in DDL triggers. The EVENTDATA() function will be raised whenever a DDL trigger is fired. Output of the EVETNDATA() function is in XML format. The following is the XML format of the EVENTDATA() with example.

You can use above tags to suit your requirments.

Let us see what are the options that we can use with EVENTDATE() functions.

Apart from monitoring table creations. another requirment for DBAs is to prevent users creating tables or any other objects which does not conform to a standard. For example, if you want to stop users from creating tables which do not have prefix tbl, you can use following DDL trigger.

CREATE TRIGGER [ddltrg_CheckCreateTable] ON DATABASE

FOR CREATE_TABLE

AS

SET NOCOUNT ON

DECLARE @xmlEventData XML,

@tableName VARCHAR(50)

SET @xmlEventData = eventdata()

SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)'))

IF LEFT(@tableName, 3) <> 'tbl'

BEGIN

RAISERROR ( 'You cannot create table name without starting with tbl',

16,- 1 )

ROLLBACK

END

GO

After creating above DDL trigger, if you try create a table like the following,

CREATE TABLE Customer

(

ID INT,

Desccription VARCHAR(50)

)

You will get below error and table will not be created because of the ROLLBACK statement specified in the trigger.

Msg 50000, Level 16, State 1, Procedure ddltrg_, Line 17

You cannot create table name without starting with tbl

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

It is important to remember is that unlike DML triggers, in DDL triggers you won't find INSTEAD OF triggers. Instead of using INSTEAD OF triggers, you can write the trigger so that it triggers instead of the opreration. Because of this, in DML triggers you do not have to roll them back. As there is no such an option for DDL triggers, you have insert a ROLLBACK which might be a bit expensive.

You can extend the DDL trigger to include stored procedures , functions and for schemas.

 Also, if you want to stop users doing ALTER_TABLE during peak hours, you can do this by using the PostTime XML tag of EVENTDATA().

Server Triggers

Server DDL triggers fire when server operations are performed. For example, if you want to audit create database operations, the following trigger can be used.

CREATE TRIGGER [ddlsvrtrg_CREATE_DATABASE_LOG] ON ALL SERVER

FOR CREATE_DATABASE

AS

/* Your code goes here */

This trigger will also have the same EVENTDATA() function with same output XML format. Hence you will have all the options that database triggers have.