这是问题所在：
创建一个触发器,以防止对任何特定类中低于2.5的整体平均成绩的采用关系进行任何更改.注意：此触发器不是为了解决任何给定学生的平均GPA,而是应该针对特定班级中指定的所有成绩的平均成绩.
这是架构：
Student-schema =(studentnum,name,standing,gpa,major) Class-schema = (schedulenum,semester,department,classnum,days,time,place,enrollment) Instructor-schema = (name,office) Teaches-schema = (name,schedulenum,semester) Taking-schema = (studentnum,grade)
我在这些触发器上度过了一段可怕的时光,但这是我努力做到这一点：
CREATE OR REPLACE TRIGGER stopChange
AFTER UPDATE OR INSERT OR DELETE ON taking
REFERENCING OLD AS old
NEW AS new
FOR EACH ROW
DECLARE
grd_avg taking.grade%TYPE;
BEGIN
SELECT AVG(grade)
INTO grd_avg
FROM taking
WHERE studentnum = :new.studentnum
AND schedulenum = :new.schedulenum
AND semester = :new.semester;
IF grd_avg < 2.5 THEN
UPDATE taking
SET grade = :old.grade
WHERE studentnum = :old.studentnum
AND schedulenum = :old.schedulenum
AND semester = :old.semester;
END IF;
END;
/
ERROR at line 1: ORA-04091: table TAKING is mutating,trigger/function may not see it ORA-06512: at "STOPCHANGE",line 6 ORA-04088: error during execution of trigger 'STOPCHANGE'
任何建议？我正在使用Oracle.
解决方法
我认为您可以通过将其重写为前触发器而不是后触发器来解决此问题.但是,对于插入和删除,这可能有点复杂.这个想法是：
CREATE OR REPLACE TRIGGER stopChange
BEFORE UPDATE OR INSERT OR DELETE ON taking
REFERENCING OLD AS old
NEW AS new
FOR EACH ROW
DECLARE
grd_avg taking.grade%TYPE;
BEGIN
SELECT (SUM(grade) - oldgrade + new.grade) / count(*)
INTO grd_avg
FROM taking
WHERE studentnum = :new.studentnum
AND schedulenum = :new.schedulenum
AND semester = :new.semester;
IF grd_avg < 2.5 THEN
new.grade = old.grade
END IF;
END;
