sql – 表正在变异,触发器/函数可能看不到它(停止平均等级从低于

创建一个触发器,以防止对任何特定类中低于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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!