Details, Details SQL*Forms 3.0

SQL*Forms: Delete Details Option (Oracle V6)

       By:  Michele Greeder
       
      When creating a multi-block form with a Master-Detail relationship
      in Version 3.0 SQL*Forms, one of the first steps is to set up the
      master and detail blocks and specify the Master-Detail
      Relationship.  This relationship allows your form to maintain the
      connection between blocks that have a "primary to foreign key"
      relationship between them.  This is accomplished  by selecting the


      "Default" option from the main menu's "Block" option in order to


      enter the Default Block Form.  While using the Default Block Form
      to build a multi-block data entry form, I discovered a few
      problems and their respective solutions in regards to the "Delete
      Details" option.
       

      When creating a multi-block form, the Delete Details option
      determines the way in which detail records are handled when data
      is queried, deleted, or committed.  For example, if a record in
      the form's master block is deleted, the Delete Details option is
      specified, SQL*Forms automatically builds certain triggers and
      procedures for a master block that has one detail block 
      associated with it.  According to the SQL*Forms Designer's
      Reference Manual (Version 3.0), "if you add more detail blocks to
      a master block, SQL*Forms automatically edits these triggers to
      enforce the relationships."  I have found, however, that this does
      not happen.  This effects the procedure


      "clear_masterblock_details."  The code is written to handle only


      the details in the first detail block.  To modify this procedure
      so that is applies to any additional blocks in the form, the lines
      of code pertaining to each detail block need to be written the
      same way as the code for the first detail block.  This simply
      requires the addition of "not checkmaster or" to each if-statement
      that applies to any additional detail blocks.
       
       
      It is important to note, that as far as I know, the Delate Details
      option and the automatically build triggers and procedures
      associated with it, can only be selected in the Default Block Form
      when creating a new form.  Whether or not the Delete Details
      option is selected, this is the only time it can be used to
      automatically build these triggers and procedures in a multi-block
      form.  By building two separate multi-block forms, one with the
      Delate Details option selected and one without, I found that there
      are only two primary differences between the two .INP files.  If
      the Delete Details option is not initially selected when the new
      form is created, the selection can be reversed by doing the
      following.  First of all, the "clear_masterblock_details"
      procedure still needs to be modified whether the Delete Details
      option is selected or not.  The second modification consists of
      building a "Pre-Delete" trigger which locks each table in share
      update mode, deletes records in the detail blocks along with the
      master, and deletes all rows in the table associated with the
      detail block that corresponds to the master row that is to be
      deleted.  The third modification requires most of the text in the


      "Key-Delrec" trigger to be deleted.  The only text needed is the


      last six-line begin-end statement which clears the detail block
      and deletes the record in the master block.  The cursor statement
      is not needed in this case.
       

      By making these few deletions/additions, a great deal of time and
      effort can be saved when adding the Delete Details option to a
      form which has already been created.  Consequently, if a situation
      arises later in which the Delete Details option is no longer
      required, the option can be "de-selected" simply by deleting the


      "Pre-Delete" trigger and adding the cursor text to the


      "Key-Delrec" trigger with the same result as if the option has not


      been selected in the first place.