I, in my past application i audit record changes in db in this way:
for every table thats need it i add 5 fields
- RowID
- UserId
- CreateDate
- ChangeDate
- Deleted
and add a view that filter Deleted = false and output only standard fields
All delete and update operation were made by store procedure thats manage correctly those fields: every update or delete become a set of deleted flags and and a creation of a new record (for update only)
In this way i can simply access to table to see who and when make a changes and easy rollback the record deleting the actual end setting deleted = false to the desired one
In my new application i need something similiar but i plan to use Nhibernate and so i will try to avoid stored procedure, there is other way to do it? Possibily without trigger
I read How to efficiently version records in an SQL database but don't like much the idea od have a duplicate table: confronting existing record whith old seem and roll back more difficult.
I find Envers but it too create a duplicate table
EDIT
Thanks to Cole W suggestion i have implemented NH events listener is this way:
public class AuditEventListener: DefaultDeleteEventListener, IPreUpdateEventListener, IDeleteEventListener
{
protected override void DeleteEntity(IEventSource session, object entity, EntityEntry entityEntry, bool isCascadeDeleteEnabled, IEntityPersister persister, ISet transientEntities)
{
if (entity is LoggedEntity)
{
var e = (LoggedEntity)entity;
e.Annullato = true;
e.DataModifica = DateTime.Now;
CascadeBeforeDelete(session, persister, entity, entityEntry, transientEntities);
CascadeAfterDelete(session, persister, entityEntry, transientEntities);
}
else
{
base.DeleteEntity(session, entity, entityEntry, isCascadeDeleteEnabled, persister, transientEntities);
}
}
public bool OnPreUpdate(PreUpdateEvent eventItem)
{
if (eventItem.Entity is LoggedEntity)
{
DateTime dataModifica = DateTime.Now;
store(eventItem.Persister, eventItem.State, "modified_date", dataModifica);
return false;
}
else
return false;
}
private void store(IEntityPersister persister, object[] state, string property_name, object value)
{
int index = Array.IndexOf(persister.PropertyNames, property_name);
if (index == -1)
{
return;
}
state[index] = value;
}
}
this is the configuration in fluent way:
FluentConfiguration configuration = Fluently.Configure()
.Database(config)
.Mappings(m =>
{
m.FluentMappings
.AddFromAssemblyOf<Class1Map>();
m.AutoMappings.Add(
AutoMap.AssemblyOf<Class1>(new NHAutoMapConfiguration()));
})
.ExposeConfiguration(cfg =>
{
cfg.EventListeners.DeleteEventListeners =
new DefaultDeleteEventListener[] { new AuditEventListener() };
cfg.EventListeners.PreUpdateEventListeners =
new IPreUpdateEventListener[] { new AuditEventListener() };
});
the only thing I do not like is the way to define which objects need to be logged in: I can not use an interface because I would be forced to redefine the properties in all of the objects involved, so I created a base object that interested extend, but in this way the properties become visible on the outside of my domain (I can not make it private or internal because NHibernate in the Data access layer must have access).
I think in the end the choice is between this road with this problem rather than envers that would leave clean my domain objects but would create additional tables in the db I think in the end the choice is between this road with this problem rather than envers that would leave me clean my items but would create additional tables in the db
