Trigger mutating error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Trigger mutating error

  1. #1
    Join Date
    Jun 2005
    Posts
    7

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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!
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool

    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

  4. #4
    Join Date
    Feb 2005
    Posts
    158
    And you'll need to take the COMMIT out of the trigger too.

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width