Microsoft.NET

……………………………………………….Expertise in .NET Technologies

DDL Triggers in SQL Server 2005

Posted by Ravi Varma Thumati on September 21, 2009

Introduction

Some times it is necessary for a company to have a solid chage manage procedures for IT systems. One of the requirement is to know who did what and when to the dataabse objects. There are several ways to achieve this is SQL Server 2000. However, SQL Server 2005 introduced a new feature called DDL triggers to satisfy the requirement 

Triggers are not new to SQL Server. But prior to SQL Server 2005 triggers were DML triggers, which were raised only when there is an INSERT, UPDATE or DELETE action. A new table, database or user being created raises a DDL event and to monitor those, DDL triggers were introduced in SQL Server 2005.

 Implementation

 Following is the syntax for DDL triggers.

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH <ddl_trigger_option> [ ,…n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,n ]

AS { sql_statement [ ; ] [ …n ] | EXTERNAL NAME < method specifier > [ ; ] }  

 DDL triggers can be created in either in the Database or the Server. If you want to monitor table creations and drops, you should create DDL trigger on the database, while to monitor operations like database creations you should create a DDL trigger on the Server.

Take a simple example of creating a database.

 CREATE DATABASE [DDL_TRIGGERS_DB]

 Let us assume that we want to log all the new table creations. We will log all the events in some other database called DDL_Trigger_Log in a table which has following schema.

 CREATE TABLE [dbo].[tblDDLEventLog](

[ID] [int] IDENTITY(1,1) NOT NULL,

[EventTime] [datetime] NULL,

[EventType] [varchar](15) NULL,

[ServerName] [varchar](25) NULL,

[DatabaseName] [varchar](25) NULL,

[ObjectType] [varchar](25) NULL,

[ObjectName] [varchar](25) NULL,

[UserName] [varchar](15) NULL,

[CommandText] [varchar](max) NULL,)

 Then we need to create a DDL trigger so that all the relevant event data is updated in the above table. Following will be the DDL trigger.

 CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG] ON DATABASE — Create Database DDL Trigger

FOR CREATE_TABLE — Trigger will raise when creating a Table

AS

SET NOCOUNT ON

DECLARE @xmlEventData XML

— Capture the event data that is created

SET @xmlEventData = eventdata()

— Insert information to a EventLog table

INSERT INTO DDL_Trigger_Log.dbo.tblDDLEventLog

(

EventTime,

EventType,

ServerName,

DatabaseName,

ObjectType,

ObjectName,

UserName,

CommandText

)

SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query(‘data(/EVENT_INSTANCE/PostTime)’)),

‘T’, ‘ ‘),

CONVERT(VARCHAR(15), @xmlEventData.query(‘data(/EVENT_INSTANCE/EventType)’)),

CONVERT(VARCHAR(25), @xmlEventData.query(‘data(/EVENT_INSTANCE/ServerName)’)),

CONVERT(VARCHAR(25), @xmlEventData.query(‘data(/EVENT_INSTANCE/DatabaseName)’)),

CONVERT(VARCHAR(25), @xmlEventData.query(‘data(/EVENT_INSTANCE/ObjectType)’)),

CONVERT(VARCHAR(25), @xmlEventData.query(‘data(/EVENT_INSTANCE/ObjectName)’)),

CONVERT(VARCHAR(15), @xmlEventData.query(‘data(/EVENT_INSTANCE/UserName)’)),

CONVERT(VARCHAR(MAX), @xmlEventData.query(‘data(/EVENT_INSTANCE/TSQLCommand/CommandText)’))

GO

 Then create a table and retrieve data in the tblDDLEvetnLog table:

triggers1

 You can see that all the necessary information, we will look more details about DDL triggers.

 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;

triggers2

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.

Enable or Disable Triggers

As in DML triggers, you have the option to Enable or Disable DDL triggers (for both server and database triggers)

 DISABLE TRIGGER ddltrg_CREATE_TABLE_LOG

ON ALL SERVER

GO

ENABLE TRIGGER ddltrg_CREATE_TABLE_LOG

ON ALL SERVER

GO

Trigger Execution Order

 When there are several triggers, you can define which trigger to execute first and last. There is a system stored procedure named sp_settriggerorder to set the priority. This is the same stored procedure which you can use to set priority for DML triggers as well.

 sp_settriggerorder [ @triggername = ] [ triggerschema. ] triggername

        , [ @order = ] value

        , [ @stmttype = ] statement_type

        [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]

From @order parameter you can set either first or last, which is the order of the trigger execution. The @namespace parameter can be set either DATABASE or SERVER depending on whether the DDL trigger is a database or server dependent trigger.

System Tables

 It is often necessary to know where the triggers are saved. In case of database DDL triggers, the information is stored in sys.triggers and sys.trigger_events. The sys.triggers view contains information like trigger name, create date etc and sys.trigger_events view contains the for which events those triggers are going to execute.

 SELECT * FROM sys.triggers

SELECT * FROM sys.trigger_events

 In case of Server DDL triggers, you have to use sys.server_triggers and sys.server_trigger_events.

SELECT * FROM sys.server_triggers

SELECT * FROM sys.server_trigger_events

Improvements

Eventhough there are 100+ events included for DDL triggers, there are few important events. Specifically events for database backup, database restore, and SQL Server Job related.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: