Geek asks and answers

The table is mutating, the trigger / function may not see it (mid-level stop from falling below 2.5)

Here's the problem:

Create a trigger that will prevent any changes in attitude that reduce the overall middle class in any particular class below 2.5. Note. This trigger is not intended to refer to the average GPA of any given student, but rather should belong to the middle class for all classes assigned in a particular class.

Here is the diagram:

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 have a terrible time with these triggers, but here is 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 obviously am doing something wrong, because when I go to update or delete a tuple, I get an 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 tips? I am using Oracle.

+10
source share
6 answers

, , , . . :

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;
+4

DECLARE, .

pragma autonomous_transaction;
+13

, : http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS2005

. UPDATE INSERT DELETE , . , . . . , . - , , ​​ . , .

+9

, , , , / . : , ( ), .

, avg_grade .

+2

. .

1) Old/New Column . 2) , 1.

, .

0

If you want other data, join another 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;
    /
0
source

More articles:


All Articles