DDL Triggers in SQL Server 2005

Introduction

In 2002 the US Congress enacted the Sarbanes-Oxley Act which required a company to have solid change manage procedures for IT systems. One of the requirement is to know who did what and when to the database objects. There are several ways to achieve this in SQL Server 2000 - C2 level auditing or implementing third party tools. However, SQL Server 2005 introduced a new feature called DDL triggers to satisfy the Sarnes-Oxley.

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.

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 relevent event data is updated in the above table. Follwing 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:

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