Trigger Compiles Fine But Encounters ORA-04091 on UPDATE

Question

1.) I coded a trigger for a table called grades as follows:

CREATE OR REPLACE TRIGGER grades_before_update
BEFORE UPDATE OF grade_percent
ON grades
FOR EACH ROW
DECLARE
     grade_percent_var  NUMBER;
BEGIN
     SELECT  grade_percent
     INTO    grade_percent_var
     FROM    grades
     WHERE   student_id = :new.student_id;    

     IF (grade_percent_var > 100 OR grade_percent_var < 0) THEN
          RAISE_APPLICATION_ERROR(-20001, 'grade percent must be between 0 and 100');
     END IF;
END;
/

And it compiles fine. An example of my 2-columned grades table is as follows:

student_id  grade_percent 
1           70
2           85 
3           90
4           70

The problem comes in when I'm trying to test this trigger as follows:

UPDATE grades
SET grade_percent = 80
WHERE student_id = 1;

It gives me an

Error ORA-04091: table GRADES is mutating, trigger/function may not see it. Rewrite the trigger (or function) so it does not read that table.

2.) Also, I plan to include another IF statement in my trigger to check if the new grade_percent inputed is between 0 and 1. If yes, then it should convert that decimal value to whole percentage values for example:

UPDATE grades
SET grade_percent = 0.9
WHERE student_id = 2;

Here's what I have so far and I'm not sure how to write the second line of the code:

 IF (grade_percent_var > 0 OR grade_percent_var < 1) THEN
      INSERT(grade_percent_var * 100);

The second line should turn any values inserted from 0-1 into whole percents, for example: 0.7 turns into 70 percent and insert it into that column for that row. However, it's a syntax error and I'm not sure how to proceed from here.

Any help is greatly appreciated. Thank you.

fyi a lot of code in many other languages compiles but suffers from errors at runtime, so the "Compiles Fine But" part of your question isn't adding much information. e.g. "My Java program compiles fine but fails to open this file..." — William Robertson, Apr 22 '17 at 09:42

score 3 · Accepted Answer · edited Apr 21 '17 at 22:34

3

First, you don't need a trigger for this:

alter table grades add constraint chk_grades_grade_percent
    check (grade_percent between 0 and 100);

Second, you can just use the new value for the trigger -- if you have to use a trigger:

CREATE OR REPLACE TRIGGER grades_before_update
BEFORE UPDATE OF grade_percent
ON grades
FOR EACH ROW
BEGIN
     IF (:new.grade_percent > 100 OR :new.grade_percent < 0) THEN
          RAISE_APPLICATION_ERROR(-20001, 'grade percent must be between 0 and 100');
     END IF;
END;
/

edited Apr 21 '17 at 22:34

Alex Poole

183,384
11
179
318

answered Apr 21 '17 at 21:02

Gordon Linoff

1,242,037
58
646
786

There's a protocol that states I can't alter the table structures themselves. – 5120bee Apr 21 '17 at 21:07
I'm getting a PLS-0049: bad bind variable on 'new.grade_percent_var' also. – 5120bee Apr 21 '17 at 21:13
@5120bee - looks like the `_var` was just left in by accident, converting from your local variable name to the column name. Hopefully you figured that out already... – Alex Poole Apr 21 '17 at 22:33
Yeah, I figured that was it. This trigger compiles fine too but it still has the ORA-04091 table mutating error when testing it with a simple UPDATE query. – 5120bee Apr 21 '17 at 22:56
Just for readability I'd prefer `if :new.grade_percent not between 0 and 100`. For one thing it's closer to the error message. – William Robertson Apr 22 '17 at 09:38

Trigger Compiles Fine But Encounters ORA-04091 on UPDATE

1 Answers1