Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Keeping history on master/detail tables
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