Data Auditing Tool (Audit Trigger Generator)

Introduction

In today’s IT word most of the applications need data auditing, whether it is an ERP system or Work Flow based Application or CRM tool or any database system,
auditing has become essential part of that system. Basically Auditing will help on below areas.

Application admin can use Audit data for analysis.

Forensic analysis on database tampering.

Audit data also will help quick data recovery to some extent.

There are multiple approaches in implementing data audit, but having Audit logic in table’s trigger is the most efficient approach and also its very easy for maintenance.

I have created the tool (Stored Procedure) that will generate audit triggers for your tables and load Audit data in a single table for your entire application.
You can also customize the Stored Procedure based on your application need.

I have used this tool in couple of my projects and saved around 80% of coding effort. I am sure that you can implement Audit Trial in your entire project in couple of days using this tool.

And also I’ve uploaded sample table and test data that will help you understanding Audit Trial.

Any table changes will not impact Application Audit Trial, Audit triggers are capable of
reading columns dynamically and load data it in Audit table.

For update action you can load only modified data in Audit Table.

Will track table’s primary column name and data as record identifier, this can be overridden by configuring specific column as record Identifier in Audit
configuration table. For example, “Sales Person Name” can be configured in Audit configuration table as a record identifier, In that case Trigger will use “Sales
Person Name” as a record identifier instead of “Sales Person ID”.

Trigger can go and pull the data from foreign key reference tables and track it. Let’s take below example, while updating “SalesPerson” table it can go and
pull “Territory Name” from “SalesTerritory” table by using “Territory ID” that will give complete information for data auditing.

Audit Tables needed for Data Auditing

Below Audit tables need to be created and configured for Audit implementation. Let’s understand significance of each table.

01. AuditCategory

This table will help categorizing Audit data, for example Audit data can be classified like below.

Master Data

Sales

Purchase

02. AuditSubCategory

Application table name should be configured with category mapping.

Specifying table’s primary key or Specific Column information in RecordIdentifier column will help Audit trigger to load both RecordIdentifier
column name and data in audit table. This will help locating the record and match with Actual table data.

The purpose of AuditCategory and AuditSubCategory table is creating audit report with proper grouping.

03. AuditLogDecodeTableMapping

This table is really important one. This table should be loaded with all foreign key references so that Audit trigger can pull necessary information from
foreign key table and load it in Audit table (refer feature 6).

04. AuditActionType

This table is to maintain action details (Insert, Update, and Delete.)

If your application has common update statement /stored procedure which will update the entire record in every execution. But the user is updating only few columns /data in every time, there is no point tracking both changed and unchanged data in Audit table. Since the audit trigger has data from both Inserted (new record) and Deleted (Old record) table you can validate and track only changed data in Audit table.