Data auditing is something usually that we apply to larger, enterprise level applications, but adding an audit trail to smaller applications, especially shrink wrapped commercial applications can be a huge value add; especially if it can be done quickly and easily
Think of an application you are building for a client. Wouldn’t it be cool to deliver it self-auditing? The commercial tool you ship to a larger client base or sell to the public, could also be made self-auditing, so end users would have a full history of all changes to sensitive tables.
Despite the obvious appeal, integrated auditing into a rapidly changing, dynamic application can be a real pain. Each time a table changes, triggers must be recreated or you risk errors or worse missing audit data, that was never applied to new columns.
ApexSQL Trigger was designed to as a Rapid Application Development (RAD) tool for quickly and easily applying a data change audit trail to any SQL Server database.
ApexSQL Trigger ecosystem
The ApexSQL Trigger application ecosystem consists of the following
- Architecture objects – these are tables for storing audit data aka repository and the views and stored procedures that allow for querying and reporting off of the repository. These objects can be installed in the database you are auditing or (as we would recommend) a separate database.
Trigger template – this is the DNA of your auditing triggers. Each will be created exactly the same based on the template. The template can be edited and changed at any time and ApexSQL Trigger even has a self-contained IDE for managing templates.
See Using the template editor in ApexSQL Trigger for how to manage templates in the ApexSQL Trigger IDE
Project file – Once you have used ApexSQL Trigger to create your auditing plan, hit save and you will create a project with all of your table selections and configuration settings. This project can be re-opened again, at any time, to regenerate triggers for the database (more on project files below)
- Triggers – Triggers are the output of ApexSQL Trigger. When you load a project, specify a template and run it against a database, it will produce a script that will generate all of the triggers required to create that audit plan.
ApexSQL Trigger application – this application can consume and execute projects, configure and store settings, create triggers and even allow for editing and executing templates and SQL code from within the application
ApexSQL Trigger console application – this application has a command line interface and allows for running ApexSQL Trigger automatically and unattended. See ApexSQL Trigger Command Line Interface (CLI) switches for a complete reference of the CLI
The stored project contains some critical information, to allow the auditing plan to be re-executed, the same way, every time
Configuration options – there are various settings that control the behavior of the application and how the triggers are created
Table selections – these are the tables specifically selected for auditing. These are generally “sensitive” tables that have transactional information, such as an Invoice table where new invoices and changes to existing ones were stored. An audit trail on this information would be very helpful.
Column selections – individual columns can be selected in the profile as well
- Trigger types – for each table you can select Insert, Update and/or Delete triggers
- Advanced elements like Watches and Lookups that can be created and added to the profile
A data source and credentials
Projects offer a very convenient way to store all of your selections and configuration settings which makes it very convenient for unattended execution, because all you have to do is call ApexSQL Trigger console application from the command line and pass it a project
The architecture of a database audited with ApexSQL Trigger is very simple
- Tables – these store the audit data. These objects include:
- AUDIT_prc_StandardReport [list]
A key design decision is whether to log audit information in the audited database or to put it in a separate database. ApexSQL Trigger makes this very easy, in that you can specify which option to choose and even specify the name of the audit database and the insert statements in the triggers will be automatically updated to append the database qualifier, so the data will be written to that database
Inside the trigger you will see the SQL for inserting a record into the repository
Insert into [AuditingDatabase].[schema].[AuditingTable] (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN);
See Installing audit tables on another database for how to implement this approach
We recommend to use a separate database to store audited information. That allows you to audit multiple databases but have a single repository. It also makes continuous integration and delivery easier if you don’t have to worry about managing an auditing repository along with your core database.
Putting it all together
Once you understand the product and the approach you can start auditing your database. See the following articles to get started
As well as some advanced features
- Using the template editor in ApexSQL Trigger
- How to use the Watches feature in ApexSQL Trigger
- How to use the Lookup feature in ApexSQL Trigger
- How to send email alerts when a change is made to a database record
- Auditing reports in ApexSQL Trigger
- How to customize an auditing report
- Schedule ApexSQL Trigger report generation
Automating the process
Once you have created your first audit plan and successfully executing it, it is time to automate the process
You can set up a simple batch file or PowerShell script to instantiate the ApexSQL Trigger console application, supply it with a project file and execute it. In this way you can automatically regenerate triggers every night or integrate this as part of a continuous integration or delivery process
See below for a simple example
- Obviously, other switches and combinations of switches can be used. Full documentation, along with examples of the ApexSQL Trigger CLI can be found here ApexSQL Trigger Command Line Interface (CLI) switches