Creating Database Trigger
I am trying to create a database trigger to update a value entered by the user. The trigger compiles fine but I keep getting error messages when I test the trigger. Below is the error message I am getting and the code I am trying to use.
ORA-04091: table SCOTT.EMP2 is mutating, trigger/function may not see it
CREATE OR REPLACE TRIGGER Add_Any_Emp
AFTER INSERT ON EMP
FOR EACH ROW
DECLARE
v_SAL EMP2.SAL%TYPE;
BEGIN
v_SAL := :NEW.SAL;
IF v_SAL > 20000 THEN
LOOP
UPDATE EMP
SET SAL = 20000
WHERE EMP.EMPNO = :NEW.EMPNO;
END LOOP;
END IF;
END;
/
How doI overcome this?
Software/Hardware used:
Software/Hardware used:
ASKED:
December 23, 2004 4:27 AM
UPDATED:
January 5, 2005 7:00 AM
Answer Wiki:
v_SAL EMP2.SAL%TYPE;
BEGIN
v_SAL := :NEW.SAL;
IF v_SAL > 20000 THEN
LOOP
UPDATE EMP
SET SAL = 20000
WHERE EMP.EMPNO = :NEW.EMPNO;
END LOOP;
END IF;
Not knowing Oracle, here is my guess
instead of SET SAL = how about SET NEW.SAL =
To see all answers submitted to the Answer Wiki: View Answer History.
It may be because of the loop statement. There is no reason for the loop and it would in fact make an infinite loop because there is nothing making the loop exit.
I am guessing that empno is the PK of EMP. If yes you do not need a LOOP statement or an update statement. All you need is your if test and :NEW.SAL = 2000.
…
v_SAL := :NEW.SAL;
IF v_SAL > 20000 THEN
:NEW.SAL = 20000
END IF;
Also, the trigger should be a BEFORE (not AFTER) insert trigger as you want to change the value of SAL before the insert is actually performed and not have to perform an update at all – guessing this causes the mutating error.
Hope that makes sense!