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

Audit Trail using XML column in SQL Server 2005

, 25 Nov 2011
Rate this:
Please Sign up or sign in to vote.
Implement audit trail technique using XML column in SQL Server 2005

Introduction

Currently, I am developing an application on ASP.NET & SQL Server 2005. This application requires audit trail mechanism to track user changes on a database table. I have developed a technique based on SQL Server 2005 XML data type. This technique only requires one table and does not require any trigger as triggers create more overhead. In this article, I discuss how I have implemented audit trail in my application.

Audit Trail using Single Table with XML Column

I have used a table named tbl_audittrail to store user changes on a database table. I have created a table named tbl_student for example. Every time a record is inserted or updated in tbl_student, a corresponding record is inserted in tbl_audittrail table. The structure of the two tables is as follows:

Table Structure

The primary key field of tbl_student table, i.e., pk_intStudentIddfs is IDENTITY column. The table tbl_audittrail consists of the following fields:

  • pk_intAuditTrailIddfs: Primary key & identity column
  • strTableNamedfs: The name of the table on which user changes the data
  • strPrimaryKeyfielddfs: Name of the primary key field of the changed table. For example, here it is pk_intStudentIddfs
  • intPrimaryKeyValuedfs: The value of the primary key field of the inserted or updated record
  • strActionModedfs: The action made by user on the table i.e. INSERT & UPDATE
  • xmlOldValuedfs: The value of all columns except identity column of the changed table before update as XML. For INSERT action, this field will be null
  • xmlNewValuedfs: The value of all columns except identity column of the changed table after INSERT or UPDATE as XML
  • strUserIddfs: The user id of the user who is changing the table i.e. tbl_student
  • dtDatedfs: Date & time of the change

I have added description text for all non identity columns of tbl_student to show in User Log report for user readability.

  • strStudentNamedfs: “Student Name”
  • strStudentRolldfs: “Student Roll”
  • strStudentClassdfs: “Student Class

I have created four stored procedures for this example:

  • stp_InsertUpdateStudent: This procedure is used for insert or update tbl_student table. This procedure takes 5 parameters:
    1. @intStudentId (int): takes the identity column value of the record of tbl_student table which is going to update. For INSERT, it takes 0 as value.
    2. @strStudentName (varchar): takes value as name of the student.
    3. @strStudentRoll (varchar): takes value as roll of the student.
    4. @strStudentClass (varchar): takes value as class of the student.
    5. @strUserId (varchar): takes the User Id who changes the table.
  • stp_GetXMLValue: This procedure is used to generate XML data from a table.

    This procedure takes 4 parameters (3 inputs & 1 output):

    1. @strTableName (varchar): The name of the table on which user is going to change data.
    2. @strPrimaryKeyField (varchar): The name of the primary key field of the changed table. For example, here it is pk_intStudentIddfs.
    3. @intPrimaryKeyValue (int): The value of the primary key field of the inserted or updated record.
    4. @XMLValue (XML OUTPUT): This variable stores the generated XML.
  • stp_InsertAuditTrail: This procedure is used to insert record in the tbl_audittrail table. This procedure takes 8 parameters:
    1. @strTableName (varchar): The name of the table on which user is going to change data.
    2. @strPrimaryKeyField (varchar): The name of the primary key field of the changed table. For example, here it is pk_intStudentIddfs.
    3. @intPrimaryKeyValue (int): The value of the primary key field of the inserted or updated record.
    4. @strActionName (varchar): takes ‘INSERT’ or ‘UPDATE’.
    5. @strUserId (varchar): takes the User Id who changes the table.
    6. @dtDate (datetime): takes date & time of the change.
    7. @xmlNewValue (xml): takes XML formatted value of all columns except identity column of the chaged table (tbl_student) after insert or update.
    8. @xmlOldValue (xml nullable) (optional): takes XML formatted value of all columns except identity column of the chaged table (tbl_student) before update. For INSERT, this value will be null.
  • stp_GetUserLog: This procedure generates user log report. This procedure takes one parameter:
    1. @intAuditTrailId (int): takes indentity column value of the tbl_audittrail table.

I have called stp_GetXMLValue & stp_InsertAuditTrail procedure within stp_InsertUpdateStudent stored procedure. The code is as follows:

IF @intStudentId=0
BEGIN
	INSERT INTO dbo.tbl_student
			( strStudentNamedfs ,
			  strStudentRolldfs ,
			  strStudentClassdfs
			)
	VALUES  ( @strStudentName,
			  @strStudentRoll,
			  @strStudentClass
			)

	SELECT @IdentityValue=@@IDENTITY

	EXEC dbo.stp_GetXMLValue 'tbl_student',_
	'pk_intStudentIddfs',@IdentityValue,@XMLNewValue OUTPUT

	EXEC dbo.stp_InsertAuditTrail 'tbl_student','pk_intStudentIddfs',_
	@IdentityValue,'NEW',@strUserId,@Date,@XMLNewValue

END
ELSE
BEGIN

	EXEC dbo.stp_GetXMLValue 'tbl_student',_
	'pk_intStudentIddfs',@intStudentId,@XMLOldValue OUTPUT

	UPDATE dbo.tbl_student SET
	strStudentNamedfs=@strStudentName,
	strStudentRolldfs=@strStudentRoll,
	strStudentClassdfs=@strStudentClass
	WHERE pk_intStudentIddfs=@intStudentId

	EXEC dbo.stp_GetXMLValue 'tbl_student',_
	'pk_intStudentIddfs',@intStudentId,@XMLNewValue OUTPUT

	EXEC dbo.stp_InsertAuditTrail 'tbl_student',_
	'pk_intStudentIddfs',@intStudentId,'EDIT',_
			@strUserId,@Date,@XMLNewValue,@XMLOldValue
END

This technique assumes that every table has an identity column at ordinal position 1.

To use this technique elsewhere, just run the script without tbl_student table & stp_InsertUpdateStudent stored procedure.

License

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

About the Author

Sagar Sengupta
Software Developer (Junior) DataFlow System
India India
No Biography provided

Comments and Discussions

 
Questiondeadlock & performance. PinmemberMember 277846028-Nov-11 22:14 
AnswerRe: deadlock & performance. PinmemberSagar Sengupta29-Nov-11 0:55 
GeneralMy vote of 1 PinmemberSelvin25-Nov-11 14:13 
GeneralRe: My vote of 1 PinpremiumPaw Jershauge28-May-14 1:08 
GeneralRe: My vote of 1 PinmemberSelvin28-May-14 1:17 
GeneralRe: My vote of 1 PinpremiumPaw Jershauge28-May-14 1:27 

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.140721.1 | Last Updated 25 Nov 2011
Article Copyright 2011 by Sagar Sengupta
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid