DDL Triggers in SQL SERVER 2008

SQL Server 2008 has a power full feature called DDL triggers which uses for Auditing changes in database and this is very useful too. You will find a blog entry about auditing using transaction log  here  and find a example of LOGON Trigger here. You can find several ways to do that. So if you want to who create or Delete a table from your database and when then here is a way to do so with the help of “EVENTDATA” function.
”EVENTDATA”  function returns a XML value.So here we go and see how it can be done…

First Create a Database to test …

USE MASTER

GO

-- First Create a DATABASE

CREATE DATABASE TestDDLTriggers ON PRIMARY

(NAME=N'TestDDLTriggers' ,FILENAME=N'D:\TestDDLTriggers.mdf')

LOG ON 

(NAME=N'TestDDLTriggers_LOG' ,FILENAME=N'D:\TestDDLTriggers.ldf')

You can change you path for .mdf & .ldf file

Now you create a table to hold result for you… and made a DDL Trigger..like this..

USE TestDDLTriggers

GO

-- Create a table to hold results

CREATE TABLE info_ddl (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100));

GO

-- Now create a Trigger

CREATE TRIGGER ddl_test 

ON DATABASE 

FOR DDL_DATABASE_LEVEL_EVENTS 

AS

INSERT info_ddl 

(PostTime, DB_User, Event) 

VALUES 

(GETDATE(), 

CONVERT(nvarchar(100), CURRENT_USER), 

EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')) ;

GO

So here we create a table to hold the results and a trigger on “DATABASE LEVEL EVENTS” you can see events using this

select * from sys.trigger_events

So after Creating a Trigger time to test the Trigger….

So we try first we try to create a table in the database and then after this we try to Drop that table ..so here it is…

-- Test Trigger

-- Create a table in Database

CREATE TABLE TestDDlTrigger (a int,b int,c int)

GO

-- Now Check our table which holds the data

select * from info_ddl

GO

-- Now we drop the table from Database

DROP TABLE TestDDlTrigger;

GO

-- Again check the data 

select * from info_ddl

GO

And here are the results

So in this you can track on the DDL operations like Create and Drop Table in your database.

Additionally you can store T-SQL fired at that time for this you can add a column in your table  using alter table …

-- Adding additional column to table to store T-SQL

ALTER TABLE info_ddl ADD TSQL VARCHAR(2000);

GO

then you have to Drop the existing trigger and create a new one here it is…

-- Drop Trigger

DROP TRIGGER ddl_test

ON DATABASE;

GO

-- Again Creating a Trigger

CREATE TRIGGER ddl_test 

ON DATABASE 

FOR DDL_DATABASE_LEVEL_EVENTS 

AS

INSERT info_ddl 

(PostTime, DB_User, Event, TSQL) 

VALUES 

(GETDATE(), 

CONVERT(nvarchar(100), CURRENT_USER), 

EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 

EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

GO

So in this you get T-SQL in your table..

You can also look at the Events for triggers in SQL Server 2008 by..

-- Exploring Trigger Events

select * from sys.trigger_events