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

Carol Marra wrote:

> I'm having trouble representing the following in a Forms app:
>
> 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

Requery the form then ask for the comment.

Daniel Morgan Received on Thu Mar 27 2003 - 06:10:25 CET