Creating Database Trigger

0 pts.
Tags:
Development
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?
ASKED: December 23, 2004  4:27 AM
UPDATED: January 5, 2005  7:00 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 =

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    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.
    1,740 pointsBadges:
    report
  • AlexanderM
    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!
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following