表正在变异,触发器/功能可能看不到(停止将平均等级降至2.5以下) [英] Table is mutating, trigger/function may not see it (stopping an average grade from dropping below 2.5)
问题描述
问题出在这里
创建一个触发器,以防止对取用关系进行任何更改,该更改会使任何特定班级的整体平均成绩下降到2.5以下.注意:此触发器并非旨在解决任何给定学生的平均GPA,而是应针对特定班级分配的所有年级的平均成绩.
Create a trigger that prevents any change to the taking relation that would drop the overall average grade in any particular class below 2.5. Note: This trigger is not intended to address the average GPA of any given student, but rather it should address the average grade for all grades assigned in a particular class.
以下是架构:
Student-schema =(studentnum, name, standing, gpa, major)
Class-schema = (schedulenum, semester, department, classnum, days, time, place, enrollment)
Instructor-schema = (name, department, office)
Teaches-schema = (name, schedulenum, semester)
Taking-schema = (studentnum, schedulenum, semester, grade)
这些触发器使我度过了一段糟糕的时光,但这是我为使这项工作而努力的尝试:
I'm having a terrible time with these triggers, but here's my attempt to make this work:
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;
/
我显然做错了,因为当我随后去更新或删除一个元组时,我得到了错误:
I'm obviously doing something wrong because when I then go to update or delete a tuple, I get the error:
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.
Any advice? I'm using Oracle.
推荐答案
我认为您可以通过将其重写为 before 触发器,而不是 after 触发器来解决此问题. .但是,这对于插入和删除可能有点复杂.这个想法是:
I think you can fix this by rewriting this as a before trigger, rather than an after trigger. However, this might be a little complicated for inserts and deletes. The idea is:
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;
这篇关于表正在变异,触发器/功能可能看不到(停止将平均等级降至2.5以下)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!