Table of Contents
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.
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.
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.
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.
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’. |
TableName | This field has the table name whose values are changes. |
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 |
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 an update event or else it is a delete event. Below is the code snippet for the same:
IF (SELECT COUNT(*) FROM inserted) > 0
begin
end
else
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.
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 for 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')
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.
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 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 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.
For further reading do watch the below interview preparation videos and step by step video series.
