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
|
Read How to: Configure Database mail in SQL server
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.
