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
Click Add to create a SMTP account
Set the parameters for the email account
|
Quick tip: More details about how to configure database mail you can find on msdn webpage |
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
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 |
Start ApexSQL Trigger and connect to the database you want to audit
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
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
© ALL RIGHTS RESERVED. Feedback Conditions d’utilisation Confidentialité Cookie Preference Center