SubSonic 3.0.0.3 | Data Access Layer - Audit trails

Question

Hi guys how do you implementing an Audit Trails on all objects/class on SubSonic under Data Access Layer?

If what I want is, all changes on all objects will be recorded on a single table/object.

public class AuditTrail
{
   public int Id { get; set; }
   public string SourceObjectName { get; set; }
   public int RowPK { get; set; } // Id of the SourceObject
   public string ChangeType {get; set;} // value such as "Add", "Update", "Delete"
   public string RowCapture { get; set; } // Id="6" UserId="xxx3" SurName="NoBodyx" FirstName="no3" MiddleName="B." Email="[email protected]" CreatedDate="8/6/2009 1:57:58 PM" CreatedBy="ca3" UpdatedDate="8/7/2009 5:58:37 AM" UpdatedBy="qqq" Name="no3 B. NoBodyx"
   public CreatedDate {get; set;}
}

Answer 1

I handle all of my audit trails directly in the database using triggers. When the audit only happens to changes that come from code, if someone tinkers with the data via direct SQL query or via Management Studio, you get no trail. I've also found that the trigger-based setups perform better than the code-based ones, bogging things down less.

I use a stored procedure that creates an audit table per table and sets up all of the triggers. However, a similar setup could point to a single audit table if you want.

Once the audit table exists, you can pull data from it via Subsonic.