#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    1
    Rep Power
    0

    Question trigger mutating


    i have made a simple trigger whcih is doing nothing but just updating the attribute whose value goes down uder a specified value. code is

    create or replace trigger dec_rawmaterial
    after update on raw_material
    for each row
    when ( new.quantity_present < 500 )
    begin
    update raw_material
    set quantity_present = quantity_present + 1000
    where type = :new.type;
    end;
    /


    what i do is

    update raw_material
    set quantity_present = 200
    where type = 'PLAYCITE';

    and error is

    set quantity_present = 200
    *
    ERROR at line 2:
    ORA-04091: table SCOTT.RAW_MATERIAL is mutating, trigger/function may not see it
    ORA-06512: at "SCOTT.DEC_RAWMATERIAL", line 2
    ORA-04088: error during execution of trigger 'SCOTT.DEC_RAWMATERIAL'
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    You cannot update the table that is being updated in an Oracle trigger.

    If the type column is a primary key, you can change your trigger to:
    Code:
    ...
    begin
        :new.quantity_present = :new.quantity_present + 1000;
    end;

IMN logo majestic logo threadwatch logo seochat tools logo