How to Implement Audit Log Table

I'm using LightSpeed 5.5 I want to know how to create an Audit Log Table where I can track all change history of an entity (update,delete,insert).

For example I have entity "Customer" I want also to have "CustomerAudit" which will contain all old values for the updated or the deleted rows. Soft delete would work but only for delete and I want to maintain the updated old values.

Hi Ahmed,

To achieve this you'll need to write your own code around LightSpeed. There are however some properties you can use to help with this. For example, to see what values are changing, check out LightSpeed Change Tracking: http://www.mindscapehq.com/documentation/lightspeed/Implementing-Storage-Policies-with-LightSpeed/Change-Tracking

Change Tracking will give you the original value AND the new value. So you could check that on save and write the two values.

I'd then personally look at creating your own UnitOfWork class that inherits off the generated one. You could then inspect the entities that are being changed when saving changes. Note: There are also some events on the entities themselves, but it would likely be easiest to do this with the UnitOfWork.

Obviously you'll want to manage your CustomerAudit table outside of this life cycle (so you don't, for example, save a new row to it, triggering an audit that a row was saved, triggering an audit that a row was saved...etc etc :-). You could do this either by checking the types of entities if you wanted an entity for CustomerAudit, or just direct SQL if you preferred (you can access the direct underlying ADO connection object from the Unit Of Work also).

I hope this helps. As mentioned, LightSpeed won't do all of this for you out of the box, but there are events and properties to help you build your system. Let me know how you get on!

John-Daniel Trask

Hi john,

thank's for help now i can catch all entities being inserted/updated/deleted, however when updating an entity if i'm not using Change Tracker to get the modified fields and their values how to detect the Columns /Attributes being updated and get their values dynamically (without using change tracker)

Hi,

You need to use the change tracker. That's the only way to publicly see the changed fields.

Kind regards,

John-Daniel Trask

Hi,

Another way you may be able to do this is to create an abstract base entity in the LightSpeed model, and then any Entities I would like to have auditing enabled on, I inherit from the BaseEntity.

The Base Entity is just a normal LS Entity on the model with the Property "Inheritance" set to "Abstract"

Then any Entity that you would like to enable auditing on, set their Property "Base Class" to your "BaseEntity", create a partial class for the BaseEntity, perhaps like this...

    partial class BaseEntity
    {
        protected BaseEntity()
        {
            ChangeTracker.TrackingMode = ChangeTrackingMode.ChangesOnly;
        }

        protected BaseEntity(bool autoInitialize) : base(autoInitialize)
        {
            // Starts recording changes to entity, pass in the unit of work that will be used to record the audit log on
            ChangeTracker.TrackingMode = ChangeTrackingMode.ChangesOnly;
        }
    }

And maybe override the SaveChanges in your UoW like this...

public partial class YourModelUnitOfWork
{
    public override void SaveChanges(bool reset)
    {
        var pendingChanges = this.Where(e => e.EntityState != EntityState.Default && e.ChangeTracker.TrackingMode == ChangeTrackingMode.ChangesOnly).ToList();
        if (pendingChanges.Any())
        {
            foreach (var pendingChange in pendingChanges)
            {
                var actionType = AuditLog.TypeAction.Update;
                switch (pendingChange.EntityState)
                {
                    case EntityState.Modified:
                        actionType = AuditLog.TypeAction.Update;
                        break;
                    case EntityState.New:
                        actionType = AuditLog.TypeAction.Create;
                        break;
                    case EntityState.Deleted:
                        actionType = AuditLog.TypeAction.Delete;
                        break;
                }
                // Create your AuditLog Entity Here - eg
                // var newAudit = new AuditLog{ DataModel = pendingChange.GetType().ToString() };
                // newAudit.Record(AuditLog.TypeArea.Details, actionType, pendingChange.ChangeTracker.Changes);
                // Add(newAudit);
            }

        }
        base.SaveChanges(reset);
    }

}

This code isn't tested - but it could do what you need quite easily :)

Cheers, Sean