The new DDL trigger in SQL Server 2005

Posted at : Mar/29/2007

4214 Views

Triggers is a procedure which executed when the insert,update,delete action occured on the table(s).
That is the description of triggers in former SQL Server. But now in SQL Server 2005 we do not only use
the triggers for DML. The new DDL triggers has been added to the SQL Server 2005 as an enchancements to the prior
triggers. By now we can use the triggers not only when insert,update,delete occured in the table(s) but more than
that we can use for Create,Drop,Alter on Databases. It is very useful for auditing and loging for some SQL Server
objects. But it cannot use the Insted Of trigger. Firstable i'll create some tables to store the data, logging the action occured on the table which caused by DDL.

--table to store the data

Create Table Books
(ISBN varchar(20) Not Null Primary Key, 
Title varchar(50) Null)

--tabel for logging the action 
--occured on the Books table

Create Table LogTable
(LogNum int Primary Key Identity, LogDesc xml Null)

--create the DDL triggers for Drop action

Create Trigger OnDropBooksTable
On Database
After DROP_TABLE
As
Insert Into LogTable (LogDesc) 
Values ('The Books table has been dropped')

--action the trigger to fire

Drop Table Books

--retrieve the data from the LogTable table

Select * From LogTable

--the result is
LogNum LogDesc
----------- --------------------------------
1 The Books table has been dropped

When the DML drop occured on the Books table the DDL trigger fire, then it log the information to LogTable table as we have create before. If we want to get more information details which events are fired by the DDL, Sql Server has the EventData system function. It returns the information about what events are fired and the return value is xml datatype, that's why i use the xml datatype for the LogDesc column in the LogTable table. We can get some information from the EventData system function about : Database, schema, object, objecttype, TSQL command.
So to consume the function, we have to alter the Trigger :

Alter Trigger OnDropBooksTable
On Database
After DDL_Database_Level_Events
As
Insert Into LogTable (LogDesc) Values (EVENTDATA())

--delete the existing record from the LogTable table

Delete From LogTable

--create the Books table again

Create Table Books (
ISBN varchar(20) Not Null Primary Key, 
Title varchar(50) Null
)

--retrieve the information event log from the LogTable table with the xml

Select LogDesc From LogTable

--the result is

<EVENT_INSTANCE>
    <EventType>CREATE_TABLE</EventType>
    <PostTime>2007-03-29T14:24:49.690</PostTime>
    <SPID>52</SPID>
    <ServerName>WEE\SQLDEV2K5</ServerName>
    <LoginName>WEE\UWee</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>AdventureWorks</DatabaseName>
    <SchemaName>dbo</SchemaName>
    <ObjectName>Books</ObjectName>
    <ObjectType>TABLE</ObjectType>
    <TSQLCommand>
        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" 
            ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" 
            ENCRYPTED="FALSE" />
        <CommandText>Create Table Books
            (ISBN varchar(20) Not Null Primary Key, 
            Title varchar(50) Null)    
        </CommandText>
    </TSQLCommand>
</EVENT_INSTANCE>