Thursday 26 October 2017

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
Read DDL Trigger: DDL Trigger in sql server
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



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. 

Popular Posts