Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reverse of Cascade Delete

Posted on 2003-10-22
8
Medium Priority
?
1,923 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
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!

 
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 150 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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

660 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