This week's book giveaway is in the Kotlin forum.
We're giving away four copies of Kotlin in Action and have Dmitry Jemerov & Svetlana Isakova on-line!
See this thread for details.
Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Audited tables  RSS feed

Bernard Perchman
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We've recently developed an application that has audit triggers which update an object history. We've implemented Hibernate for object persistence but we've had some trouble with providing an elegant solution to audit table reading. Specifically, we have a table X (a complex object with array-based child attributes) and its audit history X_audit. Creation and updates are pretty simple becuase the triggers fire audit records into X_audit without intervention from Hibernate.

However, the difficulty comes about when selecting time-driven data from X/X-audit. The audit table has a from- and to- date on it, and the X table has a from- on it. So selection is a complex process: for Date (D) if there is an X-audit record with (D) between from- and to- then retrieve this. Otherwise, if X there is an X with from- < (D) then use this.

My question is twofold: (i) what's the best object-relational mapping technique to represent this? I've mapped X as an entity and done the SQL using a createSqlQuery() method. (ii) What's the most elegant way of doing the retrieve ? I don't think it can be done in pure SQL because it requires logical precidence.
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!