qaziasif
asked on
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
Can some body suggest the ways.
Regards,
MAB
ASKER
Can u give me some example
ASKER
I wan to do this in Database not on FORMS
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.
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(-2
end if;
end;
/
declare
a number;
begin
select count(*) into a from dept where deptno=:old.deptno;
if a <>0 then
raise_application_error(-2
end if;
end;
/
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
a parent record exist. If it does then rollback the delete.