Oracle 8i triggers trouble: please help

From: Neil Zanella <nzanella_at_garfield.cs.mun.ca>
Date: Wed, 10 Oct 2001 20:46:19 -0230
Message-ID: <Pine.LNX.4.30.0110102029160.20542-100000@garfield.cs.mun.ca>

Hello,

I have created two tables student and courseTaken:

SQL> SELECT * FROM student;

 STUDENTID NAME GPA
---------- ---------- ----
 832467246 John Doe 0
 786764334 Joe Smith 0

SQL> SELECT * FROM courseTaken;

 STUDENTID CourseID Grade
---------- ---------- ------

 832467246         01      9
 832467246         02      5
 786764334         04      8

I wanted to write an Oracle 8i PL/SQL trigger so that each time a row is inserted into the courseTaken table the gpa is computed and is inserted into the student table. This is the first trigger I write and I have never used PL/SQL before. I also realize that using triggers is bad in general and that it is not necessary to write a trigger for this. Nevertheless I want to understand where the following code is wrong and why. Here it is:

CREATE TRIGGER calculate_gpa
AFTER INSERT ON courseTaken
FOR EACH ROW
DECLARE    average FLOAT;

BEGIN    SELECT AVG(grade) INTO average
   FROM courseTaken
   WHERE studentID = :new.studentID;

   UPDATE student
   SET gpa = average
   WHERE studentID = :new.studentID;

END;
/

Where did I go wrong. The trigger seems to compile fine but when I insert a new row into courseTaken nothing seems to happen. A variant of this was to use a subselect statement:

CREATE TRIGGER calculate_gpa
AFTER INSERT OF grade ON courseTaken
FOR EACH ROW
BEGIN    UPDATE student
   SET gpa = (SELECT AVG(grade)

              FROM courseTaken
              WHERE studentID = :new.studentID)

   WHERE studentID = :new.studentID;

END;
/

But this leads to RA-04091: the mutating table is being accessed by the AVG SQL function. Why is this a problem. I am only accessing the affected row after it is modified, not the whole table.

Thank you for your feedback,

Neil Received on Wed Oct 10 2001 - 18:16:19 CDT