DDL Triggers

Discussion in 'microsoft.public.sqlserver.server' started by news.microsoft.com, Nov 14, 2007.

  1. Hi,

    I am trying to write a DDL Trigger so that whenever someone creates or drops
    Database I store information somewhere.

    My Trigger is working, now I am trying to make it more useful by extracting:
    * "Name" of the database being dropped or created
    * Name of the User performing the action ( login account)
    * And time the action was performed.

    CREATE TRIGGER ddl_trig_database

    ON ALL SERVER

    FOR CREATE_DATABASE

    AS

    PRINT 'Database Created.'

    INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
    values (??, ??, ??, 'DB Created')

    GO

    How do Iobtain userName, dbName, actionDate within the trigger ?

    Thanks
     
    news.microsoft.com, Nov 14, 2007
    #1
    1. Advertisements

  2. news.microsoft.com

    Ben Nevarez Guest

    It is a little bit more complicated than that. Take a look at the Eventdata
    function in BOL and some of the examples there. Eventdata is used to return
    the information you are looking for.

    Hope this helps,

    Ben Nevarez
    Senior Database Administrator
    AIG SunAmerica
     
    Ben Nevarez, Nov 14, 2007
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.