MASTER DETAIL RELATIONSHIP FORM 6i

I agree that this is better implemented on the database side through the use of foreign key constraints.

If you insist on implementing the business rule from Forms 6i (say if you have different applications accessing the same data and only one is not allowed to delete "children", while the others might...)

You could put an on delete trigger trigger that checks to see if a record exists in the "parent" and then rollback (perhaps with  a form_trigger_failure) if you find one.

Alternatively, you make the application invoke the delete through a button press and then put the check in the when-button-pressed trigger...where if the record is found it issues a message, and if not founf then it deletes the child.

Note that if you have set this up as a true master-detail relationship, this will be such that the ONLY way you can delete a parent record is if you delete the child first or set an ON DELETE CASCADE constraint for the key.

Regards,
JT

Please clarify your question.  Do you want to support deleting records from master-detail tables?  If so, deleting records from the detail table should be no problem.  Do you want the master table also deleted if all details are deleted?  

Or do you want the form (or database) to prevent master records from being deleted if detail records exist?

Or do you want the form (or database) to automatically delete the detail records if a master record is deleted?

All of these are possible, so please explain what you would like to do.

i prefer this question markgeer.

Or do you want the form (or database) to automatically delete the detail records if a master record
is deleted?

If you want the database to delete detail records when master records are deleted (the best way to do it my opinion) then drop the existing foreign key constraint between the master and detail tables (if any) and re-create it including "on delete cascade".

If for some reason you do not want to have the database do this, you can create a pre-delete trigger on the master block of the form that will delete any/all related detail records before the master record is deleted.  Of cource you will have to change the properties of the block relationship to allow the master delete while detail records exist.

therooster

The answers to questions in your latest post and those suplied by markgeer are available in the previous posts also


sora

Okey,
  let me just check which of your suggestion works

i forgot...
   a have a button that is used for deleting..
   if we put the code in the when-button-press
   would it be okey if we will do it this way..
   go_block('employee_leave');
   delete_record;
   commit;
   go_block('employee_status');
   delete_record;
   commit;
   go_block('employee_master');
   commit
or what code should be written in pre-delete trigger?

therooster-
I think you have too many questions open on the same thread

sora