Understanding DDL Triggers in SQL Server 2005

  • srivathsani-296624

    Ten Centuries

    Points: 1208

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

  • Jurgen H.

    Say Hey Kid

    Points: 683

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

  • Cedric Cullingworth

    SSC Rookie

    Points: 31

    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

  • Bhavin Patel-257492

    SSC Eights!

    Points: 917

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

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • Bhavin Patel-257492

    SSC Eights!

    Points: 917

    I am confused with what you told in previous post.

  • John Kanagaraj

    Old Hand

    Points: 354

    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

  • Jurgen H.

    Say Hey Kid

    Points: 683

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

     

  • Charles Kincaid

    SSChampion

    Points: 13593

    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

  • paul2g2

    Grasshopper

    Points: 23

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

    thank you

  • lucassouzace

    SSC Eights!

    Points: 857

    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 11 (of 11 total)

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