Post reply

Understanding DDL Triggers in SQL Server 2005

  • March 14, 2007 at 1:19 pm

    #177127

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/sSrivathsani/2927.asp

  • July 4, 2007 at 12:42 am

    #717064

    It's a pitty it does not cover renaming columns, tables...

  • July 4, 2007 at 2:27 am

    #717083

    Most of the code samples are untested and need fixing if you want to see them work.

    Here's what I've settled on to cover most activities - created in all mission-critical databases:

    CREATE TRIGGER Trig_DBEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS

    AS

        INSERT master.dbo.ddl_all_databases

            (

                DateTime,

                LoginName,

                DBName,

                UserName,

                EventType,

                CommandText

            )

        SELECT  GETDATE(),

                EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(50)'),

                EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(50)'),

                EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(50)'),

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

                EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(1024)')

    GO

  • July 5, 2007 at 9:01 am

    #717431

    where do I create trigger? in master database? it will fire where any new database is created,right?

  • July 5, 2007 at 10:15 am

    #717448

    I can see trouble brewing here.  Would it be possible to:

    1. Create triggers that block all changes on tables, etc.
    2. Create a trigger that blocks changes on all triggers?

    How would one every get out of this?

    Another reason to tighly control such things.  A ticked off admin could do this and then leave.  You might not find it for a long time.  I have production databases that have not had a DDL change in years.

     

    ATBCharles Kincaid

  • July 5, 2007 at 10:23 am

    #717453

    I am confused with what you told in previous post.

  • July 5, 2007 at 5:24 pm

    #717528

    Oracle has had this functionality for a loooong time. As a result, you might want to look at how Oracle databases use the DDL (and other) System level triggers.

    For myself, I have created a DDL trigger in Oracle's SYS schema to record all changes including what object changed when, and by whom. As well, I have LOGIN/LOGOFF triggers that can be used to track who logged in, from where and if they changed any data (track COMMITs/rollbacks from Oracle's DMV)

    John Kanagaraj

  • July 6, 2007 at 12:36 am

    #717581

    As i already told , if you use the sp_rename stored procedures these triggers do nothing, nada.

     

  • July 6, 2007 at 8:02 am

    #717698

    Thank you.  You just answered two questions for me at the same time:

    1. How to force everything to go through this method so that everything is tracked.
    2. How to get around the "lock out" that I discribed.

    You restrict the use of sp_rename.  This forces everybody else to use ALTER TABLE, ALTER COLUMN, etc. to make changes.  If your logon still has access to sp_rename, etc. this gives you a back door.

    ATBCharles Kincaid

  • August 21, 2008 at 10:42 am

    #861418

    can Iplease haver the command for trigger and stored procedures and the standard format for querying tables

    thank you

  • February 23, 2009 at 6:14 am

    #948524

    hm, is good script but, because: DB_User, Event_Type

    is null? is not LOGIN of user?

    por example: i create login:

    create login lucas with password = '123'

    i connect with lucas

    now, i create table:

    create table tbl ( id int )

    DB_User: lucas

    ??

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy