Another approach to auditing involves tracking each change to an entity in a separate audit table. In this recipe, we will show you how to use NHibernate to generate audit triggers for your entity tables.
Download uNHAddIns.dll from the unofficial NHibernate AddIns project at https://bitbucket.org/fabiomaulo/unhaddins. Save the file to your solution's Lib folder.
- Create a new console application project with all standard NHibernate references, the standard NHibernate and log4net configuration, and the
Eg.Coremodel from Chapter 1, The Configuration and Schema. - Add a reference to
uNHAddIns.dll. - Set the
dialecttouNHAddIns.Audit.TriggerGenerator.ExtendedMsSql2008Dialect, uNHAddIns. - Add the following code to the
Mainmethod ofProgram.cs:var cfg = new Configuration().Configure(); var namingStrategy = new NamingStrategy(); var auditColumnSource = new AuditColumnSource(); new TriggerAuditing(cfg, namingStrategy, auditColumnSource).Configure(); var sessionFaculty = cfg.BuildSessionFactory(); var se = new NHibernate.Tool.hbm2ddl.SchemaExport(cfg); se.Execute(true, true, false);
- Build and run your application.
NHibernate has three distinct levels of mapping. First, NHibernate simply deserializes the mapping documents into their equivalent .NET objects. Second, NHibernate transforms these mapping objects into a second, more detailed set of classes named mapping metadata. Finally, NHibernate transforms these detailed classes into the final persisters. We have an opportunity to manipulate this second-level mapping up to the point where we build the session factory.
The uNHAddIns trigger generator code reads the structure of each table from the mapping metadata and constructs a matching audit table and set of triggers.
We can use the standard NamingStrategy or provide our own. When naming audit tables, the default naming strategy simply appends Audit to the name of the data table. For trigger names, it appends _onInsert, _onUpdate, or _onDelete to the data table name.
An implementation of IAuditColumnSource should return a list of AuditColumns to be added to each audit table. For example, to record the current date and time when an entity is changed, we would use this AuditColumn:
new AuditColumn()
{
Name = "AuditTimestamp",
Value = new SimpleValue()
{
TypeName = NHibernateUtil.DateTime.Name
},
IsNullable = false,
IncludeInPrimaryKey = true,
ValueFunction = delegate(TriggerActions action)
{
return "getdate()";
}
};The default implementation returns three audit columns: AuditUser, AuditTimestamp, and AuditOperation. This is sufficient to answer what changed, who changed it, and when. Unfortunately, SQL does not have a handy function to answer why. The trigger generator also defines an IExtendedDialect interface, which adds some additional trigger-related SQL dialect functions to the standard dialects. A Microsoft SQL Server 2008 and SQLite implementation are both included. This recipe uses the ExtendedMsSql2008Dialect.
The TriggerAuditing Configure() method adds the appropriate objects to our second-level mapping to be included in our database schema output from hbm2ddl.
The objects added to our mapping all implement IAuxiliaryDatabaseObject. This interface is used by hbm2ddl to include drop and create SQL statements for database objects outside the scope of NHibernate, such as triggers and non-entity tables. As we will see in the next recipe, these can also be defined using XML mappings.
Because we get the current username from SQL's system_user to get meaningful audit logs using this method, you must use one SQL or Windows account per user when logging into the SQL server. This effectively disables connection pooling, because most connections use different credentials.
In the next recipe, we
will show you how you can use SQL's CONTEXT_INFO as your username source, avoiding the account maintenance overhead and relieving the stress on the connection pool.