Solved

Reverse of Cascade Delete

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

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.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

770 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