Implementing Audit trail using trigger

Questpond
Posted by in ASP.NET category on for Advance level | Views : 7510 red flag

There are many way of doing audit trail and in one of my previous article I had discussed how we can implement audit trail using prototype pattern


 Download source code for Implementing Audit trail using trigger

Implementing Audit trail using trigger

 

Introduction and Goal

Fundamentals – inserted and deleted logical table

Our concern is only deleted tables

The Generic Audit table

Simple customer table

Trigger + deleted tables + FORXML

Ready , steady and Action

Source code download
 

 

Introduction and Goal
 

There are many way of doing audit trail and in one of my previous article I had discussed how we can implement audit trail using prototype pattern http://www.dotnetfunda.com/articles/article290.aspx  . In this session we will discuss how we can implement audit trailing using trigger. We will take up a simple customer table, create a simple audit table and then we will write a generic trigger by which we can audit any kind of table structure data in the audit table.

You can download my 400 .NET FAQ book which has everything you need in the .Net world. http://www.questpond.com/SampleDotNetInterviewQuestionBook.zip
data in XML format
 

That’s what we will achieve at the end of this tutorial a simple audit table having auditing data in XML format

 

Fundamentals – inserted and deleted logical table
 

Trigger based audit trails are based on two logical tables ‘inserted’ and ‘deleted’. So we will first go through the fundamentals of these tables and then see the actual implementation.

We will consider 3 different scenarios i.e. insert, update and delete and we will see how these three scenarios affect data in ‘inserted’ and ‘deleted’ tables.

So when an insert SQL is fired on a table its inserts this new record in to the ‘inserted’ logical table.
 

Figure :- Insert in action
 

When an update SQL is fired on a table it inserts the updated new records in the ‘inserted’ logical tables. The old records i.e. records before updating are entered in to the delete table.
 

Figure: - Update and delete in action
 

Any deletes fired on a table inserts the deleted records in the ‘deleted’ logical table.

Figure: - delete in action
 

Our concern is only deleted tables
 

Audit trail is maintained only when the records are updated or deleted. In other words we are only concerned with the deleted table. The ‘deleted’ logical table has records prior to updating or deleting.
 

The Generic Audit table
 

The audit table will have a generic structure to store any kind of audited value. There are three fields from which the ‘AuditValue’ is the most important field. This field will store the old values in XML format. We are using XML format so that we can accommodate any generic table changes.

Field Name

Description

Id

An incremental identity value

AuditValue

This field has the update values in XML format. For example <Table1 Field1="123"/> signifies that table1’s field1 value is changed and the old value before update is ‘123’.

TableName

This field has the table name whose values are changes

 

Simple customer table
 

As a example sake we have taken a simple customer table and we will be implementing audit trail on the same. It’s a simple customer table with three fields as shown below with description.
 

Field Name

Description

CustomerId

An incremental identity value

Customer Name

Name of the customer

Customer Address

Customer address

Trigger + deleted tables + FORXML
 

So we will create a simple trigger on the customer table which will query the deleted tables and store the updated value in audit table.

The first step is to create a trigger for update and delete events.

 

CREATE TRIGGER Trigger2
ON dbo.Customer
for UPDATE,DELETE

The second thing we need to do is check if this is an update or a delete. If you remember the fundamentals of inserted and deleted you will remember that updated data comes in both inserted and deleted table. But deleted data only goes in deleted logical table. So if data is present in ‘inserted’ table that means it’s a update event or else it a delete event. Below is the code snippet for the same.
 

IF (SELECT COUNT(*) FROM inserted) > 0 
begin
-- This is a update
end
else
-- This is a delete
end 

If the record is deleted we can find the same in the deleted table. We can convert the rows in to XML format using the ‘FOR XML’ keyword. So we can just query the deleted table data and convert the same in XML format using ‘FOR XML’. This XML data we can finally insert in to audit table.
 

set @OldMessage = (SELECT CustomerName ,CustomerAddress
from deleted 
FOR XML AUTO)

insert into audit(AuditValue,TableName) values (@OldMessage,'Customer')

If the record is updated we can make an inner join with the customer table and get the updated record from the deleted logical table. We have also used the ‘update’ function to check if the column was updated.
 

set @OldMessage = (SELECT (case
when update(CustomerName)
then deleted.CustomerName
end) as CustomerName,
(case when update(CustomerAddress)
then deleted.CustomerAddress
end) as CustomerAddress from deleted 
inner join Customer
on deleted.CustomerId=Customer.CustomerId 
FOR XML AUTO)

Putting it together the final trigger.
 

ALTER TRIGGER Trigger2
ON dbo.Customer
for UPDATE,DELETE
AS
Declare @OldMessage varchar(200)

IF (SELECT COUNT(*) FROM inserted) > 0 
begin
set @OldMessage = (SELECT 
(case
when update(CustomerName)
then deleted.CustomerName
end) as CustomerName,
(case when update(CustomerAddress)
then deleted.CustomerAddress
end) as CustomerAddress from deleted 
inner join Customer
on deleted.CustomerId=Customer.CustomerId 
FOR XML AUTO)
end
else
begin
set @OldMessage = (SELECT CustomerName ,CustomerAddress
from deleted 
FOR XML AUTO)
end

insert into audit(AuditValue,TableName) values (@OldMessage,'Customer')

Ready , steady and Action
 

I have updated the data in customer table randomly and you can see how the audit trail is recorded in XML format in audit table.
 

Displaying the Audit trail
 

If you want to reverse display the data we can use the ‘OPENXML’ keyword.
 

DECLARE @idoc int
DECLARE @doc varchar(1000)

select @doc=AuditValue from audit where id=60

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT * FROM OPENXML (@idoc, '/deleted',0) WITH (CustomerName varchar(50), CustomerAddress varchar(50))

EXEC sp_xml_removedocument @idoc

Below is a simple display of how the audit is data is displayed back in columns and rows format. 


• Due to XML the size can be very huge. Some kind of compression mechanism can really help out.
• There can be performance hits as an extra trigger needs to be fired. But according to our stress test it’s very minimal. In case of highly transaction tables a load test is essential before implementing in live projects.
• You need to create trigger on every table which you want to audit.
• You still need to undergo some kind of manual process to do auditing i.e. creating triggers, putting table names etc. In other words it is not a generic solution.
 

We have attached the complete MDF file which has the customer table, audit table and the trigger which we discussed above. We have attached both the MDF and LDF. Please note the database was made using SQL Express edition.
Get the source code at the top of this article.

Page copy protected against web site content infringement by Copyscape

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)