触发编译很好但在更新时遇到ORA-04091

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

1.）我为一个名为grade的表编码了一个触发器，如下所示：

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:

它编译得很好。我的2-columned成绩表的一个例子如下：

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.

错误ORA-04091：表格GRADES正在变异，触发器/功能可能看不到它。重写触发器（或函数），使其不读取该表。

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:

2.）另外，我计划在我的触发器中包含另一个IF语句，以检查输入的新grade_percent是否在0和1之间。如果是，那么它应该将该十进制值转换为整个百分比值，例如：

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.

第二行应该将从0-1插入的任何值转换为整个百分比，例如：0.7转为70％并将其插入该行的该列。但是，这是一个语法错误，我不知道如何从这里开始。

Any help is greatly appreciated. Thank you.

任何帮助是极大的赞赏。谢谢。

1 个解决方案

#1

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;
/

#1