Logging Changes

Logging Changes

Old forum URL: forums.lhotka.net/forums/t/10385.aspx

MadGerbil posted on Wednesday, May 18, 2011

I've brought up this topic several times in the past, and for that I beg your pardon.   I'm starting over in WPF and CSLA 4.0.1 and I'm enjoying the journey but now the time has come to re-architect a couple of items that have proved problematic.  One of these items is the horrible way I handled logs in the past.  I'd like to do the right thing moving forward.

Generally in my applications a user opens a record.  Each record will contain the name of a client and numerous charges.  Each charge has a sub-collection of payments.   Because multiple users can access each record it's important to log changes so that it's clear who did what to the record and when.  I've solved this in the past by creating a "Logs" collection.  Each time something would change in the parent object or any of it's children a new Log item would be created and added to the collection. 

A log would generally look like this: 10/10/2011 9:45 AM User Tacobob changed the First Name value from 'Gates' to 'Jobs'.

I've tried so many different and ultimately brittle things.  I've bubbled changes to child objects all the way up to the parent who then created the log and deposited into the parent's log collection.  I've tried added log collections to each individual object and had each object manage it's own.  I've altered CSLA business objects to accomplish some of this - the stuff works but it is a nightmare.   Much of this was accomplished inside each property so that the old and new value was available.  Obviously, dependency properties are not where I want to be adding additional code.

I'm thinking about just creating the log notes in the stored procedures (New, Update, Delete) for each object type.   That is, when a new record is created write a copy to the object's log table.  When the record is updated, write a copy of the update to the object's log table...and so on.  This way I don't have to touch the code and I could possibly show the entire history of the object is a nice pop-up window if the user requested it.   The downside is I'd be storing lots of extra data but then storage is cheap on the tiny systems I write anyways.




edore replied on Wednesday, May 18, 2011


Personnally I don't like to include lot's of logic in stored procedures.  In fact, the only stored procedures we use are the ones requiring max performance.  When it comes to handle the kind of scenarios you are talking about, I strongly advise you to stay in the "world of CSLA and OOP".  And moreover, use a unit testing approach and you won't be overwhelmed by classes too complex to maintain and use and you'll be sure of the result.

That said, we faced the same challenge on our main project and developed some pretty helpful classes, one of which is called "StampBase".  I'll provide the main concepts so you'll see the idea behind.  Here's StampBase's signature :

        public abstract class StampBase<TStampEntity, TParentEntity> : BusinessBase<TStampEntity>, 
            where TStampEntity : StampBase<TStampEntity, TParentEntity>
            where TParentEntity : BusinessBase<TParentEntity>

StampBase has some properties like : User, StampDate, Description, LastChanged, StampType (an enum with Created, Modified, etc... values) and ParentEntityId

        public static PropertyInfo<TEntityId> ParentEntityIdProperty =
            RegisterProperty<TEntityId>(o => o.ParentEntityId, StampResources.ParentEntityId);
        public abstract TEntityId ParentEntityId { getset; }

sub classes may look like this

public class CustomerStamp : StampBase<CustomerStamp Customer>

        public static PropertyInfo<Int32> CustomerIdProperty =
            RegisterProperty<Int32>(o => o.CustomerId);
        public Int32 CustomerId
            get { return GetProperty(CustomerIdProperty); }
            set { SetProperty(CustomerIdProperty, value); }
        #region StampBase overrides
        public override Int32 ParentEntityId
            get { return CustomerId; }
            set { CustomerId= value; }
So you will find a StampBase sub class for each object that is "stampable", or if you prefer, all business object implementing IStampable.
IStampable exposes the properties and methods required on the parent, including the StampList.

    public static PropertyInfo<StampList<CustomerStampCustomer>> StampListProperty =

            RegisterProperty<StampList<CustomerStampCustomer>>(o => o.StampList, CustomerResources.StampList);         public StampList<CustomerStampCustomer> StampList { get             {                 EntityLoader.LoadStampList<CustomerCustomerStamp>(this, StampListProperty, (Int32)StampParentEntity.Customer);                 return GetProperty(StampListProperty);             } }

Finally, we centralized in an ObjectFactory derived class the standard stamps persistance related management

using the business object's metastate.  For example, if the business object implements IStampable and IsNew == true,

then we can add a Stamp with StampType = Created.  The object's is serialized as an XML document in order to persist

an image of each properties values.  When it is modified, you can easily imagine what we're doing : serialize the object again. 

Then you can have an object who can compare 2 images taken from the object's StampList and highlight the differences.

Hope this helps!




MadGerbil replied on Wednesday, May 18, 2011

Interesting solution.    I see several ideas in there that I really like.

Couple Questions:

1: When creating new Stamp objects I assume these are created when the parent object is saved to the database?  That is, in the 'insert' operation you'd check to see if the object is stampable and if it is new (new gets checked before getting there, but whatever) and if so insert a stamp object into the database?  I assume you do that with updates/deletes as well?

2: Do all stamps get stored in the same table?

edore replied on Wednesday, May 18, 2011

1. You're right, we handle this in the direct ObjectFactory sub class.  Then, if for a given scenario we need to log a specific event, (for example if an object is set to inactive), then the specific object factory is responsible of detecting the state and adding the stamp.  Let's say we have some classes like :

public abstract class ObjectFactoryBase<TEntity> : ObjectFactory

  where TEntity : BusinessBase<TEntity>


public virtual TEntity Update(TEntity entity)


  // handle standard stamps here

  // persist entity + it's children




public abstract class CustomerFactory : ObjectFactoryBase <Customer>


public override Customer Update(Customer entity)


  // handle specific stamps here

  return base.Update(entity);





Yes we store them in only one table, with a FK per parent business object.  With appropriate indexes it's not a big issue.  We currently have over 4.5 million stamps in the production DB.  But still, it's entirely a DAL based decision.  You could store them in different tables if you prefer to.


MadGerbil replied on Monday, May 23, 2011


I've been thinking more about your solution and I'm under the impression that it's a bit more complex than it needs to be.   My problem is that I'm not seeing the benefit to all the inheritence and generics that you use.  I'm not saying there isn't a benefit - I likely don't have the experience to see it.

For example, if I had a "stamp" object that was comprised of these properties:

ParentId, LastUpdated, User, XML(XML representation of object)

Why wouldn't I just created a lazy loading collection of these "stamp" objects on each object for which I desire a "stamp"?   Every time a record is inserted/updated/deleted the appropriate stamp is created in the database.  It seems to me that I could get the same effect without all the additional complexity.

It seems to me with a simplier approach all of the benefits are realized in addition to keeping things much more readable.   


JonnyBee replied on Wednesday, May 18, 2011


Which databases do you need to support? 

I have done this using  insert/update triggers in SQL server many years ago using T-Sql - but not so trivial code.

A couple of alternatives:

  1. SQL Server 2008 has the build in function "Change Data Capture". No code needed in your app. 
  2. You could use the CustomFieldData to keep Original/New value of a field and create a generic change logger to your own data table.

The CustomFieldData approach will move the existing value to original value the first time a field is changed and will not generate a lot of overhead in memory. The drawback is that this will only work for "managed properties" and not for private properties (like NonSerializable properties or intergraph references).

I would not recommend to create new/update/delete procedures for this - if you are going to do this in the database then use builtin functions or triggers.

Jon Galloway posted a generic audit trigger in T-SQL:

You could also write a trigger in C#/VB.NET to run in the SqlServer CLR:

vschaak replied on Wednesday, May 25, 2011

Hi MadGerbil,

I second JonnyBee's approach of using DB-functionality for the job.
The main reason for that is answering the following question:

Do you intend to log data changes at all or do you intend to log data changes only made by users via your business objects?

By logging build on triggers you capture every(!) data change and not only those made through your application objects. In so far your description "users open a record" is only half of the truth, since they retrieve/fetch data for business objects which might represent, but not ARE your data!
So it's always possible for other apps (even if it's 'only' SQL-Server Management console!) to access same data or SQL-Commands like UPDATE or DELETE...
Another aspect, altough it seems a little outdated, watching todays discussions, is to reduce traffic between layers and tiers. By relaying on DB-triggers no additional bit is transported over the wire, which at least to me (a little outdated as stated before ;-)) sounds good.

Best wishes


MadGerbil replied on Wednesday, May 25, 2011


I think your point about capturing all data changes is superb.  

That said, I prefer the object approach for the simple reason that I'd rather work with objects than with a long database script.  That said, I don't want to use an object approach that complicates my objects by quite a bit.

In short, you guys have managed to put some good stuff on the table and now I have to kick it around for a bit.  :D

JonnyBee replied on Wednesday, May 25, 2011

If you look at the links in my previous reply you can run one SQL script that will add a generic trigger to all your tables.

Jon Galloway posted a generic audit trigger in T-SQL:

and this is in turn based on this article:


MadGerbil replied on Wednesday, May 25, 2011

I think both of the proposed solutions are actually solving slightly different problems.

Problem #1: The need to record any changes to the data so that in the event of errors/fraud/etc. it is possible to determine who did what and when.

Problem #2: Presenting the various versions of the data in a way that is useful to the end user (and not necessarily the DB guy).

I'll refer to the two solutions as the "Database Solution" and the "Object Solution".

The Database Solution is obviously the superior choice when it comes to capturing any/all changes to database records.  I think this is an IT favorite because of the ease of implementation and the completeness of the records kept.   If that were the only requirement of the project there would be no contest.  However, I think the Database Solution begins to fall apart when it comes to actually presenting the data to the users.   From my perspective, a developer would still be stuck creating a multitude of objects to make an object history presentable to an end user (no time saved there).   Consider the presentation of an enumeration value which would be stored in the database as an integer but would need to be presented in a history form (or grid) as a string - in short, it appears as if it would be necessary to create an entire object library just to handle the presentation portion so it doesn't really appear to save any time on that front.   It also isn't clear to me how well non-string values would be handled.  (Old/New values in the log table would have to handle a variety of types or be converted)

The Object Solution I believe is the superior choice when it comes to presentation.  Obviously, if the solution allowed for changes to the tables to be made without using the built in logging on an object it would be possible for non-logged changes to be made - which is a significant disadvantage.

So it appears to me the two solutions are emphasizing different aspects of the logging process.   One focuses primarily on user presentation and the other focuses on capturing absolutely everything.



vschaak replied on Thursday, May 26, 2011

Hi MadGerbil,

in a certain way, you're absolutely right. I come from a database background and thereby I judge data in another way than some OO guy's, that's right. An actual project of mine is to migrate data used by one app into data used by another app without loosing too much. There the old app is of no  further worth for the customer, but data stored via the app definitly is!
And yes, capturing data-logs in a way users can really work with, couldn't be achived with no cost, be it via the db approach or the OO approach.
Reflecting my background, you can imagine that I dislike the idea of having data only stored in enums in the app and not the DB. And as soon you've got the data stored in the DB you'll be able to JOIN data from several tables together to get a user-readable result. This applies to the INSERTED and DELETED tables in a trigger, to.
Let's think of a typical 'link-relation', where a n:m relation is resolved. Those tables often consist of two ID-values and maybe some status-info. Presenting the user that the relation between Employee #3 and department #8 has been deleted is indeed a little challenging, to say at least. There you'll probably join to the user and the department-table and there you'll have the user-readable values, that can be presented!

If needed I can go into that deeper and provide an example. Just tell me.

Best wishes and good luck


Copyright (c) Marimer LLC