Click here to Skip to main content
Click here to Skip to main content

Data Auditing Tool (Audit Trigger Generator)

, 28 Sep 2012
Rate this:
Please Sign up or sign in to vote.
Data Auditing 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.

  1. Application admin can use Audit data for analysis.
  2. Forensic analysis on database tampering.
  3. 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.

Tool Feature

  1. This Audit Tool will track entire application audit data in a single table, easy for generating audit reports. Refer below snapshot “Audit Log Table with Sample Data”.
  2. Audit Log Table with Sample Data

  3. Audit Tool also will track below information in Audit table, this information will help generating user friendly report.
  4. Table Name
    Column Name
    RecordIdentifierName(Primary Column name)
    RecordIdentifierValue(Primary Column Data)
    Actionby_ UserID
    ActionDateTime (Time stamp)
    Action Type (Insert / Update /Delete)
    Audit Description
    Old Value (Data will be loaded for Update and Delete action)
    Old Value Decode (Data from foreign key reference table)
    New Value (Data will be loaded for Insert and Update action)
    New Value Decode (Data from foreign key reference table)
    Category
  5. Any table changes will not impact Application Audit Trial, Audit triggers are capable of reading columns dynamically and load data it in Audit table.
  6. For update action you can load only modified data in Audit Table.
  7. 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”.
  8. 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.

  1. Master Data
  2. Sales
  3. 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.)

05. AuditLogData

AuditLogData table where all audit information will be maintained.

Implementation steps

  1. Download the attachment (AuditTool.Zip) and create below mentioned audit tables.
  2. [Audit].[AuditCategory]
    [Audit].[AuditSubCategory]
    [Audit].[AuditLogDecodeTableMapping]
    [Audit].[AuditActionType]
    [Audit].[AuditLogData]
  3. Create the function
  4. Audit.GenerateDynamicQuery
  5. Create Audit Stored Procedures for creating Audit Triggers
  6. Audit.Generate_AuditTrigger_For_Insert
    Audit.Generate_AuditTrigger_For_Update
    Audit.Generate_AuditTrigger_For_Delete
  7. Generate Audit triggers and deploy it.

Implementation training with sample data

  1. Download the Sample attachment (AuditToolSample.Zip) and create below sample tables.
  2. [Sales].[CountryRegion]
    [Sales].[SalesTerritory]
    [Sales].[SalesPerson]
  3. Configure sample table information in below Audit Tables.
  4. [Audit].[AuditCategory]
    [Audit].[AuditSubCategory]
    [Audit].[AuditLogDecodeTableMapping]
    [Audit].[AuditActionType]
  5. Generate Audit Trigger Script and execute it.
  6. /* Generate the Audit trigger using below SQL script and execute it*/
    EXEC [Audit].[Generate_AuditTrigger_For_Insert] 
      'SalesPerson|SalesPersonInsertAudit,SalesTerritory|SalesTerritoryInsertAudit,
       CountryRegion|CountryRegionInsertAudit', 'Jash', 'Sales', 'True'
    go
    EXEC [Audit].[Generate_AuditTrigger_For_Update] 
      'SalesPerson|SalesPersonUpdateAudit,SalesTerritory|
      SalesTerritoryUpdateAudit,CountryRegion|CountryRegionUpdateAudit', 'Jash', 'Sales', 'True'
    go
    EXEC [Audit].[Generate_AuditTrigger_For_Delete] 
      'SalesPerson|SalesPersonDeleteAudit,SalesTerritory|
      SalesTerritoryDeleteAudit,CountryRegion|CountryRegionDeleteAudit', 'Jash', 'Sales', 'True'
  7. Try with test data.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

J.Jash
Technical Lead
United States United States
I am having 11+ Years of IT experience in Web based and Client-Server application development using Microsoft technologies.
 
Software Architect, as a field, had always fascinated me right from my college days, and always been fascinated with new technologies and Ideas.
 
I have also received EARLY ACHIVER award from Microsoft in Year 2003 for MCAD certification. And acquired MCDBA certification for SQL Server 2000.

Comments and Discussions

 
QuestionAudit Trail For End Users PinprofessionalJackson K T17-Aug-14 19:16 
QuestionPostgreSQL Pinmemberhamedmirzaei23-Oct-12 3:47 
AnswerRe: PostgreSQL PinmemberJ.Jash1-Nov-12 5:13 
QuestionQuestion on Article Pinmemberseanjjin8-Oct-12 16:32 
AnswerRe: Question on Article PinmemberJ.Jash9-Oct-12 17:21 
Questionquestion PinmemberMember 94851574-Oct-12 12:09 
AnswerRe: question PinmemberJ.Jash4-Oct-12 16:23 
QuestionThanks PinmemberMember 94781602-Oct-12 12:25 
GeneralThank you PinmemberTammam Koujan24-Jun-12 2:51 
GeneralRe: Thank you PinmemberTammam Koujan24-Jun-12 3:15 
GeneralRe: Thank you PinmemberJ.Jash2-Jul-12 18:47 
QuestionAudit update action Pinmemberbobfox20-Jun-12 12:08 
GeneralRe: Audit update action [modified] PinmemberJ.Jash2-Jul-12 18:45 
Questiondownload PinmemberMember 652253419-Jun-12 3:01 
AnswerRe: download PinmemberJ.Jash20-Jun-12 18:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 28 Sep 2012
Article Copyright 2012 by J.Jash
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid