Keeping history on master/detail tables

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 Received on Sat May 20 2006 - 16:16:42 CDT