Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23

    Unanswered: trigger (mutating table error)

    I use the following trigger to check that the sum of percentage of properties of a vehicle can't be more than 100%.


    create or replace trigger check_percentage
    before insert or update of PER_PROP on PROPERTY
    for each row
    declare
    sum PROPERTY.PER_PROP%TYPE;

    begin

    if inserting then
    SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
    if((sum + :new.PER_PROP )>100) then
    raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given' || to_char(100 - sum));
    end if ;
    end if ;

    if updating then
    SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
    if((sum + :new.PER_PROP - ld.PER_PROP )>100) then
    raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given'|| to_char(100 - sum - ld.PER_PROP));
    end if ;
    end if;

    end;
    /


    ERROR at line 1:
    ORA-04091: table PSS12.PROPERTY is mutating, trigger/function may not see it
    ORA-06512: at "PSS12.CHECK_PERCENTAGE", line 14
    ORA-04088: error during execution of trigger 'PSS12.CHECK_PERCENTAGE'



    I need something to avoid this error.

  2. #2
    Join Date
    Jan 2004
    Posts
    37
    create a statement level and a row level trigger.In side row leveltrigger you declare a pl/sql table and store the :new and ld values...
    create a statement level trigger and manupulate with these values....
    both should be contained in side a package....
    because you are trying DML operations on the mutating table and this happen in the row level trigger only...
    so instead of doing that just store the values in the PL/SQL table and do the manupulaiton in the statement level trigger..
    Last edited by chiranjib_s; 02-02-04 at 07:13.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trigger (mutating table error)


Posting Permissions

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