Click here to Skip to main content
11,702,168 members (69,814 online)
Click here to Skip to main content

Tagged as

Changelog for domain objects

, 3 May 2010 CPOL 4.7K 6
Rate this:
Please Sign up or sign in to vote.
In this post, I would like to present you a simple way to implement changelog system for database objects using NHibernate session interceptor and database triggers.

In this post, I would like to present you a simple way to implement changelog system for database objects using NHibernate session interceptor and database triggers. This system will inform which rows were created, deleted and which columns were updated and what were their previous values.

Let's imagine we have the UNIVERSE database that is used by astronomers all around the world to store discovered planets' records. Let's then think that they want to know what changes have been made to the planets' descriptions and who made them (to eventually ask him for proofs). Our simple database will have only two tables:

Planet table stores basic information about discovered planets and PlanetChangeLog table stores all changes that has been made to the Planet table. The easiest way to fill PlanetChangeLog is to use triggers. A problem that arises is how to fill the Modifier column - in the database, we often don't know who is logged to the web application (the database logged user is usually the application itself). So in the trigger code, we need to use a column ModifiedBy from the modified row:

create trigger planet_insupdttrg on Planet 
for update
as
begin
 set nocount on
 
 if (update(PlanetName))
  insert into PlanetChangeLog
       (PlanetId
       ,ChangedColumn
       ,OldVal
       ,NewVal
       ,Modifier
       ,ModificationDate)
    select ins.PlanetId
     ,'PlanetName'
     ,del.PlanetName
     ,ins.PlanetName
     ,ins.ModifiedBy
     ,ins.ModifyDate
    from inserted ins
    inner join deleted del on ins.PlanetId = del.PlanetId
 if (update(MeanRadius))
  insert into PlanetChangeLog
       (PlanetId
       ,ChangedColumn
       ,OldVal
       ,NewVal
       ,Modifier
       ,ModificationDate)
    select ins.PlanetId
     ,'MeanRadius'
     ,del.MeanRadius
     ,ins.MeanRadius
     ,ins.ModifiedBy
     ,ins.ModifyDate
    from inserted ins
    inner join deleted del on ins.PlanetId = del.PlanetId
 if (update(Volume))
  insert into PlanetChangeLog
       (PlanetId
       ,ChangedColumn
       ,OldVal
       ,NewVal
       ,Modifier
       ,ModificationDate)
    select ins.PlanetId
     ,'Volume'
     ,del.Volume
     ,ins.Volume
     ,ins.ModifiedBy
     ,ins.ModifyDate
    from inserted ins
    inner join deleted del on ins.PlanetId = del.PlanetId
 if (update(Mass))
  insert into PlanetChangeLog
       (PlanetId
       ,ChangedColumn
       ,OldVal
       ,NewVal
       ,Modifier
       ,ModificationDate)
    select ins.PlanetId
     ,'Mass'
     ,del.Mass
     ,ins.Mass
     ,ins.ModifiedBy
     ,ins.ModifyDate
    from inserted ins
    inner join deleted del on ins.PlanetId = del.PlanetId
 set nocount off
end

Now, in the application code we must fill correctly ModifiedBy and CreatedBy column. Let's first look at the object definition:

public class Planet : IAuditable
{
    public virtual Int32 Id { get; protected set; }

    public virtual String Name { get; set; }
    public virtual Double MeanRadius { get; set; }
    public virtual Double Volume { get; set; }
    public virtual Double Mass { get; set; }

    public virtual String CreatedBy { get; set; }
    public virtual DateTime CreateDate { get; set; }
    public virtual String ModifiedBy { get; set; }
    public virtual DateTime ModifyDate { get; set; }
}

and its mapping file:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="AuditWithNH"
                   namespace="AuditWithNH">
  <class name="Planet" table="Planet" mutable="true" dynamic-update="true">
    <id name="Id" column="PlanetId" type="Int32" unsaved-value="0">
      <generator class="native" />
    </id>

    <!--<span class="code-comment"> Planet characteristics --></span>
    <property name="Name" column="PlanetName" type="String" 
		length="256" not-null="true" />
    <property name="MeanRadius" column="MeanRadius" type="Double" not-null="true" />
    <property name="Volume" column="Volume" type="Double" not-null="true" />
    <property name="Mass" column="Mass" type="Double" not-null="true" />

    <!--<span class="code-comment"> Changelog --></span>
    <property name="ModifiedBy" column="ModifiedBy" type="String" 
		length="30" not-null="true" />
    <property name="ModifyDate" column="ModifyDate" type="DateTime" not-null="true" />
    <property name="CreatedBy" column="CreatedBy" type="String" 
		length="30" not-null="true" />
    <property name="CreateDate" column="CreateDate" type="DateTime" not-null="true" />
  </class>
</hibernate-mapping>

You may notice that the Planet class implements IAuditable interface. This interface informs NHibernate session interceptor that this object has changelog fields that need to be filled:

using System;

namespace TnE.Shared.Audit
{
    /// <span class="code-SummaryComment"><summary>
</span>

Finally, let's define the AuditSessionInterceptor. As stated before, this interceptor will check whether object (being persisted) implements IAuditable interface and if it does, the interceptor will fill the changelog fields. In the code below, I assume that LoggedUser property of the interceptor will be set after successful user authentication by authentication module. Usually NHibernate session (so also the session interceptor) has the same lifetime as the HTTP request so each user will have its own session interceptor - so the LoggedProperty may be set once and will be valid till the finalization of the interceptor. However, if you implement different NHibernate session management, you may want to retrieve the logged user name in each of the interceptor methods.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using NHibernate;

namespace AuditWithNH
{
    /// <span class="code-SummaryComment"><summary>
</span>

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sebastian Solnica
Software Developer (Senior)
Poland Poland
Interested in tracing, debugging and performance tuning of the .NET applications (especially ASP.NET).

My twitter: @lowleveldesign
My blog: http://lowleveldesign.wordpress.com

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150819.1 | Last Updated 3 May 2010
Article Copyright 2010 by Sebastian Solnica
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid