Re: Oracle 8i triggers trouble: please help

ORA-04091: table name is mutating, trigger/function may not see it Cause: A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being
modified by the statement that fired the trigger. Action: Rewrite the trigger or function so it does not read the table.

You may try to rewrite a trigger replace it by TRIGGER BEFORE. In this case there is a need to make appropriate calculations due to the absence of you record.

(average * (number of records in table Corse Taken for this student) + :new.grade )/(average + 1)

"Neil Zanella" <nzanella_at_garfield.cs.mun.ca> a écrit dans le message news: Pine.LNX.4.30.0110102029160.20542-100000_at_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 Thu Oct 11 2001 - 14:15:56 CDT