表正在发生变化，触发器/函数可能看不到它(阻止平均成绩低于 2.5)

2022-01-01 00:00:00 sql oracle triggers

问题来了:

创建一个触发器，以防止任何会导致任何特定班级的整体平均成绩低于 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;

相关文章