-
Trigger mutating error
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
-
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!
-
Try this:
Code:
SELECT CSAREA INTO :new.CSAREA
FROM LUSIZE
WHERE PIPE_DIA = :new.PIPE_DIA;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
And you'll need to take the COMMIT out of the trigger too.
-
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...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|