How to send email alerts when a change is made to a database record (4290027)

Applies to
ApexSQL Trigger

Summary
This article shows how to customize ApexSQL Trigger architecture so that email alerts are sent when changes are made to a database record. Database mail must be configured and this solution can only be used with MS SQL Server 2005 or later

Description

1. Configure the database mail
   1. Open SQL Server Management Studio and connect to the SQL Server instance
   2. In Object Explorer, expand Management
   3. Right-click Database Mail and select Configure Database Mail
   4. Select Set up Database Mail
   5. Set the Profile name and Description

   6. Click Add to create a SMTP account

      

   7. Set the parameters for the email account

      

      

   Quick tip: More details about how to configure database mail you can find on msdn webpage

2. Copy the existing architecture from the following location:

C:\Users\<user>\AppData\Local\ApexSQL\ApexSQLTrigger2015 and paste it on the same location or Desktop for example. Change its name to ApexSQL2053_Mail.audx

• Open the newly created architecture and search for Dim TableFullName

• Add the following:

  'Variables for Mail Alerts

  Dim Mail_Profile, Mail_Recipients, Mail_Body, Mail_Subject

  as shown below:

  

• Below the StartTriggerNameDelete = “tr_d_AUDIT_” add the following:

  'Variables for Mail Alerts Mail_Profile = “John” Mail_Recipients = “John@mymail.com” Mail_Body = “Please verify database change.” Mail_Subject = “Unauthorized Database Change”

  Customize the Mail Alerts variables values from above with your desired values

  

• Search for INSERT INTO #tmp (Id) VALUES (CAST(@IDENTITY_SAVE AS DECIMAL)) (it comes three times as a search result) and add the following code (below all three of the search results)

  --Send email alert     EXEC msdb.dbo.sp_send_dbmail         @profile_name = '{% Audit.Print Mail_Profile %}',        @recipients = '{% Audit.Print Mail_Recipients %}',        @body = '{% Audit.Print Mail_Body %}' ,        @subject = '{% Audit.Print Mail_Subject %}'

  as shown below:

  

  In the Search dialog, click the Find next button, to navigate to another part when the modification needs to be applied. Repeat the previous step:

  

  In the Search dialog, click the Find next button, to navigate to another part when the modification needs to be applied. Repeat the previous step:

  

Quick tip: The change needs to be applied on all places inside the architecture in order to customize it properly

• Save changes to the ApexSQL2053_Mail.audx file

3. Start ApexSQL Trigger and connect to the database you want to audit

4. On the Advanced tab, click the Open and select ApexSQL2053_Mail.audx

   

5. Create the triggers. If the triggers have already been created, recreate them

   

When fired, the triggers created using this template will not only store audit data but will also send email alerts indicating that a change has been made

About ApexSQL Trigger

ApexSQL Trigger is a database auditing tool for SQL Server which allows you to monitor when and how the data and schema was changed and who made the change. Audit SQL Server 2005 – 2014 databases