Audit Trail using XML column in SQL Server 2005

• Download script file - 2.67 KB

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:

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.