Alert when database created or deleted

Category: sql server notification

Question

Kurt Woodfin on Wed, 17 Dec 2008 15:25:48


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

Replies

Joe Webb on Mon, 26 Jan 2009 13:20:41


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

 

VenkatVicky on Sat, 11 Aug 2018 14:25:20


Hi All,

Could you provide the DB drop/delete trigger.

Or any other way to get the notification.

Mohsin_A_Khan on Sat, 11 Aug 2018 15:34:44


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.