replace foreign key with on delete cascade with trigger

[prakashs43]

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

[Axr2]

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.

[prakashs43]

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 = ld.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 = ' || ld.grp_id ;
sql2 := 'delete from ' || l_item_ct_tbl || 'where ' || l_score_tbl || '.grp_id = ' || ld.grp_id ;
sql3 := 'delete from ' || l_dist_tbl || 'where ' || l_score_tbl || '.grp_id = ' || ld.grp_id ;
sql4 := 'delete from ' || l_c_count_tbl || 'where ' || l_score_tbl || '.grp_id = ' || ld.grp_id ;
execute immediate sql1 ;
execute immediate sql2 ;
execute immediate sql3 ;
execute immediate sql4 ;
dbms_output.put_line (' sql1: ' || sql1) ;
END;
/

[Axr2]

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?

[prakashs43]

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 = ld.grp_id

how can i get rid of this?

can i use commit or use multiple procedure ?

your help will be much appreciated.

[Axr2]

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

See case 2.

[prakashs43]

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 ....

[tamilselvan]

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

[prakashs43]

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 ?

[prakashs43]

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.