SQL Server database trigger auditing – How to customize data tracking and reporting – Part 1

ApexSQL Trigger uses SQL Server database triggers for capturing and storing changes that occur on the audited data and schema. More specifically, the DML database triggers are used for capturing INSERT, UPDATE or DELETE statements, and DDL trigger is used for capturing CREATE, ALTER or DROP statements. Now, since there are various types of columns in each database table, reading the audit report is often not very user-friendly and this especially applies to foreign key values in different tables.

For example, changes in audited numeric fields values, often used as reference keys, are reported in ApexSQL Trigger’s reporting system with the Old and New value columns. That may be enough for someone who is aware of the exact data structure that is being audited, but for someone who just needs to read the report for i.e. business purposes, it definitely isn’t. Therefore, there are two ApexSQL Trigger report enhancing features:

• Watches
• Lookups

Watches

ApexSQL Trigger uses watches as additional “readable” table fields along with the information about audited changes, even if the watched field itself isn’t changed. Watches allow adding recognizable fields to any audit database change, which is helpful to those reading the report.

Let’s say that some changes occurred in the Clients table, containing a list of contacts with their:

• ClientID
• Name
• Email
• Company name

If a contact name of a client company has been updated, for the initial report, it would not mean much to know only the old and new names. But if additional information, such as the company name is provided, that would make a difference.

To accomplish that, the corresponding watch should be added. The Watches panel can be accessed from the View tab, in the Show panel section. In this case, to add a watch AdressType name with the audited contact Name:

1. Select the Name field from the Audit column name drop-down menu

   

2. Select ModifiedDate from the Watch column name drop-down menu

   

3. Confirm the new watch by clicking the Add button

The created watch will provide additional company name information whenever an address type name is changed.

Any watch can be changed or deleted afterwards, but any previously stored information in the ApexSQL Trigger repository database will not be affected.

Be advised that watches can only be used for updated values. Watches will not work for inserted or deleted values.

As mentioned above, Lookups is another advanced feature of ApexSQL Trigger that helps creation of more human-readable reports.