replace foreign key with on delete cascade with trigger

Hi Friends,

It is very long time since i visited this form. i am looking for some idea because of functionality restrictions to replace foreign key with on delete cascade --to--> trigger in 10g.

eg:
we have two tables dept and emp table.
emp table has foreign key with on delete cascade enabled

---

now we want to get same functionality by removing this foreign key but still having delete cascade between emp and dept .

please let me know if this possible
after removing foreign key . child records should be deleted when parent is deleted .


actual setup is i have 1 table grp with 5000 child tables having foreignkey pointing to single column in grp table.

Thanks fro your help.
Siva prakash

Yes, it is possible. For performance reasons, you probably would still need to index corresponding columns on the child tables.

Search on for "delete cascade" on asktom..he explains it (and provides examples) quite well.

i disabled all foreign key constarinst and tried this trigger .
trigger succesfully created .
but when i delete from grp table master table it is giving
ora-4091 table isr.grp is mutating

this basically deletes from 4 types of child tables.

CREATE or replace TRIGGER grp_delete_cascade
before DELETE ON grp
for each row
DECLARE
l_version_cd varchar2(8) ;
l_score_tbl varchar2(30) ;
l_item_ct_tbl varchar2(30) ;
l_dist_tbl varchar2(30) ;
l_c_count_tbl varchar2(30) ;
sql1 varchar2(500) ;
sql2 varchar2(500) ;
sql3 varchar2(500) ;
sql4 varchar2(500) ;
BEGIN
select distinct cd into l_version_cd from version where version_id in
( select version_id from grp where grp_id = :old.grp_id ) ;
l_score_tbl := 'SCORE_' || l_version_cd ;
l_item_ct_tbl := 'ITEM_CT_' || l_version_cd ;
l_dist_tbl := 'DIST_' || l_version_cd ;
l_c_count_tbl := 'C_COUNT_' || l_version_cd ;
sql1 := 'delete from ' || l_score_tbl || 'where ' || l_score_tbl || '.grp_id = ' || :old.grp_id ;
sql2 := 'delete from ' || l_item_ct_tbl || 'where ' || l_score_tbl || '.grp_id = ' || :old.grp_id ;
sql3 := 'delete from ' || l_dist_tbl || 'where ' || l_score_tbl || '.grp_id = ' || :old.grp_id ;
sql4 := 'delete from ' || l_c_count_tbl || 'where ' || l_score_tbl || '.grp_id = ' || :old.grp_id ;
execute immediate sql1 ;
execute immediate sql2 ;
execute immediate sql3 ;
execute immediate sql4 ;
dbms_output.put_line (' sql1: ' || sql1) ;
END;
/

Wooo..Sorry. I completely misunderstood your initial question. I thought you wanted to implement "delete cascade" instead of triggers..rather than the other way around!

I'm not much of a developer..but that seems like a bad idea. You're reinventing the wheel..and setting yourself for mutation problems.

The "select version_id from grp where grp_id = : old.grp_id" in the trigger is obviously causing the mutating error. Don't select it, rather find a way around it..can't you use the old version_id directly?

PS : Why are you even going this route?

actually this version_id is unique .
now the problem is delete_cascade is inefficient in my scenario ...

i have 4000 tables each table is something like score_ver1
score_ver2 . when i try to delete grp from this table . database will start looking at all these 4000 tables . actually based on record that is to be deleted . i can tell where child tables are present

so i am dynamically selecting these tables and deleteing coresponding records in them.

but as you said ... below select stmt in trigger might be causing mutation .
select version_id from grp where grp_id = :old.grp_id

how can i get rid of this?

can i use commit or use multiple procedure ?

your help will be much appreciated.

http://asktom.oracle.com/~tkyte/Mutate/index.html

See case 2.

HI,

i saw case2 from the link which you sent .
i cannot add hidden_date column to grp table.
thsi is primary key table pointing to 5000+ tables and used in the application mercilessly ....

Go for simple solution - procedure, in which you pass parent key value and delete 4000 child tables' rows.

Trigger and cascade delete are all slow processes.

Tamil

I was just thinking if i can do this process automatically transparent to application .

basically there might be several screens in oracle forms application where this thing might be present.

also i guess some of you might have noticed there is a bug in 10g in particular .

if you have master tale and child table and if you insert records in master table . child tables are locked . i raised this bug for 10g BUG:4309859 SLOW DML I....

you will notiice this lock if you have something of this kind in large scale . like 1 master table and 1000 child tables.

but the same insert in 8i database will work 40 times faster.

did anyone tried this type of insert 9i ?

here is the new 10g table locking system which i saw different from 8i .

following is direct reply from oracle :
----------------------------------------

is locking a table when records are being inserted into master table is also normal behaviour ?


07-JUN-05 13:43:09 GMT


UPDATE:
=======

1.- YES, under direction of Development team information
2.- Only way to avoid situation are to reduce the number of FK's or temporarily disa
ble them during loads.

This locking architecture would cause performance problem on almost all databases.