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

Similarly as Watches, the Lookups feature allows the ApexSQL Trigger report to show a meaningful value from a related SQL Server database table, along with the value of the field that was audited. A more human-readable value can be shown in the audit reports, for example “111” (customer’s ID number) versus “John Smithson” (customer’s actual name).

To examine how to achieve this, the previously described Clients table will be used, along with the Invoices table, which consists of the following columns:

• InvoiceID
• Amount
• Date
• and Client (holding numeric ClientID of the corresponding row from the Clients table)

Now, if someone changes the “owner” (Client field) of the particular invoice, it will be shown in the audit report as:

Indicating that the Client field has changed the value from “111” to “222”. Not much of an information for the reader of this report.

However, if the Lookups feature is set up properly, the output from the installed SQL Server database trigger will be something like this:

Here is how to accomplish this; The Lookups panel can be accessed from the View tab, in the Panels panel section, just like the Watches:

1. Click Add in the Lookups pane
2. The Add lookup dialog will open
3. Edit the required fields:
   • Descriptively name a new lookup as “Invoice owner changed” (or similar, as it will be the name of the column in the SQL database auditing report)
   • Select the related table from the Related table drop-down menu
   • Using the Condition grid, make a pair of fields that links each Invoice (base table) row to the Clients (related table) row; in this case, shell link Invoices.Client with Clients.ClientID
   • It is possible to simply use the existing column from a related table (e.g. Clients.Name) as a reported value, but selecting the Expression option offers additional customizing features

   • If ‘Owner name: ‘ + {table}.Name + ‘ / ID: ‘ + LTrim(Str({table}.ClientID)) is used as an expression, the output will be exactly what is needed

     

This is just an example of what can be achieved with just a few clicks to add powerful customization to your trigger based SQL Server auditing.