Automated Notification Email for SQL Server Object Modifications

By: Daniel Calbimonte | Comments (3) | Related: > Monitoring

Problem

I am a developer and I need to send a notification email when objects change in a SQL Server database. How can I do that in SQL Server?

Solution

You can address this need using triggers and Database Mail.

In this example, we will show how to send an email reporting the change when a table, view or stored procedure is created, dropped or altered using a simple trigger and Database Mail.

Requirements

SQL Server 2008 or later.
An email account (I am using Hotmail in this example).
Internet connection.

Setting Up SQL Server Database Mail

We first need to configure Database Mail in SQL Server.

If you are not familiar with email configuration, please refer to these links:

Create SQL Server Trigger to Notify of Object Changes

Once that you have your email ready, write the following trigger:

ALTER TRIGGER object_changes
ON DATABASE
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_VIEW,DROP_VIEW,ALTER_VIEW,CREATE_PROCEDURE,DROP_PROCEDURE,ALTER_PROCEDURE
AS 
   DECLARE @data XML = EVENTDATA() 
   DECLARE @eventType nvarchar(100)= CONCAT ('EVENT: ',@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),+ CHAR(13))
   DECLARE @TsqlCommand nvarchar(2000)=CONCAT('COMMAND:   ',@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
   DECLARE @BodyMsg nvarchar(2100)=CONCAT(@eventType,@TsqlCommand)
 
   EXEC msdb.dbo.sp_send_dbmail  
   @profile_name = 'developer1',  
   @recipients = '[email protected]',  
   @body =@BodyMsg,
   @subject = 'The following object(s) was/were changed';
GO

This trigger will send an email if a table, view or stored procedure is created, dropped or altered.

Once executed, a trigger named object_changes will be created as shown below:

Breaking Down the Code

You can create a Data Manipulation Language (DML) trigger or a Data Definition Language (DDL) trigger. In this example, it is a Data Definition Language trigger. A DDL Trigger is a trigger that is triggered when a database object is created, altered or dropped. The DML triggers occur when an insert, delete or update operation occurs.

You can execute a trigger at the database (ON DATABASE) or server level (ON ALL SERVER):

ON DATABASE

In the FOR section we specify the events that will fire the trigger. In this case the trigger is fired when a table/view/procedure is created/altered/modified:

FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_VIEW,DROP_VIEW,ALTER_VIEW,CREATE_PROCEDURE,DROP_PROCEDURE,ALTER_PROCEDURE

For a complete list of events, refer to this link:

DDL Events

The @data variable is a XML variable that will store the trigger information in XML. We will use the function EVENTDATA() that will help us to get database events. In this case, we will capture trigger information:

DECLARE @data XML = EVENTDATA()

For more information about EVENTDATA, refer to this link:

EVENTDATA (Transact-SQL)

In the next lines we are storing in a variable named @eventType. In this variable we are storing the event. It can be a drop, create or alter event. We are using a CHAR(13) to add a new line:

DECLARE @eventType nvarchar(100)= CONCAT ('EVENT: ',@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),+ 
CHAR(13))

Next, we will add a new variable to capture the command line used to create/alter/drop the object:

DECLARE @TsqlCommand nvarchar(2000)=CONCAT('COMMAND: ',@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))

We will concatenate, using the CONCAT function, the eventType and TsqlComand variables in a variable BodyMsg, which will be used as the body message in our email:

DECLARE @BodyMsg nvarchar(2100)=CONCAT(@eventType,@TsqlCommand)

The last lines of code will send the email to our IT email. We use the sp_send_dbmail store procedure to send the email. Recipients will be the email address to receive the email. Profile_name is the name of your mail profile when you configure it. If you do not remember your profile name, you can use the msdb.dbo.sysmail_help_profileaccount_sp stored procedure to check. Body will be the body of the email. In this example, the body will show the event type and the command line executed.

EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'developer1',  
@recipients = '[email protected]',  
@body =@BodyMsg,
@subject = 'The following object(s) was/were changed';

For more information about the sp_send_dbmail stored procedure, refer to this link:

sp_send_dbmail (Transact-SQL)

Testing the Trigger

If everything is OK, we will add a new table in the Database to test the trigger which will fire this event and send an email:

CREATE TABLE [dbo].[Employee](
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[OrganizationNode] [hierarchyid] NULL,
[OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

The table create will fire a trigger and the trigger will send the email. Let’s look at the email that was sent:

As you can see, you can receive in the email the T-SQL command used.

You can also receive the user and the time when the command was executed. The following code can be used to track the user and the execution time in the trigger:

Declare @currentuser varchar(100)= CONVERT(nvarchar(100), CURRENT_USER)
Declare @executiontime datetime =getdate()

As you can see, you can combine the email service to receive information about database activity using sp_send_dbmail with the trigger. You could also combine the sp_send_dbmail with PowerShell, SQL Server Agent Jobs or SQL Server Integration Services.

Next Steps

For more information about sending emails in SQL Server and triggers refer to these links:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Thursday, February 13, 2020 - 7:05:55 AM - Gemah	Back To Top (84418)
Please help me. If i have field database ID, namestudent, time, date and emailstudent. And i want automatic send email notification to students concerned after students has been absent.