Some background -

We are running Oracle 8/9. We have a (mostly) normalized relational schema, with timestamp fields in place. However, these fields are managed via triggers. The DBA's will not remove the triggers. I want to be able to use these fields for optimistic locking, and I also will need use of the second level cache. I know that I can simply call session.refresh(obj) after every save/update to get the correctly modified timestamps after the triggers are done. However, there is a feeling amongst others here that this would be too inefficient. The way they handle this now, using a homegrown O/R tool is to make all inserts/updates through a CallableStatement in the form of "begin [insert/update stmt] returning [a couple of fields]". I've taken a quick look at possibly modifying Hibernate to do the same thing and wanted to get your opinions if this was doable.

As I see it, this is what would need to be modified or extended:

BatcherImpl to use conn.prepareCall() instead of prepareStatement().
EntityPersister to add the additional begin & returning text, register the out params, and update the object with the triggered fields.
PersisterFactory (possibly) to instantiate my new OracleTriggeredEntityPersister.

I'm sure I'm missing loads of detail, but does this look generally doable? What else should I be looking at?

Thanks,
Ken

Am I making this too difficult - would a simple Interceptor.postFlush() implementation suffice?

Something like this is already implemented in Hibernate3 - you could have a look at the CVS version.

From what I've seen of Hibernate3, you've added support for Stored Procedures - which this is not. Can you point me at a particular class to review?

You can define custom SQL for CRUD operations, and also make the statements used callable. There is no support for any kind of TS-Return values though.

Right - no built in support. But is it doable (on our part) to modify/extend/hack Hibernate to support that kind of operation? I understand of course that this would be Oracle specific and would never integrate into the main codebase. Assuming it is doable, is there much more to it than I mentioned in my first message: BatcherImpl, EntityPersister, (maybe) PersisterFactory?

Thanks!