Table is mutating, trigger/function may not see it (stopping an average grade from dropping below 2.5)

Question

Here's the problem:

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.

Here's the schema:

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'

Any advice? I'm using Oracle.

Answer 1

First you need to read about triggers, mutating table error and compound triggers: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS2005

Your trigger is AFTER UPDATE OR INSERT OR DELETE. Means if you run UPDATE OR INSERT OR DELETE statements on this table, the trigger will fire. But you are trying to update the same table again inside your trigger, which is compl. wrong. This this is why you are getting the error. You cannot modify the same table the trigger is firing on. The purpose of trigger is to fire automatically when table is updated, inserted or deleted in your case. What you need is some procedure, not trigger.

Answer 2

use this statement inside DECLARE, it will work.

pragma autonomous_transaction;

Answer 3

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;  

Answer 4

I had the same issue and I noticed that if you do a select on the same table you put the trigger on you may/will get this problem. You can remove FOR EACH ROW or use the data in :New to do the calculation (if possible) and then make the update.

In your case it will make more sense to use separate table to have the avg_grade per semester.

Answer 5

Even we have ended up with same issue in our project. But after searching in few oracle forums, we have found the below solution.

1)Save the Old/New Column data in a temporary table as pat of row level trigger. 2) Write a statement level trigger and use the data saved in step 1.

This would solve the issue I think.

Answer 6

If you want retrieve other data by join other table(TABLE_ADDRESS). Here is my solution.

 CREATE OR REPLACE TRIGGER TRIGGER_TABLE_ACTIVITIES AFTER  INSERT ON TABLE_NAME
     FOR EACH ROW
    DECLARE 
    V_ADDRESS VARCHAR2(100); 
    BEGIN 

            SELECT A.ADDRESS INTO V_ADDRESS 
            FROM TABLE_ADDRESS A
            WHERE A.ADDRESSID = :NEW.ADDRESSID
            ;
            INSERT INTO TABLE_ACTIVITIES(
                            NAME, ADDRESS)
            VALUES(:NEW.NAME, V_ADDRESS);
    END;
    /