FORMS: how do I implement not-quite a master-detail relationship?

Table A contains the following fields (simplified for example; # is PK):

#name

effective_start_date
description

Table B is an archive of Table A; any updates or deletions to A get inserted into B with a database level trigger (on update or delete):

#name
#effective_start_date

description
archive_date
archive_comment

So, B can have multiple versions of the description for the 'name' in Table A, each with a different effective_start_date. (The data is really more complex than this, and warrants archiving.)

When A is updated or deleted, the DB trigger inserts the old row into B, populating everything but the archive_comment. This is where Forms comes in.

Table A data is maintained in forms. I have a block for A (containing all table columns) and a block for B, containing the comment as a database column, and the effective_start_date as a non-db column (I maintain it internally). At a high-level, this is what I need the behavior to be:

When a user updates or deletes a Table A record, prompt them to enter a comment for the archive (if they wish). As I see no way to "intercept" the database trigger on A and pass in the archive_comment from forms to be inserted into B w/ the rest of the trigger-generated record, my approach was this:

When there is an update or delete, go ahead and commit the record to the DB, which will trigger the insert of the archive record into B. Then prompt user to enter in comment, and on the commit-form, intercept the insert or update of B that Forms will attempt. For this, I have written on_insert and on_update triggers. These triggers always do an update on B, based on the 'name' and the 'effective_start_date'. Eg "update table_B set archive_comment = :b.archive_comment where name = b.name and effective_start_date = b.effective_start_date"

This works, the very first time through.

The next time, however (say the user updates the same row in A again), I get FRM-40654. Forms brings up the comment window w/ the previous value it in, and it thinks that the value is out of sync w/ the DB. i have successfully done a set_record_property STATUS to QUERY, but the error still occurs.

Any ideas?? Maybe I'm taking entirely the wrong approach, but I dont' know what else to do. I've tried making the comment a non-db field, but then I don't have any luck actually getting the value into the DB. (is it possible to write your own update code in FORMS that tries to set a DB value to a non-db forms field?)

Thanks for your suggestions.
Carol Received on Thu Mar 27 2003 - 01:23:04 CET