none
Alert when database created or deleted RRS feed

  • Question

  • I am new to SQL, and I am trying to create an alert to e-mail an operator when a database is created or deleted.  Does anyone have a script to set this up.

    Kurt

    Kurt
    Wednesday, December 17, 2008 3:25 PM

Answers

  • Hi Kurt - 

    Here's a script from BOL that creates a DDL trigger that will fire when a database is created.

    IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
    GO
    CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
    GO
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
    GO

     

    Here's the link to the original page. http://msdn.microsoft.com/en-us/library/ms186406(SQL.90).aspx

    I'd suggest writing this information to a table and then create a job that runs every few minutes to analyze the table and send an email if needed. Sending an email from within a trigger is not generally recommended.

    HTH...

    Joe

     


    Joe Webb, SQL Server MVP | http://weblogs.sqlteam.com/joew/
    Monday, January 26, 2009 1:20 PM
    Moderator

All replies

  • Hi Kurt - 

    Here's a script from BOL that creates a DDL trigger that will fire when a database is created.

    IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
    GO
    CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
    GO
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
    GO

     

    Here's the link to the original page. http://msdn.microsoft.com/en-us/library/ms186406(SQL.90).aspx

    I'd suggest writing this information to a table and then create a job that runs every few minutes to analyze the table and send an email if needed. Sending an email from within a trigger is not generally recommended.

    HTH...

    Joe

     


    Joe Webb, SQL Server MVP | http://weblogs.sqlteam.com/joew/
    Monday, January 26, 2009 1:20 PM
    Moderator
  • Hi All,

    Could you provide the DB drop/delete trigger.

    Or any other way to get the notification.

    Saturday, August 11, 2018 2:25 PM
  • Hi All,

    Could you provide the DB drop/delete trigger.

    Or any other way to get the notification.

    Hi there, this is almost a 10-year old thread. Please open a new thread to get more responses. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Saturday, August 11, 2018 3:34 PM