Home » SQL & PL/SQL » SQL & PL/SQL » Help with a Trigger (Mutating Trigger)
Help with a Trigger (Mutating Trigger) [message #218574] Thu, 08 February 2007 13:48 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I have this simple trigger that calculates avg score and updates it into another table. But I'm running into a "ORA-04091 table string.string is mutating, trigger/function may not see it" error. I've tried several ways to get around it but still can't get it to work.

CREATE OR REPLACE TRIGGER update_review_scores_trg
BEFORE
INSERT OR DELETE OR UPDATE
ON review
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
v_proposal_id number;
v_avg_score float;
begin
select a.proposal_id into v_proposal_id
from assignment a
where a.assignment_id = :old.assignment_id;

select avg(b.score) into v_avg_score
from assignment a, review b
where a.assignment_id = b.assignment_id
and a.proposal_id = v_proposal_id;

update proposal set review_avg_score = v_avg_score
where proposal_id = v_proposal_id;

end;
/

Thanks in advance!!
Re: Help with a Trigger (Mutating Trigger) [message #218616 is a reply to message #218574] Fri, 09 February 2007 00:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Google or search this forum for mutating table. There are zillions of examples out there.
Re: Help with a Trigger (Mutating Trigger) [message #218705 is a reply to message #218616] Fri, 09 February 2007 07:17 Go to previous messageGo to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi,
Mutatating trigger problem is solved by number of ways.

But the simple solution i find is using PRAGMA autonomous_transaction;

Only user statement PRAGMA autonomous_transaction; in declare section then problem will be solved.

Other solution what i know by using PL/SQL table. But it is somewhagt complicated to implement.

Thanks,
Amol
Re: Help with a Trigger (Mutating Trigger) [message #218713 is a reply to message #218705] Fri, 09 February 2007 09:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use autonomous transaction in a trigger??
What if the actual transaction gets rolled back? The new average then is based on the inserted, yet rolled back row.

I think it is safe to stick to this mantra:
Don't use autonomous transaction, except for logging.

To solve the mutating table problem, use the package-after statement trigger combination solution. It is THE standard solution.
Better yet: check if your design is correct. Mutating table problems often are the result of a buggy design.
Re: Help with a Trigger (Mutating Trigger) [message #218718 is a reply to message #218713] Fri, 09 February 2007 09:41 Go to previous message
lowcash75
Messages: 67
Registered: February 2006
Member
I ended up going with a view approach. That's a better way of doing things rather than calculating averages on the fly and then storing them in a table.

Thanks!

[Updated on: Fri, 09 February 2007 09:41]

Report message to a moderator

Previous Topic: Help needed
Next Topic: Need help for Query
Goto Forum:
  


Current Time: Mon Dec 05 15:16:12 CST 2016

Total time taken to generate the page: 0.15292 seconds