Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

Data Auditing Tool (Audit Trigger Generator)

Rate me:
Please Sign up or sign in to vote.
5.00/5 (8 votes)
28 Sep 2012CPOL3 min read 58.2K   2.1K   29   16
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

    Image 1

    Image 2

  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. Image 3

  9. 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

Image 4

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.

Image 5

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).

Image 6

04. AuditActionType

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

Image 7

05. AuditLogData

AuditLogData table where all audit information will be maintained.

Image 8

Implementation steps

  1. Download the attachment (AuditTool.Zip) and create below mentioned audit tables.
  2. SQL
    [Audit].[AuditCategory]
    [Audit].[AuditSubCategory]
    [Audit].[AuditLogDecodeTableMapping]
    [Audit].[AuditActionType]
    [Audit].[AuditLogData]
  3. Create the function
  4. SQL
    Audit.GenerateDynamicQuery
  5. Create Audit Stored Procedures for creating Audit Triggers
  6. SQL
    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. SQL
    [Sales].[CountryRegion]
    [Sales].[SalesTerritory]
    [Sales].[SalesPerson]
  3. Configure sample table information in below Audit Tables.
  4. SQL
    [Audit].[AuditCategory]
    [Audit].[AuditSubCategory]
    [Audit].[AuditLogDecodeTableMapping]
    [Audit].[AuditActionType]
  5. Generate Audit Trigger Script and execute it.
  6. SQL
    /* 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)


Written By
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

 
QuestionTrack SQL server changes Pin
Kristen tornsten19-Jan-15 18:57
Kristen tornsten19-Jan-15 18:57 
QuestionAudit Trail For End Users Pin
Jackson K T17-Aug-14 19:16
professionalJackson K T17-Aug-14 19:16 
QuestionPostgreSQL Pin
hamedmirzaei23-Oct-12 3:47
hamedmirzaei23-Oct-12 3:47 
AnswerRe: PostgreSQL Pin
J.Jash1-Nov-12 5:13
J.Jash1-Nov-12 5:13 
QuestionQuestion on Article Pin
seanjjin8-Oct-12 16:32
seanjjin8-Oct-12 16:32 
AnswerRe: Question on Article Pin
J.Jash9-Oct-12 17:21
J.Jash9-Oct-12 17:21 
Questionquestion Pin
Member 94851574-Oct-12 12:09
Member 94851574-Oct-12 12:09 
AnswerRe: question Pin
J.Jash4-Oct-12 16:23
J.Jash4-Oct-12 16:23 
QuestionThanks Pin
Member 94781602-Oct-12 12:25
Member 94781602-Oct-12 12:25 
GeneralThank you Pin
Tammam Koujan24-Jun-12 2:51
professionalTammam Koujan24-Jun-12 2:51 
GeneralRe: Thank you Pin
Tammam Koujan24-Jun-12 3:15
professionalTammam Koujan24-Jun-12 3:15 
GeneralRe: Thank you Pin
J.Jash2-Jul-12 18:47
J.Jash2-Jul-12 18:47 
QuestionAudit update action Pin
bobfox20-Jun-12 12:08
professionalbobfox20-Jun-12 12:08 
GeneralRe: Audit update action Pin
J.Jash2-Jul-12 18:45
J.Jash2-Jul-12 18:45 
Questiondownload Pin
Robert Marck19-Jun-12 3:01
Robert Marck19-Jun-12 3:01 
AnswerRe: download Pin
J.Jash20-Jun-12 18:10
J.Jash20-Jun-12 18:10 

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

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