MASTER DETAIL RELATIONSHIP FORM 6i

Hi,
   if you delte records in a master-detail relationship how can we do that if one of the rule of deleting is that "you cannot delte records until the master table or parent still contains records'
theroosterAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
soraConnect With a Mentor Commented:
Normally, it would be the other way round. i.e - you cannot delete master/parent records if there are still child (or foreign key table) records.

Actually the easiest way to do this would be to enforce the foreign key referential integrity constraint in the database itself.


Lets say you have a DEPT table

DEPT
----
DEPT_NO
DEPT_NAME


EMP
---
EMP_NO
EMP_NAME
DATE_OF_JOINING
etc


EMP_DEPT_INFO
--------------
EMP_NO
DEPT_NO


In the above case you can add a foreign key (without delete cascade) to EMP_DEPT_INFO to both EMP and DEPT. This way, you cannot delete the EMP record or the DEPT record until the EMP_DEPT_INFO record is first deleted.

ALTER TABLE EMP_DEPT_INFO ADD CONSTRAINT fk_emp_no FOREIGN KEY (emp_no) REFERENCES EMP (EMP_NO);

and

ALTER TABLE EMP_DEPT_INFO ADD CONSTRAINT fk_dept_no FOREIGN KEY (dept_no) REFERENCES DEPT (DEPT_NO);

If your rule is not the typical parent-child relationship, you can always write a trigger on the table (whose records you are deleting) and inside the trigger check if there are any records in the other parent table. If found, then raise an exception. This exception can be trapped in your code - from where the deletion is being done.


sora
0
 
jtriftsMI and AutomationCommented:
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
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
theroosterAuthor Commented:
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?

0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
soraCommented:
therooster

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


sora
0
 
theroosterAuthor Commented:
Okey,
  let me just check which of your suggestion works
0
 
theroosterAuthor Commented:
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?
0
 
soraCommented:
therooster-
I think you have too many questions open on the same thread

sora
0
All Courses

From novice to tech pro — start learning today.