How Do You Track Column Changes

[scohan]

We're writing a J2EE/Oracle app. We have a requirement to track changes to specific columns in a subset of our tables. We need to be able to show the before and after column values. And this history needs to be available at any time. So if a column has been changed 10 times over the last year, we need to show the 10 different column values over that time.

What is the best way to provide this functionality?

One thought was to have the code record the changes in a seprate set of 'history' tables. I've also been trying to figure out if Oracle's LogMiner can be used to satisfy this requirement. I'm kind of stumbling around in the dark. There must be a preferred or standard way to satisfy this requirement as I'm sure others have had to do the same thing.

Can anyone please help shed some light on this topic? Thanks.

 

[Guest_imported]

Hi Scohen,
Your requirement is to store the historical data and the best way to do is to have a StartDate and EndDate in the table, current record will be inserted with no end date and any old record will be updated with current timestamp in EndDate.
Thanks,
Naresh
NARESHCH@HOTMAIL.COM

 

[wushutwist]

I also suggest looking into Oracle's built in audit capabilities. No use reinventing the wheel unless you have to.