DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: replace foreign key with on delete cascade with trigger

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    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

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    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.

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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;
    /
    siva prakash
    DBA

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    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.

  5. #5
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  6. #6
    Join Date
    Oct 2002
    Posts
    807

  7. #7
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  9. #9
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  10. #10
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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
  •  


Click Here to Expand Forum to Full Width