Automated Notification Email for SQL Server Object Modifications

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


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?


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.


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

Setting Up SQL Server Database Mail

We first need to configure Database Mail in SQL Server.

database mail

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
   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';

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:

object changes

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):


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 a complete list of events, refer to this link:

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:


For more information about EVENTDATA, refer to this link:

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)'),+ 

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:

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

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

outlook mail

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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Calbimonte 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

Comments For This Article

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.

Monday, February 12, 2018 - 3:34:21 AM - Thomas Franz Back To Top (75179)


Drawback of a server / database trigger: you can't longer create In-Memory-Tables, since they are not compatible to those triggers.

Wednesday, February 7, 2018 - 5:03:13 PM - jeff_yao Back To Top (75132)

If sp_send_dbmail fails (for whatever reason), your DDL may fail as well.

So in this regard, it may be safer to store the EVENTDATA in a table. 

On the other hand, I strongly recommend you to use Extended Event instead of trigger to achieve the same result as XE is asynchronously coupled with DDL while trigger is more synchrously coupled.

get free sql tips
agree to terms