Problem with mutating table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Problem with mutating table

  1. #1
    Join Date
    Mar 2003
    Posts
    5

    Talking Problem with mutating table

    Hi everybody.

    Ive met a curious error while trying to fire a procedure from several triggers.

    The statement is quite easy... All I want is to fire a procedure that tracks al the rows in a table that have the same value in a certain field that the row/s modified (inserted,updated or deleted).

    So... Ive made three after triggers for each row in the table so if any row changes in the table, a procedure that makes a query to view what rows whith this special value are left in the table is fired.

    This has generated an ORA-04091 error, alerting that the table must be mutating and my procedures/triggers may not view it. Ive readed in some internet database sites where people says that this problem will be fixed making before triggers instead of after ones.

    Ive tried but... however there is no error, my procedure does not retrieve all information, because it doesnt view the last row changing (inserted, updated or deleted) in the table.

    Any suggestion?
    Thanks in advance.
    Bardo.

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Mutating table problem = bad design problem, IMHO.
    Build the procedure into a background job and let the triggers notify the job about the values.
    Use dbms_alert or dbms_pipe to notify the job.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Sep 2001
    Posts
    37

    Re: Problem with mutating table

    Originally posted by Bardo

    So... Ive made three after triggers for each row in the table so if any row changes in the table, a procedure that makes a query to view what rows whith this special value are left in the table is fired.

    This has generated an ORA-04091 error, alerting that the table must be mutating and my procedures/triggers may not view it.
    This could be happening because you procedure is using DML statements that includes the table that fires the trigger.

    If you wanna reference some value for a column in the row of the table that actually fires the trigger, you can use the ld and :new keywords to get it.

    HTH

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Start using Autonomous transaction that solves mutating table error.

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