How to configure and use before-after auditing in ApexSQL Audit

Applies to:

ApexSQL Audit


This article describes how to configure and run before-after auditing, reporting and alerting in ApexSQL Audit.


Before-after is database auditing solution designed to track information on who and when made the change, what objects were affected, what application and host are used, and most importantly what was the original (before) and after value upon data change.

This feature leverages SQL triggers to capture data changes for DML transactions in SQL databases. It is an essential tool to track information for Inserts, Updates, and Deletes against database objects and sensitive data.

Before-After auditing configuration

To configure Before-after auditing feature in ApexSQL Audit, follow these steps:

  1. Run the application interface and go to Before-after tab in the main ribbon

    ApexSQL Audit main ribbon. Before-after database auditing

  2. Add database for auditing in this step

    Add database Database auditing

  3. Continue by adding database tables from the Add table pane

    Add tables for Before-after

  4. Check the columns and DML operations that will be audited across the selected tables and hit the Apply button to finish

    DML auditing on columns

The auditing criteria can be also further polished via advanced filters to achieve greater precision via logical conditions that specify exact cases on which before-after auditing will be triggered.

To specify conditions, advanced filters have to be enabled before adding and combining the logical expressions. Use the Additional drop-down menu and tick the Advanced filter option to show the Advanced filter panel and start entering expressions in it:

Advanced auditing filters

Additional columns

Once the configuration is applied, the set of database triggers is created upon selected tables to collect the data on DML activities and fill the auditing trail, the next section will explain how to run reports and configure alerts.


The easiest way to review before-after database auditing data is by using the pre-determined reporting template Before-after auditing that is available when clicked on the New button in the Reports tab:

ApexSQL Audit reports. Before-after audit report

Additionally, we can use multiple filters to enhance the precision of the report by specifying servers, databases, logins, application and many more such as before and after values, also as before-after columns that will allow one to precisely create a report upon specific data:

Audit report filter criteria.

To run the audit report, click the Preview button which will extract data directly into the application interface, or Generate to export the data in available output formats:

ApexSQL Audit report overview.

Additional columns

By default, the report will show only columns with the before-after auditing enabled and their changes if such event occurs. Sometimes it can be practical to track some additional columns along with changed ones, in case we need to verify value association between them or some similar scenario. There is the Additional columns option that will enable custom selection of columns for every table, not to be audited but to be shown next to the audited column.

To enable this function, switch back to the Before-after tab in the main menu and within the Advanced drop down menu tick the Additional columns option. This will show another checkbox column next to the Insert, Update and Delete checkbox columns. Now any necessary column can be set with the additional column status.

Additional columns settings

This column, whether it is audited or not, will be displayed in a report next to a column in a table that experienced before-after event

Before- after reporting with additional columns

For more details about the audit report management, visit this ApexSQL Audit reporting feature overview page and learn how to create, run and schedule custom reports.


Apart from reporting, it is also very easy to create an alert to trigger whenever a specific case on data change occurs, which helps to identify any potential unwanted DML changes. The quick alert configuration wizard allows utilizing on the before-after alert type and set up the conditions on which an alerting notification is being sent:

SQL Server before-after alerting.

Moving forward through the wizard, the key step is to configure alert conditions upon which will alert raise a notification:

Alert rule definition. SQL Alert filter configuration.

Historical alert notification data is available in the History alert tab, while real-time notifications can be also delivered via e-mail and identify any alerting activity fast enough.

To learn more on how to configure it, consult this How to create a custom alert with an email notification guide:

ApexSQL Audit history alert tab.