SQL/Oracle 10g - Issue with triggers

Question

I'm having an issue with SQL/Oracle 10g when it comes to create a statement level trigger. What I'm trying to achieve with this trigger is to delete a practical class when enrollment of students is < 5. Below is what I've written:

CREATE OR REPLACE TRIGGER delete_prac
AFTER UPDATE ON studEnrol
BEGIN
   DELETE FROM pracList
   WHERE Practical IN (
   SELECT Practical
   FROM studEnrol
   GROUP BY Practical
   HAVING COUNT(Practical) < 5);
END delete_prac;

The error I'm getting however is:

ORA-04091: pracList table is mutating, trigger/function may not see it

I've looked into this error and the suggestions that arose were to include the :new and :old keywords, but I don't know how I would do that.

Any help is appreciated!

Thanks.

EDIT: Forgot to add which table was mutating, its the pracList table EDIT2: Changed it to a statement level trigger and the mutating table still occurs with pracList

do you have another triggers on pracList?

Florin Ghita
– Florin Ghita

2012-03-20 09:38:14 +00:00
Commented Mar 20, 2012 at 9:38 — Florin Ghita
– Florin Ghita, Commented Mar 20, 2012 at 9:38
show ddl on tables: pracList, studEnrol

turbanoff
– turbanoff

2012-03-20 10:17:17 +00:00
Commented Mar 20, 2012 at 10:17 — turbanoff
– turbanoff, Commented Mar 20, 2012 at 10:17

Ben · Accepted Answer · 2012-03-20 11:37:32Z

This probably occurs as Florin Ghita suggests because you have a trigger on praclist. When you update studenrol you then try to delete from praclist. This act fires another trigger, which causes your problem. This error is raised in order to provide a read consistent view of the data. As Tom Kyte says this error could be indicative of a flaw in your schema or logic, and you should reconsider one or both.

Also, and generally speaking, full table scans, which is what you're doing, in a trigger are A Bad Idea ^TM. Depending on the size of the table you might end up spending 1 second or 2 hours on each and every update on studlist, which is far from optimal.

Without knowing your schema or what the trigger on praclist does it's difficult to be certain, however, in your case, there seems to be an "out". Drop this trigger entirely; there is no need for it.

Create a materialized view on praclist to provide you the data you need. It doesn't make sense anyway to be constantly deleting and inserting values into praclist when you can just have a view that keeps the information in here constantly up-to-date. The following query should do it:

select p.*
  from praclist p
  join ( select practical
           from studenrol
          group by practical
         having count(*) >= 5 ) s
    on p.practical = s.practical

Incidentally, your current trigger doesn't allow for an insert into praclist if the number of students enrolled suddenly becomes enough for a practical to take place. A materialized view takes care of this problem as well as the mutating table error.

turbanoff · Accepted Answer · 2012-03-20 09:19:01Z

1

remove FOR EACH ROW

Then your trigger will run only once after update rows, and the table will not be mutating.

answered Mar 20, 2012 at 9:19

turbanoff

2,5096 gold badges43 silver badges103 bronze badges

1 Comment

user1280446 Over a year ago

Doesn't work. I tried changing a students enrollment in the studEnrol table from one practical to another and the error still occurs. For example, 5 students were enrolled in physics, then I edit one student who was doing physics to do maths and the trigger should delete the practical from the praclist.

Collectives™ on Stack Overflow

SQL/Oracle 10g - Issue with triggers

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related