-
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
siva prakash
DBA
-
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 07: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.
siva prakash
DBA
-
-
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 ....
siva prakash
DBA
-
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 ?
siva prakash
DBA
-
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.
siva prakash
DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|