Path: utzoo!attcan!uunet!zaphod.mps.ohio-state.edu!usc!apple!fernwood!oracle!news
From: kbittner@oracle.uucp (Kurt Bittner)
Newsgroups: comp.databases
Subject: Re: SQL*Forms V3.0 -- Multiple detail relations?
Message-ID: <1990Oct22.020947.26529@oracle.com>
Date: 22 Oct 90 02:09:47 GMT
References: <DRACK.90Oct17081418@diablo.titan.tsd.arlut.utexas.edu> <1990Oct18.152751.11893@oracle.com> <2797@ns-mx.uiowa.edu>
Reply-To: kbittner@oracle.UUCP (Kurt Bittner)
Distribution: comp.databases
Organization: Oracle Corporation, Belmont, CA
Lines: 62

In article <2797@ns-mx.uiowa.edu> broy@vaxa.weeg.uiowa.edu writes:
>In article <1990Oct18.152751.11893@oracle.com>, kbittner@oracle.uucp (Kurt Bittner) writes...
>> 
>> 1) Use the CONSTRAINT capability provided in the Oracle RDBMS to define the
>>    primary key/foreign key relationships when you create the tables.
>>    It is also a good idea to code CHECK constraints into the table definitions
>>    since Forms 3 can use these to automatically generate validation triggers
>>    if you select "Use Constraints" on the default block screen.
>
>Oracle documentation and Oracle class instructors indicate that CONSTRAINT 
>definitions are NOT supported until version 7. 
>Does SQL*FORMS 3.0 use them while the RDBMS does not?

Yes.  It's a good idea to start using them now, since the V6 database will
accept the CONSTRAINT syntax.  Then when database enforcement is turmed on in
V7, you'll be prepared.  The issue of "supported" has more to do with the fact
that the constraint defs don't do anything in the rdbms right now.
Note that not only are the constraints used by SF3, but DEFAULTS and CHECK
constraints are used to generate triggers.  

>
>> 2) Use the Block Default option to define your blocks.  When you define the
>>    "detail" block, indicate that the "master" block is the master block.
>>    If you press "List of Values" while in the "master block" field, you'll
>>    get a pop-up menu of all valid master block choices based on blocks 
>>    already defined having a foreign key reference in your current table.
>>    Selecting the master block will define the relationship automatically.
>
>This works fine for the first detail block, but I have had to manually create
>the appropriate triggers for all subsequent detail blocks of the same master. 
>I haven't tried using your CONSTRAINT suggestion. Is that what corrects the 
>problem? 

It won't handle this, since the default form generator creates triggers called
'clear_details', etc.  A second detail block for the same master would over-
write these triggers, so this isn't done.  One way of handling this would be 
to:  (1) create a default form for the first master-detail relationship
     (2) create a second default form for the second master-detail relationship
         (consisting of only the master block and the second detail block)
     (3) use the copy/reference object facility to copy the block defs, 
         triggers, etc. from the first form into the second form.  Be sure to
         use different names to prevent over-writing.
     (4) Edit the triggers you want to enable for both blocks so that things
         like KEY-UP in the master block will coordinate both detail blocks.
This isn't perfect, but is better than nothing, and certainly is better than
forms v2.

>If so, why didn't tech support clue me in (ref TAR# 99530.41). I was 
>told it was a bug that would be corrected in the next release.

 This may be a better answer.

>There is also a problem that if you delete all blocks in the form, the 
>clear_details and query_details procs aren't deleted. Then when those blocks
>are added back to the form, the procs aren't re-created.

Correct.  You have to remember to manually delete the triggers which do block 
coord.  

Kurt Bittner           "My opinions are, humbly, my own and are not likely to
Consultant              be shared by anyone, let alone my company."
Oracle Corporation