Trigger errors
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Trigger errors

  1. #1
    Join Date
    Feb 2001
    Posts
    119
    create or replace trigger acc_det_update_trigger before update on
    account_details for each row
    begin
    insert into acc_det_backup select * from acc_det where Comp_acct in
    (select comp_acct from acc_det where comp_acct =ld.comp_acct );
    end ;
    /

    Hear I want to insert the rows into the backup table before the rows are updated .I am getting these errors

    ERROR at line 1:
    ORA-04091: table TESTUSER.ACC_DET is mutating, trigger/function may not see it
    ORA-06512: at "TESTUSER.ACC_DET_UPDATE_TRIGGER", line 2
    ORA-04088: error during execution of trigger 'TESTUSER.ACC_DET_UPDATE_TRIGGER'

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You can't look at or modify a table (acc_det in this case) that is mutating (changing) in a trigger. You will need to rewrite the query so it avoids doing a double select on acc_det.

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hello;
    You are not allow to see the tables data in which trigger you are currently. This is for row-level-trigger;

    but you can store data in a data of a package ( when you are in row-level-trigger ) and read that data from package on statement-trigger-level;

    Orca

  4. #4
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    Create the trigger as an atonomus transaction and from it call a function that run your select and return the columns/values you need.

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    This trigger fires before update on the account_details table but at runtime reports the acc_det table as mutating.
    It looks like it's fired by an update statement inside a trigger on the acc_det table.
    If this is the case you should make the backup of the acc_det row in a before update trigger on the acc_det table, not on the account_details table.
    Then the trigger could look like this:
    Code:
    create or replace trigger acc_det_update_trigger before update on 
    acc_det for each row 
    begin 
      insert into acc_det_backup (comp_acct, ... list of other columns here ...)
      values (:old.comp_acct, ... other :old. columns here ...);
    end ; 
    /
    If you made a typo and the acc_det and account_details tables are the same, you can use the trigger above as well.
    Ales




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