Hi Everyone,

I have a two tables with Master/Detail relationship. I would like to
keep a history of both tables. for this reason, I have made two
additionals tables; call them Master-H and Detail-H.

Master-H is the same structure as Master (except for having the
different primary key). Same thing for Detail-H.

I would like to use trigger mechanisms to populate the history tables
whenever changes are made to the original tables. However, I am
running into some chanllenges.

For instance, in one transaction, I make changes to both master and
detail tables. How can I append rows in both Master-H and Detail-H and
at the same time, know that both records were a part of the same
transaction?

I have done this before but with single tables. This is the first time
that I am attempting to record history with Master/Detail tables.
Maybe the method I am using is not the right method after all, since I
really do not feel that this problem should be as complicated as I am
finding it to be.

I would appreciate it if anyone out there can share similar experiences
and their solutions.

Thanks,
Hooman

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 --

Mark, thanks for you reply.

The reason I need to relate the transaction of the header and detail
tables is that I have to provide the users with a screen that shows a
history of changes to these tables. So if there was a transaction in
the past, where the user modified records in both Header and Detail, I
want to display it as a single record instead of two separate ones.
Otherwise reading the history information will be very confusing.

As for your solution, I think I understand it. But it is still a lot
more complicated that I would hope for. Maybe you are right and I need
to rethink wether it is really that necessary to have to relate the
transactions afterall.

Another method that I am thinking of, which could be simpler, would be
to use save date and time of each tranaction in the both header/detail
and their history tables. That way, I could group the transactions by
their datetime.

Hooman