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:
- Setting up Database Mail for SQL Server
- Setup SQL Server Database Mail to use a Gmail, Hotmail, Yahoo or AOL account
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 = 'IT@hotmail.com',
@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:
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 = 'IT@hotmail.com',
@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
) 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:
