Solved

MASTER DETAIL RELATIONSHIP FORM 6i

Posted on 2002-04-28
9
1,149 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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 5

Accepted Solution

by:
sora earned 50 total points
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
 

Author Comment

by:therooster
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility
Okey,
  let me just check which of your suggestion works
0
 

Author Comment

by:therooster
Comment Utility
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
Comment Utility
therooster-
I think you have too many questions open on the same thread

sora
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

763 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now