Trigger mutating error

[JHill]

Hello,

I am setting up a trigger in Oracle 9i to do the following. Everytime a field PIPE_DIA is updated in table SP_SEWGPIPE, i want it to take the CSAREA value from a lookup table LU_SIZE and populate SP_SEWGPIPE.CSAREA with that same value. However, when i do the following trigger, i keep getting an error in line 3 stating that "SP_SEWGPIPE is mutating". I have the following:

begin

UPDATE SP_SEWGPIPE
SET CSAREA =
(SELECT CSAREA
FROM LUSIZE
WHERE PIPE_DIA = :new.PIPE_DIA;
commit;

end;

Any suggestions?

Thanks,
jas

[slimdave]

Hmmm, this seems to be a design problem relating to denormalization. The CSAREA is dependent upon PIPE_DIA, right? I know that it's not directly answering your question, but is there a reason why you cannot just calculate the CSAREA dynamically from the PIPE_DIA each time you need to?

By the way, the idea of an Oracle message telling you that "your sewage pipe is mutating"? Great!

[LKBrwn_DBA]

Try this:

SELECT CSAREA INTO :new.CSAREA 
  FROM LUSIZE 
 WHERE PIPE_DIA = :new.PIPE_DIA;

[gamyers]

And you'll need to take the COMMIT out of the trigger too.

[TimHall]

Hi.

It looks like the guys have solved you problem, but sometimes you can have issues that are not easily solved by rearranging the query. In these cases you might need to use a combination of row and statement level triggers to get round the mutation. This method is explained here:

http://www.oracle-base.com/articles/...Exceptions.php

Remember to try your alternatives first.

Cheers

Tim...