Appendix C: Master-detail Key Triggers

Introduction

This appendix lists the triggers and procedures that are generated automatically in SQL*Forms 3.0 for master-detail coordination. The code listed in this appendix are for the dept (master) and emp (detail) blocks.

Procedures

The following procedures are automatically generated to support the key triggers for master-detail coordination.

check_package_failure

   procedure check_package_failure is
   begin
       if not form_success then
           raise FORM_TRIGGER_FAILURE;
       end if;
   end;

query_details

   procedure query_details ( detail char ) is
   begin
       go_block(detail);
       check_package_failure;
       execute_query;
   end;

clear_details

   procedure clear_details ( detail char, master char, opt number ) is
   begin
       go_block(detail);
       check_package_failure;
       if :system.block_status = 'CHANGED' then
           clear_block(opt);
           if :system.block_status = 'CHANGED' then
               go_block(master);
               raise FORM_TRIGGER_FAILURE;
           end if;
       end if;
       clear_block;
   end;

query_dept_details

   procedure query_dept_details is
   begin
       if ( (:dept.DEPTNO is not null) and :system.record_status != 'NEW' )
    then
           query_details('emp');
       end if;
       go_block('dept');
   end;

clear_dept_details

   procedure clear_dept_details ( checkmaster boolean, opt number ) is
   begin
       if ( not checkmaster or ((:dept.DEPTNO is not null)) ) then
           clear_details('emp', 'dept', opt);
       end if ;
       go_block('dept');
   end;

Key Triggers

This section lists the key triggers that were generated for master-detail coordination.

KEY-CLRBLK

   clear_dept_details(TRUE, ASK_COMMIT);
   clear_block;
   exception when form_trigger_failure then null;

KEY-CREREC

   clear_dept_details(TRUE, ASK_COMMIT);
   create_record;
   exception when form_trigger_failure then null;

KEY-CLRREC

   clear_record;
   clear_dept_details(FALSE, ASK_COMMIT);
   query_dept_details;
   exception when form_trigger_failure then null;

KEY-DELREC

   declare
       cursor detail_cur is select 'x' from emp
           where DEPTNO = :dept.DEPTNO;
       detail_dummy  char(1);
   begin
       open detail_cur;
       fetch detail_cur into detail_dummy;
       if ( detail_cur%found ) then
           message
       ('Cannot delete master record when matching detail records exist.');
           close detail_cur;
           raise form_trigger_failure;
       end if;
       close detail_cur;
   end;
   begin
   delete_record;
   clear_dept_details(FALSE, NO_COMMIT);
   query_dept_details;
   exception when form_trigger_failure then null;
   end;

Other Key Triggers

The following triggers have the same code except for the do-the-right-thing built-in.

KEY-DOWN
KEY-ENTQRY
KEY-EXEQRY
KEY-NXTREC
KEY-NXTSET
KEY-PRVREC
KEY-SCRDOWN
KEY-SCRUP
KEY-UP

For example, the trigger code for KEY-DOWN is as follows.

 
  clear_dept_details(TRUE, ASK_COMMIT);
   down;
   query_dept_details;
   exception when form_trigger_failure then null;