In my previous post I looked at using Castle ActiveRecord and class hierarchies & inheritance to automate some tasks related to implementing audit trails in your application. The foundation was laid to ensure some required information, namely the current user and date and time of editing data, was seamlessly integrated into the domain logic. In this post I'd like to show the next step in this process - that of implementing triggers and the tables required to store this audit trail information.
Download - Darkside.AuditTrail.Part2.zip
To start off, lets take a look at the database structure created by ActiveRecord from the sample in the previous post.
You'll notice that the fields in the base class ObjectBase<T> are included in every table. Using the Generator Studio application (shameless plug), I pointed to this database and these three tables, and selected two of the templates included with the default installation, AuditTablesTemplate_Guid and AuditTriggersTemplate_Guid. After generating the files - there are 6 scripts generated altogether, 3 for the audit tables, and 3 for the triggers - I ran the scripts in SQL. The resulting tables look almost exactly the same as the tables above, barring 1 extra field which is added to be a surrogate primary key.
The scripts for the triggers look like something like this:
Expand Code
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tr_Customer_Audit]'))
DROP TRIGGER [dbo].[tr_Customer_Audit]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_Customer_Audit] ON [dbo].[Customer] FOR INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM inserted)
BEGIN
INSERT INTO
Customer_Audit (
Customer_Audit_Id,
[CustomerId],
[IsDeleted],
[EditedById],
[EditedDateTime],
[FirstName],
[LastName],
[Age]
)
SELECT
newid(),
inserted.[CustomerId],
inserted.[IsDeleted],
inserted.[EditedById],
inserted.[EditedDateTime],
inserted.[FirstName],
inserted.[LastName],
inserted.[Age]
FROM
inserted
END
Very simply, the triggers select information from the inserted table and insert it into the audit table. Because the code updated the EditedById and EditedDateTime fields, the audit information always has the correct data in the fields.
The resulting data in the xxxx_Audit tables will be a line-by-line history of the inserts and updates made to the corresponding tables.