Get an alert when a certain record changes in SQL Server

Auditing a database is the first step towards staying updated about database changes. However, if specific, highly sensitive data needs reviewing, an immediate notification of any change is preferable.

Sending email alerts to one or more recipients is one of the most common ways of immediate notification. Sending emails for every change in a database is not advised. It will, in most cases, quickly fill your inbox, and add many unnecessary actions, which can also affect the overall application performance. Therefore, it is important to choose the changes that you want to be alerted about, carefully.

How to set up email alerts

If you’re using SQL Server Auditing, logs stored in a file, application logs or security logs can be used.

Create a job to run every minute. The job should:

• Run the stored procedure that reads the logs and finds the changes that you want to be notified about. If you’re storing audit logs into a file, use the fn_get_audit_file function to read it
• Send an email if records about specific transactions are found

A drawback of this method is that database-level auditing cannot be applied to columns, so changes cannot be tracked on a specific column, but on the whole table. It cannot be concluded what has actually happened from the following information:

UPDATE  TOP  ( 200 )  humanresources . employeepayhistory 
SET     modifieddate  =  @ModifiedDate 
WHERE   (  businessentityid  =  @Param1  ) 
       AND  (  ratechangedate  =  @Param2  ) 
       AND  (  rate  =  @Param3  ) 
       AND  (  payfrequency  =  @Param4  ) 
       AND  (  modifieddate  =  @Param5  )

Another solution is to use the SQL Server Change Data Capture or Change Tracking features.

To get the changed records for a table and information about these changes, use the CHANGETABLE(CHANGES…) function. It queries the records stored in the internal change tracking tables.

Then, the same procedure as above should be used – a stored procedure that reads the records, and a job that will run every minute.

A drawback of these features is that they do not track the time of the change nor old and new values of the changed record. Since the operation is synchronous, it prolongs the transaction time. Another drawback is that you have to query the tables periodically and then send the notification email.

The next solution is to create triggers on a table that needs auditing. Triggers will be fired when a record is inserted, deleted or updated and msdb.dbo.sp_send_dbmail should be used to send an email alert to a specified email recipient, immediately:

CREATE TRIGGER t_Pers 
ON Person.Person 
AFTER INSERT, UPDATE, DELETE 
AS 
   EXEC msdb.dbo.sp_send_dbmail 
                        @profile_name = 'ApexSQLProfile', 
                        @recipients = 'marko.radakovic@apexsql.com' , 
                        @body = 'Data in AdventureWorks2012 is changed', 
                        @subject = 'Your records have been changed' 
GO

As triggers are fired per batch, not per row, it should be ensured that all batch transactions are captured, and no data is lost.

An additional drawback is that a lot of code needs to be written manually, while running a job every minute is not a practical solution. Once an email is sent, there is no record about the changes made, such as old values or the time when those changes have occurred. Managing triggers one by one on a database with a large number of objects can be time-consuming.

The recommended auditing and alerting solution is to have a specialized SQL Server auditing tool do everything for you – create triggers for the columns and actions that need auditing, store the audited records for later reference, and automatically send emails. It also provides easy management of multiple triggers simultaneously.

ApexSQL Trigger is a database-auditing tool for SQL Server, which captures the data and schema changes that have occurred on a database, including information on who made the change, which objects were affected by it, when it was made, as well as information on the SQL login, application and host used to make the change. It stores all the captured information in a central repository and exports it in print friendly formats.

To audit data changes and send email alerts using ApexSQL Trigger:

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

      

      

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”
     

     Quick tip: You can customize the Mail Alerts variables values from above with your desired values in this step, or you can change them later using the ApexSQL Trigger Template editor. In this example we will leave Mail Alerts like this here, and change them later.

     

   • 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 each of the three 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

   

To modify the new architecture and set the email account:

1. On the Home tab, in the Triggers group, click Edit template

2. Find the following code:

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

3. Modify it using the settings for your database mail account, set in step #1

   

4. In the Template editor, from the Run menu click the Process button, and then save the changes using the Save button

5. In the Main grid, select the table that contains sensitive data

   

6. If any triggers exists, recreate them using the steps below

7. In the Columns pane, select the columns to audit

8. On the Home tab, in the Triggers group, click the Create button

9. The trigger script is opened in the internal editor. Check out the code for msdb.dbo.sp_send_dbmail

   

10. Click the Execute in the menu

11. After the triggers are successfully created, you’ll get the following message
    

    

Now all you have to do is wait for those emails

Getting email notifications whenever there is an insert, delete or update of a specific table column doesn’t have to be complicated, and can be accomplished with almost no code writing. Let ApexSQL Trigger do the work for you!

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.