Solved

MASTER DETAIL RELATIONSHIP FORM 6i

Posted on 2002-04-28
9
1,249 Views
Last Modified: 2007-12-19
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'
0
Comment
Question by:therooster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 5

Accepted Solution

by:
sora earned 50 total points
ID: 6974746
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
 
LVL 4

Expert Comment

by:jtrifts
ID: 6976549
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6981189
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:therooster
ID: 6991072
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6992147
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
 
LVL 5

Expert Comment

by:sora
ID: 6992592
therooster

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


sora
0
 

Author Comment

by:therooster
ID: 6993739
Okey,
  let me just check which of your suggestion works
0
 

Author Comment

by:therooster
ID: 6996250
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
 
LVL 5

Expert Comment

by:sora
ID: 6997920
therooster-
I think you have too many questions open on the same thread

sora
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question