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