Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Keeping history on master/detail tables

Re: Keeping history on master/detail tables

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 20 May 2006 15:21:26 -0700
Message-ID: <1148163685.944389.32470@j73g2000cwa.googlegroups.com>


Hooman, you indicate that you want to be able to tell if the changes were made in a single transaction, why? Isn't it just good enough that any change made to the header or detail table will be logged to the appropriate history table?

A table trigger on the header and detail will handle this if you do not require marking the rows such that you can identify them as being part of the the same transaction. Often a history/audit table has columns for the user and date/time of the change appended to the rows. Are you sure this is not good enough.

If not, you will need to add a transaction id/batch number column to the history tables and select a sequence value to populate it with at the beginning of the transaction. Then you will have to provide the value selected for the header update and pass it to the detail table insert/update. I would suggest making all updates use package created for this purpose. To prevent the development of a process that directly updates the tables without using the package you leave the sequence column nullable but place before insert/update triggers on the tables that reject rows without the value.

HTH -- Mark D Powell -- Received on Sat May 20 2006 - 17:21:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US