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.

This article is moved to following link:

http://blog.extreme-advice.com/2009/03/05/ddl-trigger-in-sql-server-2005-for-create-table-alter-table-drop-table-create-procedure-alter-procedure-drop-procedure-etc/

Advertisements

15 Responses to “DDL Trigger in SQL-Server 2005 for Create table, alter table, drop table, create procedure, alter procedure, drop procedure etc.”

  1. Sylvester Marshall Says:

    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

    • riteshshah Says:

      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

      • Sylvester Marshall Says:

        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

  2. Monika Says:

    How can we use this for TRUNCATE event and DELETE event?
    Is there any way of this?

  3. John Says:

    Thanks, this has helped a lot.:-)

  4. ramveersngh Says:

    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

    • riteshshah Says:

      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

  5. samiksha Says:

    please tel me There are How many the types of DDL Trigger in SQL server
    2005 ?

  6. patricia Says:

    thanks this educating for me

  7. laxman Says:

    how can i drop ddl triger?
    ie drop alter not work knw
    how it is possible?

  8. Don Says:

    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.

  9. Amit Kumar Gupta Says:

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

  10. Jason Says:

    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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: