DDL Trigger in SQL-Server 2005 for Create table, alter table, drop table, create procedure, alter procedure, drop procedure etc.

DDL Trigger in SQL-Server 2005 for Create table, alter table, drop table, create procedure, alter procedure, drop procedure etc.

You might be aware with the term of “TRIGGER” which is not at all new concept but yes, it has new feature in SQL-Server 2005 which you have not experienced in any of prior edition of SQL-Server before 2005.

Microsoft has introduced two type of DDL trigger. Database level DDL trigger and Server level DDL trigger. Now, you will think that where can you use it???? Answer is very broad, you can use it to protect your database or your server from being create, drop or alter table or procedure. You may also wish to have track about who is doing what???? You can track any create, alter or drop regarding your tables or your procedures.

I am going to show you one short example, further you can develop your own TRIGGER for your customize need. I assume that all of you are having AdventureWorks database installed on your computer, ships with SQL-Server 2005.

Let us create one small table for tracking database activity.

--this table will store some information about user activity

CREATE TABLE dbLOG

(

e_time VARCHAR(50),

e_servername VARCHAR(25),

e_username VARCHAR(15),

e_commandtext VARCHAR(max)

)

Now, we will create one database level trigger which will track any user if s/he tries to create, alter or drop table in AdventureWorks database.

CREATE TRIGGER [db_LOG]

ON DATABASE --this statement will create DDL trigger on database level, you can use

-- ON ALL SERVER for making trigger for server (for all database in your server)

FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE --will track only create, drop or alter table

AS

SET NOCOUNT ON

DECLARE @xEvent XML

SET @xEvent = eventdata() --capture eventdata regarding SQL statement user have fired

INSERT INTO dbLOG VALUES(

REPLACE(CONVERT(VARCHAR(50), @xEvent.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),

CONVERT(VARCHAR(25), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),

CONVERT(VARCHAR(15), @xEvent.query('data(/EVENT_INSTANCE/UserName)')),

CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))

)

GO

Now, you can try creating one table in database and you will get log of the same from our “dbLOG” table.

create table test1(id int)

Now check your dbLOG table. You will have information about user activity which we have captured from EVENTDATA() in our trigger. We have just captured very little information from EVENTDATA(), but you can have more than that. Following XML script shows you full data of EVENTDATA() contain.

<EVENT_INSTANCE>

<EventType>CREATE_TABLE</EventType>

<PostTime>2009-03-05T17:48:43.063</PostTime>

<SPID>56</SPID>

<ServerName>ECHEM-SRV</ServerName>

<LoginName>ECHEM-SRV\Administrator</LoginName>

<UserName>dbo</UserName>

<DatabaseName>AdventureWorks</DatabaseName>

<SchemaName>dbo</SchemaName>

<ObjectName>Test1</ObjectName>

<ObjectType>TABLE</ObjectType>

<TSQLCommand>

<SetOptions ANSI_NULLS="ON"

ANSI_NULL_DEFAULT="ON"

ANSI_PADDING="ON"

QUOTED_IDENTIFIER="ON"

ENCRYPTED="FALSE" />

<CommandText>CREATE TABLE [dbo].[Test1](

[id] [int]

) ON [PRIMARY];</CommandText>

</TSQLCommand>

</EVENT_INSTANCE>

You can use any of the information contained in above XML script.

You can disable, enable and drop DDL trigger with following commands.

DISABLE TRIGGER [db_LOG]

ON DATABASE

GO

ENABLE TRIGGER [db_LOG]

ON DATABASE

GO

DROP TRIGGER [db_LOG]

ON DATABASE

GO

Reference: Ritesh Shah