Can you recommend a way to integrate Hibernate with an audit trail? I mean, how can application software cooperate with Hibernate, to maintain a historical record of changes to the database?

One possible solution (which I'm considering) is for Hibernate to call application code shortly before it attempts to execute SQL that inserts, update or deletes anything. For instance, the application would implement net.sf.hibernate.Interceptor and take appropriate action when Hibernate calls Interceptor.onFlushDirty. The application would also need to know when Session.delete(String query ...) is about to attempt a SQL DELETE; is there a similar callback for that purpose? Are there other cases wherein Hibernate changes the database? Can Hibernate call application code first, in each case?

To place this in context, assume these requirements: for certain tables, whenever a row is changed (inserted, updated or deleted), the same database transaction must also insert rows into another 'audit trail' table. Each row of the audit trail must contain a copy of the pre-change values of most fields, the post-change values, the identity of the user who initiated the change and a timestamp. Several applications change the database, including some that are not Hibernate-based.

To fulfill these requirements, my team defines database triggers that insert rows into the audit trail. Pre-change and/or post-change values are supplied to the trigger by the database. The trigger gets the user's identity and timestamp by querying a 'perpetrator' table, which relates those values to each database client identifier. Database clients (running application software) must insert their data into the perpetrator table before they attempt changes to audited data. In the case of a Hibernate-based application, we hope Hibernate can call our application code at the right time to insert data into the perpetrator table. It would be best (for performance) not to insert anything into the perpetrator table in a transaction that doesn't change anything.

(I hear you cry, "that's horribly expensive!" You're right; a significant fraction of a system's capacity must be expended to maintain such an audit trail. Some commercial users are willing to pay the price.)

There was some discussion of this months ago <http://forum.hibernate.org/old//910417.html>.

hi,

I think I have a similar problem: I would like to get this dreamy feature out of hibernate : a multi-server synch framework

to get this, I would create a log of all the modifications of persistent objects and collections, recording say:

(a) object id,
(b) a set of properties common to all my persistent classes,
(c) and the update hql queries launched

This would give me the solution, and also a base for audit trails, and as I log the hql, it would synch serves working on
different db types, even.

By creating my own interceptor, I get that onSave and onFlushDirty I can log (a), (b); the only way I got to
access (c) is by either

- changing a few core hibernate classes, but this is clearly not the right way

or

- implement an appender wich retrieves logs and builds (c), but this seems frail as it depends on Hibernate sources have log.debug calls, and even from their syntax as in:

private static void logQuery(String hql, String sql) {
if ( log.isDebugEnabled() ) {
log.debug("HQL: " + hql);
log.debug("SQL: " + sql);
}
}

a completely different way would be to use Hibernate mappings as base to generate xml versions of persistent objects, but this would have to be hand mantained across versions.
of course I am for the moment ignoring problems like rollback of the hqls.

any ideas ?

Session.delete doesn't execute SQL to 'mass delete' directly, I see (from reading the code); it executes the query, loads each object and passes it to delete(Object), which calls Interceptor.onDelete (indirectly).

Similarly, Interceptor.onSave is called before inserting rows into the database, at least in some cases.

So, is Interceptor sufficient? Or is there a better interface for auditing?