Method to implement audit trail per row

I want to add two columns to all my t开发者_如何学Pythonables [Create Time] & [Modify Time] to capture audit trail .In the future these fields might be used in partitioning the table. Please suggest on what is the best approach of doing this ? Is it wise to do it through some triggers at DB or to update these values through code. We are using Hibernate APIs to talk to Oracle 11g.

Since you're already using Hibernate, take a look at Hibernate Envers:

The Envers project aims to enable easy auditing/versioning of persistent classes. All that you have to do is annotate your persistent class or some of its properties, that you want to audit, with @Audited. For each audited entity, a table will be created, which will hold the history of changes made to the entity. You can then retrieve and query historical data without much effort.

This is the more heavyweight solution, but gives your application full control.

Alternatively, you can do this via Oracle PL/SQL Triggers, but your application will have limited control over that.

There are pros and cons.

Personally, I use triggers.

Doing so leads to an interesting edge case though, when you've a main table and many related table whose audit trail you want to share. Say orders + order lines + [lots of other related tables] that relate to orders. In this case, the interesting piece of data to audit is the order, so orders should arguably contain the audit-related details. If you've a trigger that kicks in for each insert/update/delete of order lines + [lots of other related tables], then performance eventually takes a hit.