SQL Server Mentalist

BISQL # 26: Audit Trail Part : II- Implementation of Auto Auditing with Pros and Cons

July 23, 2011 11:19 am

Hi Friends,

This Article for implementing auto auditing will Covers Following points:

Need of Audit trailing
Implementation of Audit trailing
Code Specification
Pros of Audit trailing System
Cons Of Audit trailing system
Reference Used For Article

Need of Audit trailing:

When we are performing any modification on the database and we need to track all the changes in the for Database at every level we will implement Audit trailing
As per capitation requirement Auditing is most important aspect to track change details and easy to maintenance

Implementation of Audit trailing

1.Database Table Implementation

· Only one table which is going to track every change in configurable Audit table

Audit table specification

Explanation of fields

AuditID: Unique Audit Identification number
Type:I,U,D(I-Insert,U-Update,D-Delete)
TableName:On which table changes is occurred
PK: Primary Key for Audit Table
FieldName:On which filed Changes is occurred
OldValue: Old value of given field
NewValue: New value of given field
UpdateDate:When Change Occurred
UserName: DB User Name for Change

2. Database Stored Proc Implementation

We have Following Stored Proc Which will implement auditing for required Table

Stored Proc Name: CreateAudit
Parameters to Stored Proc and its use
1.@TableName: Name of the table to be auditing is required

2.@AuditType: We have 3 Types of auditing (Insert, Update, and Delete) i.e. value (I, U & D)

Code Specification

Code Snippet:

One can have all the Script here :http://sqlblog.com/blogs/paul_nielsen/archive/2007/01/15/codegen-to-create-fixed-audit-trail-triggers.aspx , http://autoaudit.codeplex.com/

Pros of this System

Easy for maintenance
Easy to implement and reuse
User can Set Audit for Specific table
Do Not consider auditing Staging table

Cons of this system

One Simple Daily batch Job For Archival of history of audit table only
Table Growth is Unstoppable during bulk insertion
May affect performance of System as we are using triggers for each insert ,update and delete

Reference Used For Development

1. Shadow table Auditing : http://www.c-sharpcorner.com/uploadfile/etechmentor/607/

2. prototype pattern Auditing : http://www.dotnetfunda.com/articles/article336-implementing-audit-trail-using-trigger.aspx

3. Output Clause : http://www.sqlhub.com/2011/07/audit-trail-with-output-clause-in-sql.html

4. By MSDN : http://technet.microsoft.com/en-us/library/dd392015(SQL.100).aspx

5. @ DBA level Auditing: http://www.sqlservercentral.com/articles/Auditing/63247/

6. Auto Audit : http://sqlblog.com/blogs/paul_nielsen/archive/2007/01/15/codegen-to-create-fixed-audit-trail-triggers.aspx , http://autoaudit.codeplex.com/,

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC

· BISQL # 28: Audit Trail Part : IV–Comparing Entity Framework and SQL CDC

· BISQL # 29 : Analysis and Documentation Query

· BISQL # 30 : How Should I Find Information about Data Type

· BISQL # 31 : How To Create Folder from SSMS

Hope you will like this post on Audit Trail Part II.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

           For More information related to BI World visit my Mentalist Blog

                        Link Resource Blog >> Daily Interesting links

                        SQL Server Mentalist >> SQL Learning Blog

                        Business Intelligence Mentalist >> BI World

Connect With me on

| FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

Posted by Vishal Pawar

Categories: Query, SQL

Tags:

« Older Newer »

5 Responses to “BISQL # 26: Audit Trail Part : II- Implementation of Auto Auditing with Pros and Cons”

[…] BISQL # 26: Audit Trail Part : II- Implementation of Auto Auditing with Pros and Cons […]

By BISQL # 28 : Audit Trail Part : IV–Comparing Entity Framework and SQL CDC for Audit Trail « SQL Server Mentalist on July 23, 2011 at 12:29 pm
[…] BISQL # 26: Audit Trail Part : II- Implementation of Auto Auditing with Pros and Cons […]

By BISQL # 28 : Audit Trail Part : IV–Comparing Entity Framework and SQL CDC for Audit Trail - Vishal Pawar's Blog on July 29, 2011 at 12:46 pm
[…] BISQL # 26: Audit Trail Part : II- Implementation of Auto Auditing with Pros and Cons […]

By List of monthly post of MS BI and SQL blog–July 2011 « SQL Server Mentalist on July 31, 2011 at 10:11 pm
[…] BISQL # 26: Audit Trail Part : II- Implementation of Auto Auditing with Pros and Cons […]

By List of monthly post of MS BI and SQL blog–July 2011 « (B)usiness (I)ntelligence Mentalist on July 31, 2011 at 10:19 pm
[…] BISQL # 26: Audit Trail Part : II- Implementation of Auto Auditing with Pros and Cons […]

By List of monthly post of MS BI and SQL blog–July 2011 « Dactylonomy of Web Resource on July 31, 2011 at 10:30 pm

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.