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.
This article is moved to following link:
Ritesh's Blog for SQL-SERVER & .NET 
September 2, 2009 at 5:06 am
Dear Ritesh,
A Very Good Morning to you Sir !!
Until now, I was just wondering or rather taxing my memory with loads and loads of queries as to how to maintain a log when a user alters the database in anyway.
After quite sometime, I got the answer through your website. I wouldn’t know how to THANK YOU and let you know how relieved I am feeling now.
God Bless you in all your endeveavour.
Kind Regards,
Sylvester Marshall
September 2, 2009 at 5:25 am
Hi Sylvester,
It is really nice to get encouraging comment from you. Actually this blog is dead now as I am not keeping it much updated. You are welcome to my live blog at http://www.SQLHub.com which is really very active.
Thanks,
Ritesh Shah
September 5, 2009 at 9:54 am
Thank you Ritesh for your selfless service to folks like us !!
I’ve bookmarked your new blog and look forward to your valuable assistance in the future as well.
Warm Regards,
Sylvester Marshall
February 10, 2010 at 7:14 am
How can we use this for TRUNCATE event and DELETE event?
Is there any way of this?
February 12, 2010 at 10:51 am
Thanks, this has helped a lot.:-)
March 24, 2010 at 10:30 am
This post is very good to understand about DDL triggers.
But i want to get the list of all DDL triggers which are created in database.
How can i get this.
Please help
March 24, 2010 at 10:49 am
Hello Ramveersingh,
You can follow below steps:
1.) Open SSMS studio
2.) expand your SQL Server instance from tree view at left hand side
3.) expand “Database” node
4.) expand Your Database
5.) expand “Programmability”
6.) expand “Database Trigger”
you can see list of all database trigger here. BTW, I am not much active here in this blog, you can keep visit my current blog at http://www.SQLHub.com
November 23, 2010 at 2:33 pm
please tel me There are How many the types of DDL Trigger in SQL server
2005 ?
February 11, 2011 at 9:16 am
there are basically two types of DDL triggers alter and drop
December 22, 2010 at 9:38 pm
thanks this educating for me
October 24, 2011 at 4:35 pm
how can i drop ddl triger?
ie drop alter not work knw
how it is possible?
October 25, 2011 at 3:33 am
you should use DROP TRIGGER command, not alter. look at the below link for more information
http://technet.microsoft.com/en-us/library/ms173497.aspx
April 11, 2012 at 7:26 pm
fyi – if you keep your dbLog table in a separate database, for this trigger to work, the sql login performing the alter/drop/create must have insert access to the dbLog table in the other database! the trigger runs under the security context of the login attempting the DDL statement.
January 16, 2013 at 6:58 am
Nice Post its very use full.
QU.Tell me about Truncate ?.How to protect our database by truncate command because if i write truncate_table then they showing error.?
January 16, 2013 at 8:57 pm
Auto Default extended properties
CREATE TRIGGER [DB_Add_extended_propeties]
ON DATABASE
FOR CREATE_TABLE,CREATE_VIEW, CREATE_PROCEDURE
AS
SET NOCOUNT ON
DECLARE @eventInfo XML
SET @eventInfo = EVENTDATA()
DECLARE @timestamp DATETIME, @user VARCHAR(100), @ObjectType VARCHAR(50), @ObjectName VARCHAR(50)
SELECT @timestamp=GETDATE()
SELECT @user=SYSTEM_USER
SET @ObjectType=CONVERT(VARCHAR(50),@eventInfo.query(‘data(/EVENT_INSTANCE/ObjectType)’))
SET @ObjectName=CONVERT(VARCHAR(50),@eventInfo.query(‘data(/EVENT_INSTANCE/ObjectName)’))
EXEC sys.sp_addextendedproperty @name=N’Created by’, @value=@user , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=@ObjectType ,@level1name=@ObjectName
EXEC sys.sp_addextendedproperty @name=N’Purpose’, @value=N” , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=@ObjectType ,@level1name=@ObjectName
EXEC sys.sp_addextendedproperty @name=N’Created on’, @value=@timestamp, @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=@ObjectType ,@level1name=@ObjectName
GO