Reverse of Cascade Delete

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
qaziasifAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrJekyllCommented:
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.
qaziasifAuthor Commented:
Can u give me some example
qaziasifAuthor Commented:
I wan to do this in Database not on FORMS
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

DrJekyllCommented:
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.  
anand_2000vCommented:
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;
/
qaziasifAuthor Commented:
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
andrewstCommented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qaziasifAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.