Send email to DBA team when a database created or dropped or Altered

Our requirement is that when any DBA team member creates or Alter or dropped a mail alert trigger to Team members.

With the help of DDL trigger, we can achieve.

Read Trigger: Trigger in SQL server

http://bageshkumarbagi-msbi.blogspot.in/2017/08/trigger-in-sql-server.html

Read DDL Trigger: DDL Trigger in sql server

http://bageshkumarbagi-msbi.blogspot.in/2017/08/ddl-trigger-in-sql-server.html

Let’s see the example

Here I am creating a DDL trigger on the server and with the help of sp_send_dbmail SP sending the email.

USE master GO CREATE TRIGGER [ddl_trig_database] ON ALL SERVER FOR CREATE_DATABASE AS declare @results varchar(max) declare @subjectText varchar(max) declare @databaseName VARCHAR(255) SET @subjectText = 'DATABASE Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() +' on ' + cast(GETDATE() as varchar(20)) SET @results =   (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)')) --sending mail EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'Bagesh_Mail',  @recipients = 'bageshkumarbagimsbi@gmail.com',  @body = @results,  @subject = @subjectText,  @exclude_query_output = 1 --Suppress 'Mail Queued' message GO

Read How to: Configure Database mail in SQL server

http://bageshkumarbagi-msbi.blogspot.in/2015/10/configure-database-mail-in-sql-server.html

Now trigger is created successfully.

Whenever we will create a database a mail trigger to 'bageshkumarbagimsbi@gmail.com'.

Let’s see. Now I am creating a database on my server

CREATE DATABASE [Test_DDL_TRIGGER]  CONTAINMENT = NONE  ON  PRIMARY ( NAME = N'Test_DDL_TRIGGER', FILENAME = N'D:\Test_DDL_TRIGGER.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  LOG ON ( NAME = N'Test_DDL_TRIGGER_log', FILENAME = N'D:\Test_DDL_TRIGGER_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO

When we created a database a mail trigger in the inbox.

See the database in the server.

Now I am seeing my Inbox

See the mail body

Writing a trigger when the database is dropped

USE master GO ALTER TRIGGER [ddl_trig_database_DROP] ON ALL SERVER FOR DROP_DATABASE AS declare @results varchar(max) declare @subjectText varchar(max) declare @databaseName VARCHAR(255) SET @subjectText = 'DATABASE Droped on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() +' on ' + cast(GETDATE() as varchar(20))  SET @results =   (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)')) EXEC msdb.dbo.sp_send_dbmail  @profile_name = 'DDL_Trigger_Test',  @recipients = 'bageshkumarbagimsbi@gmail.com',  @body = @results,  @subject = @subjectText,  @exclude_query_output = 1 --Suppress 'Mail Queued' message GO

Trigger created successfully.

Let’s drop the database.

DROP DATABASE Test_DDL_TRIGGER

See the result.

See the body

Similarly, we can write the trigger on Alter database, Create any object on the database.

Hope this will be helpful. Please leave your comment.