Solved

Reverse of Cascade Delete

Posted on 2003-10-22
8
1,892 Views
Last Modified: 2012-06-22
I have a emp (Child) table and Dept (master) table. Cascade delete is set between two. I want to have cascade delete on e.g. If some body delete a Dept records all related records in Emp should be deleted. Its is working fine for me. Now what I want in addition to this is whenever user what to child record in Emp table it should not be deleted if there is a master record in DEPT.

Can some body suggest the ways.

Regards,

MAB
0
Comment
Question by:qaziasif
[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
8 Comments
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9600200
You could write a Pre-Delete trigger on your child table that checks to see if
a parent record exist. If it does then rollback the delete.
0
 

Author Comment

by:qaziasif
ID: 9600278
Can u give me some example
0
 

Author Comment

by:qaziasif
ID: 9600337
I wan to do this in Database not on FORMS
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 5

Expert Comment

by:DrJekyll
ID: 9600507
Correction, you cannot use a trigger as transaction control statements are not allowed.  I am not sure how to do this at the database level which is ideal.  In your app you could check to see if a parent record exist. If so do not allow deletion. However this not prevent deletions outside of the application.  
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9600513
Create or replace trigger trg_emp before delete on emp as
declare
a number;
begin
select count(*) into a from dept where deptno=:old.deptno;
if a <>0 then
raise_application_error(-20111,'Delete not allowed');
end if;
end;
/
0
 

Author Comment

by:qaziasif
ID: 9600697
Think carefully.

If I have set Cascade Detele on Dept Table (Which I mentioned above) and want to delete data from dept table where deptno = 10 then the trigger you mentioned would fire and my Cascade would not work. If I adopt the way by Anand then I need to know how to by pass the trigger code when its is being called by some other source
0
 
LVL 15

Accepted Solution

by:
andrewst earned 50 total points
ID: 9601747
Strange requirement.  But if you have a foreign key from EMP to DEPT, then if EMP.DEPTNO is not null it MUST mean that the associated DEPT record exists.  So you can prevent delete like this:

CREATE OR REPLACE TRIGGER strange_rule BEFORE DELETE ON emp
FOR EACH ROW
BEGIN
  IF :old.deptno IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20001,'Cannot delete employee that is related to a department' );
  END IF;
END;
/

Then you still have the problem of the cascade delete on DEPT being stopped by this.  The only way round that (I can think of) is to set a flag variable in a package like this:

CREATE OR REPLACE PACKAGE pkg IS
  deleting_dept BOOLEAN := FALSE;
END;
/

CREATE OR REPLACE TRIGGER dept_bd
BEFORE DELETE ON dept
BEGIN
  pkg.deleting_dept := TRUE;
END;
/

CREATE OR REPLACE TRIGGER dept_ad
AFTER DELETE ON dept
BEGIN
  pkg.deleting_dept := FALSE;
END;
/

Then change the first trigger to:

CREATE OR REPLACE TRIGGER strange_rule BEFORE DELETE ON emp
FOR EACH ROW
BEGIN
  IF :old.deptno IS NOT NULL AND NOT pkg.deletin_dept THEN
    RAISE_APPLICATION_ERROR(-20001,'Cannot delete employee that is related to a department' );
  END IF;
END;
/

How about that?
0
 

Author Comment

by:qaziasif
ID: 9603917
It seems okay to me. Its a nice way of doing reverse cascade.

 About strange, if you have to maintain old stuff and you cant do much about it then you have to do like this. Anyway thank you for propmt response.

Regards,

MAB
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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 how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

736 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