Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Automated Notification Email for SQL Server Object Modifications


By:   |   Last Updated: 2018-02-06   |   Comments (2)   |   Related Tips: More > 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

  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
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 [email protected],
   @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:

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

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:

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:

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 [email protected],
@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
) 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:

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:



Last Updated: 2018-02-06


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Remark:

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


Wednesday, February 07, 2018 - 5:03:13 PM - jeff_yao Back To Top

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.


Learn more about SQL Server tools