Solved

Reverse of Cascade Delete

Posted on 2003-10-22
8
1,852 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
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
 
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
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 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

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…
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 copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

705 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

17 Experts available now in Live!

Get 1:1 Help Now