replace foreign key with on delete cascade with trigger
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.
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.
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.
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?
Last edited by Axr2; 05-25-2005 at 08:15 PM.
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.
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.
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
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.
Click Here to Expand Forum to Full Width