-
Titolo
How to create an auditing plan -
Descrizione
How to create an auditing plan -
Risoluzione
Applies to
ApexSQL TriggerSummary
This article explains how to install the ApexSQL Trigger auditing architecture and create an auditing plan on a sample database.Description
Installing an auditing architecture
To track data changes in database tables, an auditing architecture needs to be installed on a database. An auditing architecture contains:
- Auditing triggers - installed on each table included in the auditing process
- Auditing tables – installed in an audited database to keep data changes
- Stored procedures – installed in an audited database to manage the auditing architecture, audited data, and generate reports
After establishing a connection to a database, if an auditing architecture is not installed, a popup dialog appears, to inform the user that the architecture needs to be installed. On confirmation, the Manage architecture window appears where you can install an auditing architecture, or load a customized architecture:
If you want to keep the audited data in another database, auditing tables and stored procedures must be installed in the corresponding database, and the Trigger template needs to be modified.
Quick tip:
Auditing triggers must be installed on a database tables included in the auditing process no matter if the data changes will be stored in another database.
Creating an auditing plan
When an auditing architecture is installed, tables from an audited database appear in the application main grid. By default, all operations (INSERT, UPDATE, and DELETE) are checked for auditing. However, you can remove any of them, and make a selection of tables and columns to be audited:
Quick tip:
Columns will not be selected automatically when including a specific table in the auditing process, and the auditing triggers will not be installed for a specific table, even if it is checked in the main grid. Tables checked for auditing, without included columns will be grayed out.
To show additional values in the auditing report, you can optionally use the Lookups, and Watches features.
Creating the auditing triggers
When all tables/columns have been selected for auditing, the user can create the auditing triggers by clicking the Create button, in the Triggers group, under the Home tab:
Quick tip:
Each time an auditing architecture, or any of the features (Lookups, Watches) are changed, the auditing triggers must be re-created, in order to apply an auditing setting to a database.
The user can use a script to create the triggers to execute it on another database, or modify it to specific requirements, since it will appear before the execution in the Script window:
Clicking the Execute button creates the triggers on the specified tables.
The auditing triggers can be managed additionally, after creating. The user can enable/disable any of them to stop tracking changes.
When the triggers are created, all changes will be tracked and stored in the auditing tables.