Mutating Table issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Mutating Table issue

Hybrid View

  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Mutating Table issue

    I have been trying to solve an issue where I need to capture a new record in one table on insert and create another row in the same table with some of the data from the inserted record.

    For example 4 fields of data from the inserted record will be stored in a new record in the same table. I also need a trigger that will run on delete where a row is deleted, another row in the same table will be deleted based upon data within the row that was orignally deleted and caused my trigger to fire.

    I keep running into the mutating error when I try to build this out and cannot seem to find a way around it. Any suggestions or examples?

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Are you getting ORA-04091?

    oerr ORA 04091
    04091, 00000, "table %s.%s is mutating, trigger/function may not see it"
    // *Cause: A trigger (or a user defined plsql function that is referenced in
    // this statement) attempted to look at (or modify) a table that was
    // in the middle of being modified by the statement which fired it.
    // *Action: Rewrite the trigger (or function) so it does not read that table.

    http://asktom.oracle.com/tkyte/Mutate/index.html
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2007
    Posts
    2
    I did try this but for some reason with all 3 triggers I now get no errors but also no new row inserted into the table other than the orignal which should have cause the trigger to fire.

    So I am at a loss.

    I would like that if table A has a record inserted, values from column 1,2,3,4,5 are used with a new insert into the same table (Table A).

  4. #4
    Join Date
    May 2000
    Location
    Richardson, Texas, USA
    Posts
    39

    bad design?

    why do you need to insert the values into the same table? it does seem like a bad design to me.

    may be you should try to insert the values into another table.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by ssu4716
    why do you need to insert the values into the same table? it does seem like a bad design to me.

    may be you should try to insert the values into another table.
    That would certainly improve the sloppy design LOL
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    I have to agree that it sounds like bad design, but here's a method for dealing with that error if you have no other way out:

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

    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

  7. #7
    Join Date
    Feb 2005
    Posts
    158
    Quote Originally Posted by froth
    I would like that if table A has a record inserted, values from column 1,2,3,4,5 are used with a new insert into the same table (Table A).
    But that triggered insert is also an insert into table A, so would fire the trigger, resulting in another insert ad infinitum....Unless you have shares in a company making disk drives, this doesn't seem like a good idea.

    How about you tell us what the APPLICATION is trying to achieve.

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