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.

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

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.

CREATETRIGGER Trigger2
ON dbo.Customer
forUPDATE,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:

IF (SELECT COUNT(*) FROM inserted) > 0begin-- This is a update
endelse-- 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.

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 (casewhenupdate(CustomerName)
then deleted.CustomerName
end) as CustomerName,
(casewhenupdate(CustomerAddress)
then deleted.CustomerAddress
end) as CustomerAddress from deleted
innerjoin Customer
on deleted.CustomerId=Customer.CustomerId
FOR XML AUTO)

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.

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.