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

Thread: table mutating...

  1. #1
    Join Date
    Aug 2001
    Location
    Dubai
    Posts
    41

    Red face


    Hello guys
    Hope you can help me as soon as possible . I wrote a trigger which goes like ..

    CREATE OR REPLACE TRIGGER SALCHK
    BEFORE INSERT OR UPDATE ON EMP
    FOR EACH ROW
    DECLARE
    MAXSAL NUMBER;
    MINSAL NUMBER;
    APPERR EXCEPTION;
    BEGIN
    SELECT MAX(SAL),MIN(SAL) INTO MAXSAL,MINSAL FROM EMP WHERE JOB = :NEW.JOB;
    IF :NEW.SAL < MINSAL OR :NEW.SAL > MAXSAL THEN
    RAISE APPERR;
    END IF;

    EXCEPTION
    WHEN APPERR THEN
    RAISE_APPLICATION_ERROR(-20001,' SALARY MUST BE GREATER THAN '||MINSAL||' AND LESS THAN '||MAXSAL);
    END;
    /

    SQL> START TRG1

    Trigger created.

    SQL> INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,DEPTNO) VALUES (1111,'POPO','CLERK',2500,20);
    INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,DEPTNO) VALUES (1111,'POPO','CLERK',2500,20)
    *
    ERROR at line 1:
    ORA-20001: SALARY MUST BE GREATER THAN 800 AND LESS THAN 1300
    ORA-06512: at "BENLY.SALCHK", line 17
    ORA-04088: error during execution of trigger 'BENLY.SALCHK'


    SQL> UPDATE EMP SET SAL=2000 WHERE EMPNO=7369 AND JOB='CLERK';
    UPDATE EMP SET SAL=2000 WHERE EMPNO=7369 AND JOB='CLERK'
    *
    ERROR at line 1:
    ORA-04091: table BENLY.EMP is mutating, trigger/function may not see it
    ORA-06512: at "BENLY.SALCHK", line 6
    ORA-04088: error during execution of trigger 'BENLY.SALCHK'

    The trigger is created but the problem is its showing the message as shown above if you do insert or update.

    if possible kindly rectify the problem and give me some tips of table mutating becuase its comming in almost all trigger what i am writing

    thanks in advance
    regards
    bins

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

  3. #3
    Join Date
    Dec 2001
    Location
    Brazil
    Posts
    282

    Wink Table Mutating


    Hi Beejay,
    The problem is simple. In line triggers (triggers you create
    using the FOR EACH ROW sentence) you cannot use DML
    (Select, Delete, Insert or Update) commands if the table
    you`re using DML is the table the trigger runs the action.
    Your code:

    --you are creating a 'line trigger' for the table EMP
    CREATE OR REPLACE TRIGGER SALCHK
    BEFORE INSERT OR UPDATE ON EMP FOR EACH ROW

    -- you are trying to use SELECT (DML command) with
    the same table that the trigger uses.
    SELECT MAX(SAL),MIN(SAL) INTO MAXSAL,MINSAL FROM EMP

    Try doing this: Create a package with a type of table variable
    and do this select there. And then use the information held in this variable in the 'trigger for each row'. If you don`t know how to do it, answer me and I`ll give you the code.

    I hope you can do it.
    F.

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Table Mutating

    Originally posted by Mnemonical

    In line triggers (triggers you create
    using the FOR EACH ROW sentence) you cannot use DML
    (Select, Delete, Insert or Update) commands if the table
    you`re using DML is the table the trigger runs the action.
    It's called row trigger, not line trigger but that doesn't matter...

    Additionnaly the trigger cannot modify the table to which a changing column references via primary, foreign or unique constraint.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You can however write two functions. One that returns the low value and one that returns the high value. You get around the mutating table by declaring PRAGMA AUTONOMOUS_TRANSACTION; in your functions. I would not recommend doing this though. Wouln't it be better to create a table storing the minimum and maximum salaries? You could have it broken down by job categories. And Since you would be returning data from a table other than the table that you are inserting into or updating you would not have a mutating table.


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