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.


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 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.

 

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

2 Responses to “DDL Triggers in SQL Server 2005”

  1. There is a litle mistake on your code just a tiny thing when you do the “insert” instruction the name of the database you use it’s diferent than the one you created before

    DDL_TRIGGERS_DB-> DDL_Trigger_Log.dbo.tblDDLEventLog

    Thanks for the article it helped me a lot

  2. For user it’s better to use user_name .. because that is the logged in user iso the role, besides this it’s very very helpfull

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |