Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    9

    Unanswered: mutating table error

    Hi, there.
    Have next structure:
    Code:
    create table tbl_a 
    (
    tbl_a_id int constraint pk_tbl_a primary key
    );
    
    create table tbl_b 
    (
    tbl_b_id int constraint pk_tbl_b primary key,
    tbl_a_id int constraint fk_tbl_b_tbl_a references tbl_a (tbl_a_id) on delete set null
    );
    
    create index i_tbl_b$tbl_a_id on tbl_b(tbl_a_id);
    
    
    create table tbl_c
    (
    tbl_c_id int constraint pk_tbl_c primary key,
    tbl_a_id int constraint fk_tbl_c_tbl_a references tbl_a (tbl_a_id) on delete set null
    );
    
    create index i_tbl_c$tbl_a_id on tbl_c(tbl_a_id);
    
    
    create table tbl_d
    (
    tbl_d_id int constraint pk_tbl_d primary key,
    tbl_c_id int constraint fk_tbl_d_tbl_c references tbl_c (tbl_c_id) on delete set null
    );
    
    create index i_tbl_d$tbl_c_id on tbl_d(tbl_c_id);
    
    
    CREATE OR REPLACE TRIGGER TBL_B_BRIUD
    BEFORE DELETE OR INSERT OR UPDATE
    ON TBL_B
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
      delete from tbl_d
      where 1=0; 
    END tbl_b_briud;
    
    
    insert into tbl_a values(1);
    
    insert into tbl_b values(1, 1);
    insert into tbl_c values(1, 1);
    
    insert into tbl_d values(1, 1);
    
    commit;
    And after executing
    Code:
    delete from tbl_a;
    I obtained:
    Error at line 1
    ORA-04091: table TBL_D is mutating, trigger/function may not see it
    ORA-06512: at "TBL_B_BRIUD", line 2
    ORA-04088: error during execution of trigger 'TBL_B_BRIUD'
    Please, explain why fk_tbl_d_tbl_c is used

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DELETE FROM TBL_A causes:
    (1) TBL_B.TBL_A_ID = NULL, which is UPDATE, which causes trigger TBL_B_BRIUD to fire
    (2) TBL_C.TBL_A_ID = NULL, which causes ...
    (3) ... TBL_D.TBL_C_ID = NULL, which is UPDATE

    So: in (3) table TBL_D is in process of mutation (as TBL_C_ID column is being set to NULL). At the same time, trigger TBL_B_BRIUD is trying to perform DELETE FROM TBL_D (1).

    As Oracle says, table TBL_D is *mutating* and thus can't be modified by a database trigger.

    Put it on a piece of paper (i.e. draw the ER diagram). Follow "DELETE FROM TBL_A" execution, and you'll see what's going on).

  3. #3
    Join Date
    Jun 2014
    Posts
    9
    Quote Originally Posted by Littlefoot View Post
    (2) TBL_C.TBL_A_ID = NULL, which causes ...
    (3) ... TBL_D.TBL_C_ID = NULL, which is UPDATE.
    What? How?
    Lets just disable trigger and try to delete root row again:
    Code:
    CREATE OR REPLACE TRIGGER TBL_B_BRIUD
    BEFORE DELETE OR INSERT OR UPDATE
    ON TBL_B
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DISABLE
    BEGIN
      delete from tbl_d
      where 1=0; 
    END tbl_b_briud;
    
    delete from tbl_a;
    
    select * from tbl_d;
    and result is

    TBL_D_ID TBL_C_ID
    ---------- ----------
    1 1
    1 row selected.
    So, i asked, why FK_TBL_D_TBL_C is triggered? I beleive, it's a bug.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't think so.

    FK_TBL_D_TBL_C foreign key constraint "fires" because of cascades. When you delete a record from TBL_A, its TBL_A_ID is referenced by TBL_C's TBL_A_ID column. Another cascade step is TBL_D's TBL_C_ID column which references TBL_C's TBL_C_ID column.

    Shortly, when you delete from TBL_A, it affects TBL_C which affects TBL_D.
    Code:
    TBL_A.TBL_A_ID >>> via FK >>> TBL_C.TBL_A_ID
                                  TBL_C.TBL_C_ID >>> via FK         >>> TBL_D.TBL_C_ID
                                                                          ^
                                                                          |
                                                    these two, in parallel, with the DB trigger
                                                    enabled, cause mutating table error
                                                                          |
                                                                          v
                   >>> via FK >>> TBL_B.TBL_A_ID >>> via DB trigger >>> TBL_D
                                                                          ^
                                                                          |																	  
                                                    when the DB trigger is disabled, 
                                                    TBL_D is not affected by TBL_B's changes
                                                    so nothing mutates

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •