table mutating...

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

http://govt.oracle.com/~tkyte/Mutate/index.html

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.

Quote:

---

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.

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.