Jeff, I have been able to get the solution to work using: - Code generation of Historical Classes using Freemarker - Hibernate Filters within the Historical Classes to apply the 'as-at' date - A Hibernate Event Listener to capture inserts, updates and deletes - An AOP interceptor to populate the historical DB - Hibernate event Listeners to automatically apply the filters when lazy loading historical entities (not essential but nice to have as it ensures the filters are on before loading lazy-loaded properties) - AND (crucially) small changes to two classes - QueryParameters (to fix the way filters were implemented by removing assumptions about the order filters appear in the HQL) and EntityJoinWalker (to add in the filters when lazily loading entities). Essentially filters are NOT applied when lazily loading entities - (there is the point of view that this is a bug....as it means you get different outcomes depending on whether you allow the historical entities to be all loaded at once or by lazy initialization). It is this issue that I fixed with my Hibernate changes. As you can guess this was a few weeks work - and is not a trivial afterthought - and I believe better Hibernate support for Historical DBs would fantastic. Alex -----Original Message----- From: Drost, Jeff [mailto:Jeff_Drost@adp.com] Sent: Wednesday, 17 January 2007 8:23 AM To: hibernate-dev(a)lists.jboss.org Subject: [hibernate-dev] Regarding Alex Bacon's "Major issue with Hibernate I have read over these threads and am very interested in doing something similar. This is clearly a very common problem. http://lists.jboss.org/pipermail/hibernate-dev/2006-December/000909.html http://lists.jboss.org/pipermail/hibernate-dev/2006-September/000267.htm l Writing audit state with triggers is a tried and true method, and is easy to get working. Reading these records back into a consistent domain model; now that's magic. I would like to set a session level filter parameter with the desired transaction date. I have found "something" of a solution, though I'm not sure if it's taking advantage of a Hibernate feature or a hack. Hibernate will correctly substitute filter parameters in conditions that are not part of filters. This means you can map where clause on an entity that refers to a filter parameter. @FilterDef(name = "foo", parameters = {@ParamDef(name = "asOfDate", type = "timestamp")}) @Where(clause = ":foo.asOfDate BETWEEN eff_start_dt and eff_end_dt") session.enableFilter("foo").setParameter("asOfDate", getAsOfDate()); See also http://forum.hibernate.org/viewtopic.php?t=969798 Thanks, Jeff ----------------------------------------- This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, notify the sender immediately by return email and delete the message and any attachments from your system.