Audit trail design

Question

Currently working on ASP.Net MVC 4 application. I have been asked to log all the changes by the user in the application. Its nothing but Audit Trail

Here is my controller code

 public ActionResult SaveBasicInfo(PatientBasicInfoViewModel basicInfoViewModel)
    {

        if (ModelState.IsValid)
        {
            var loggedInUserPersonId = ((User)Session["CurrentUser"]).PersonId;
            long tenantId = TenantContext.TenantId;
            var patient = Mapper.Map<PatientBasicInfoViewModel,
                                    Patient>(basicInfoViewModel);
            patient.TenantId = tenantId;

            if (patient.PatientId > 0)
            {
                patient.UpdatedBy = loggedInUserPersonId;
                patient.UpdatedDateTime = DateTime.Now;
                patientService.UpdatePatientDetails(patient);                  
                //Here need to do Audit Log 
                //Current User modified this fields values from `xx` to `yy` on               
            }
            else
            {
                patient.AddedBy = loggedInUserPersonId;
                patientService.AddPatient(patient);
               // Here need to Log, This user created this patient record on Date
            }


          return RedirectToAction("Details", new { id = patient.PatientId });
        }

        return RedirectToAction("Details");
    }

Could some one help me to design the table structure for this which is flexible for all the combination.

Also How do i get both the old and new values to be logged?

Also referred Ideas on database design for capturing audit trails, but didn't get :(

Alternative Solution: creating a shadow/history table for each table that needs audit and use database Trigger. explained here.

Problem with this is , how i know this column value was changed from this to that by this user? I need to show all the history by user and also for a record

Note: We are not using Entity Framework, we use simple stored procedure and ADO.Net and use POCO classes

Help me to choose the best one from below referred here

A Separate "History" Table for Each Table Being Audited
A Consolidated "History" Table for All Tables Whose Changes are Being Tracked

http://stackoverflow.com/questions/1051449/ideas-on-database-design-for-capturing-audit-trails?rq=1 duplicate. — Neville Kuyt, Oct 09 '13 at 13:52
I suggest you learn some real SQL and create some triggers against the db to automagically fire off the logging on change — Jason, Oct 09 '13 at 14:32
@Jason, writing trigger will impact the performance? I often hear `trigger` is not good — Billa, Oct 09 '13 at 14:49
@Billa use the right tool for the job, it's like saying using a thread synchronization construct, e.g. `lock`, will impact performance, sure it will, but what's your other choice? Don't do multi-threading :) — Jason, Oct 09 '13 at 15:34
@billa, logging every database interaction is going to give you much worse performance than a well designed trigger. — Liam, Oct 09 '13 at 16:27

score 1 · Answer 1 · answered Oct 10 '13 at 15:38

in Ado.NET

You can Use DataTable to Retrieve your data ,

You can get the complete change set of modified rows on a datatable using the following

 DataTable ChangedDataTable= yourdataTable.GetChanges(DataRowState.Modified);

then you can use the DataRowVersion to get the Original and Current Versions for each Modified Row

DataRowObject[0, DataRowVersion.Original] //by Column index
DataRowObject["ColumnName", DataRowVersion.Original] //by column name

i dont use Data Table. I use SQL Reader, read and prepare objects — Billa, Oct 11 '13 at 14:12

score 0 · Answer 2 · answered Oct 09 '13 at 16:23

1- Override the SaveChanges Method in your Context Class

2-

     public override int SaveChanges()
        {
            var autoDetectChanges = Configuration.AutoDetectChangesEnabled;

            try
            {
                Configuration.AutoDetectChangesEnabled = false;
                ChangeTracker.DetectChanges();

                foreach (var entry in ChangeTracker.Entries().Where(e => e.State == EntityState.Added || e.State == EntityState.Modified))
                {
                   // Save entry
// here you can get the Original Values or Current Values
entry.CurrentValues 
entry.OriginalValues
                }

                ChangeTracker.DetectChanges();

                Configuration.ValidateOnSaveEnabled = false;

                return base.SaveChanges();
            }
            finally
            {
                Configuration.AutoDetectChangesEnabled = autoDetectChanges;
            }
        }

The second DetectChanges call is pointless, the SaveChanges method by default calls the DetectChanges method on its own. http://msdn.microsoft.com/en-us/library/vstudio/dd456854%28v=vs.100%29.aspx — matt., Oct 09 '13 at 21:59
if you are using POCOs then POCO entity is not tracked by the ObjectContext. The DetectChanges method is called to synchronize the ObjectStateManager with the current state of the objects. http://msdn.microsoft.com/en-us/library/vstudio/dd456854(v=vs.100).aspx — Mohamed Gaafar, Oct 10 '13 at 11:05

Audit trail design

2 Answers2