Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET

Implementing Audit Trail using Trigger

Rate me:
Please Sign up or sign in to vote.
4.33/5 (17 votes)
26 Apr 2009CPOL5 min read 96.3K   1.8K   43   9
Implementing Audit trail using trigger

Table of Contents

Introduction and Goal

There are many ways of doing an audit trail and in one of my previous articles, I had discussed how we can implement audit trail using prototype pattern. 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. 
The data is 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.

Image 1

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, it inserts this new record into the ‘inserted’ logical table.

Image 2

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 into the delete table.

Image 3

Figure: Update and delete in action.

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

Image 4

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

SQL
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 an update event or else it is a delete event. Below is the code snippet for the same:

SQL
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 into 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’. Finally, we can insert this XML data into the audit table.

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

SQL
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 for the final trigger:

SQL
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 the audit table.

Image 5

Displaying the Audit Trail

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

SQL
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 data is displayed back in columns and rows format.

Image 6

Issues with This Implementation

  • 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 high transaction tables, a load test is essential before implementing in live projects.
  • You need to create trigger on every table that 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.

About the Source Code Download

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 that the database was made using SQL Express edition.
To download the source code, click here.

For further reading do watch the below interview preparation videos and step by step video series.

License

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


Written By
Architect https://www.questpond.com
India India

Comments and Discussions

 
SuggestionSQL Table Audit Pin
JackRP125-Jun-12 0:02
JackRP125-Jun-12 0:02 
GeneralMy vote of 5 Pin
sent4ashu4-Dec-11 19:12
sent4ashu4-Dec-11 19:12 
Generalwhere codition missing in SELECT COUNT(*) FROM inserted Pin
anas_beit5-Feb-11 23:52
anas_beit5-Feb-11 23:52 
GeneralSelect OPENXML for all row Pin
Member 225134521-Apr-10 17:20
Member 225134521-Apr-10 17:20 
QuestionSQL 2005 Pin
Paphili2-Jun-09 12:23
Paphili2-Jun-09 12:23 
GeneralGood article Pin
Donsw19-May-09 6:23
Donsw19-May-09 6:23 
GeneralAnother approach Pin
Jon_Boy27-Apr-09 3:32
Jon_Boy27-Apr-09 3:32 
QuestionXML for storing deleted records? Pin
Pavels Mihailovs27-Apr-09 0:21
Pavels Mihailovs27-Apr-09 0:21 
AnswerRe: XML for storing deleted records? Pin
Shivprasad koirala27-Apr-09 0:56
Shivprasad koirala27-Apr-09 0:56 

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.