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

Applies to
ApexSQL Trigger

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


  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

      Database mail configuration wizard

    7. Set the parameters for the email account

      New database mail account

      DB Mail config wizard

    Quick tip icon

    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:

  3. 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 = “”
      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 icon

      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
  4. Start ApexSQL Trigger and connect to the database you want to audit

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

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

  7. 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