Create a SQL Server Database Email trigger, when a tables records are updated.

A very useful tool in SQL Server is the database email service. For our latest project we needed to enable email updates on the recording of new data in our system. These instructions require the SQL Database Email service to already be configured, if you need instructions on how to do this please see my post https://philsdevblog.home.blog/2022/08/10/setting-up-sql-server-database-email/

For this to work, I needed to check any changes at all to our notification table (only for inserts) and email a defined group of users, based on the location of the data. Here I’m going to explain the basic principle of the function and how you can implement a similar feature in your system.

Create the trigger

First of all what is a trigger in SQL Server? Here’s the official Microsoft explanation.

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires, whether table rows are affected or not.

Microsoft documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16

How to create a basic trigger. This simple script will create a trigger for a table for any insert, update or delete command that is executed.

CREATE TRIGGER [dbo].[trg_TestEmail] 
ON [dbo].[tblTestEmailTrigger] 
AFTER INSERT, UPDATE, DELETE 
AS 

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'YOUR_EMAIL_PROFILE_IN_SQL_DATABASE_MAIL_CONFIG', 
@recipients = 'RECIPIENTEMAIL@ADDRESS.COM', 
@body = @EmailBody, 
@subject = @EmailSubject
GO

Taking this simple example to the next level, we can begin to add some extra functionality ourselves. One of the key criteria is that I needed to only notify users in a certain location, if a relevant record was added to the table.

To do this I took 2 steps.

1. Table preparation.
   1. The table itself already contained a LocationID for each record.
   2. I had previously created a table of UserID’s with a field for LocationID for every record.
2. Update the trigger with some code to populate a new recipients parameter.

The principle being the solution is to check the latest record for it’s LocationID. We then join to our users table on the LocationID key, and return a list of relevant email addresses into our string parameter. The parameter is then passed to our execution statement. Notice the latest version of the trigger only executes on inserts.

CREATE TRIGGER [dbo].[trg_TestEmail] 
ON [dbo].[tblTestEmailTrigger] 
AFTER INSERT
AS 

DECLARE @TestEmailList nvarchar(max)
DECLARE @EmailBody nvarchar(500)
DECLARE @EmailSubject nvarchar(500)

SET @EmailSubject = (SELECT "Run your own system specific query here to populate the email subject")

SET @EmailBody = (SELECT "Run your own system specific query here to populate the email body with relevant data")

-- Set the LocationID parameter to the LocationID from the latest record in your table. (In this example the largest Primary Key is all we need).
SET @LocationID = (SELECT TOP 1 LocationID FROM tblTestEmailTrigger ORDER BY TestEmailTriggerID DESC)

-- Select the users email addresses based on the location, and format into a comma separated string ready to pass to our execution statement

SET @TestEmailList = STUFF(
						(SELECT ',' + CONVERT(NVARCHAR(50), Email) FROM UserTable WHERE LocationID = @LocationID FOR xml path('')),1,1,''
							)

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'YOUR_EMAIL_PROFILE_IN_SQL_DATABASE_MAIL_CONFIG', 
@recipients = @TestEmailList, 
@body = @EmailBody, 
@subject = @EmailSubject,
@importance = 'High'
GO

You will also notice an additional parameter I added to the execution statement, @importance = ‘High’. This ensures the email is delivered with a high importance notification. In my example these notifications are meant to be exceptions to the norm, and expected to be delivered at a very low frequency, but alerting on a highly important level. This is appropriate for this use case, but be careful using this on frequently delivered notifications or it could become annoying to the users and have less impact than intended.

That’s it, this table is now set up to deliver notifications only to relevant users when a new record is recorded in the table data.