In a new app, I’m trying to implement an interceptor for the following events:
ORMPostInsert
ORMPostUpdate
ORMPostDelete
What I ultimately need to be able to do is get a particular property from the entity for which the event is fired, and then create a record in a separate database that contains that value (and others). More or less a basic audit trail.
The problem I’m running into has to do with transactions, I believe. When I save the entity, it’s transactional, so when I try to save the data to the other database (either via another entity or even a straight SQL query), I get all kinds of complaints about the mixing of datasources in a transaction.
Hoping that someone has some advice about how to go about tackling something like this. I appreciate the help!
There is a known issue with the open of another entity in an entity event, this has been logged with Adobe but they claim that this is by design.
The only solution is to use normal SQL, or maybe even HQL.
In which I have discussed here
http://www.andyscott.id.au/blog/Auditing-data-with-ORM-and-ColdFusion-and-a-workaround
For me, the issue is the multiple databases. Because the original save() is transactional, the subsequent save inside the ORM event occurs within the original transaction. I’ve tried using just SQL, but the result is the same since a different datasource is accessed within the original transaction for a different datasource.
What I’m really after is how (or if) I can keep my normal save() to be transactional while still being able to hook into the ORM events to do auditing. Unless there’s something in the AOP magic that I haven’t explored yet, I feel like I’ve hit a wall 
Thanks
Ok that seems a bit harsher, and I am not sure as I would have thought that if the datasources are in the same database it would work, but transactions don’t work across multiple databases.
Hey Andrew–
Actually, I was able to get the SQL to work (finally). When you mentioned the bug, I looked again at my process, and sure enough, I was opening a relational entity of the target object. Even when I stopped trying to log anything, it still bombed since I had traversed the relationship and realized the related entity before the transaction was complete.
Once I realized this, though, I found a work-around. Instead of using the entity itself to get the specific value of its related entity, I wrote a simple little method to parse through my entity’s metadata and build a SQL path to the value of the other entity (or table, in this case) that I needed. I simply execute the sql query as I normally would, and I’m able to get the value without messing up the Hibernate flush.
As far as logging, since I now have a plain ol’ struct of data that’s completely decoupled from anything Hibernate, I spawn a thread to log the access trail, and am able to do this even on the other database without it conflicting with the original Hibernate transaction.
Cool, I wish I could find the ticket as there was nice Java package that would suit very well this. Sounds like an interesting expandable solution.