Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    10

    Unanswered: After triggers modifying same table

    I wanted to use some triggers to maintain inverse relationships on a table, but get a ora-04091 error when doing so. I have a table that has two fields (role_id, excludes_role_id). After insert, update, or delete I want to maintain the inverse relationship automatically. Given that the error indicates Oracle doesn't allow such an action inside of a trigger, is there some way to get around this, so that it will be done automatically? Maybe a sneaky roundabout way through use of a view or some such?

    Thanks,
    Steve

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    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.
    Oracle is trying to protect fools from themselves.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2008
    Posts
    10
    Thanks, because I hadn't consulted the tubes before posting to find that out. I didn't ask that. Please try reading what is written. You have responded to both of my questions so far and contributed nothing useful(other than your mention that you have "hardcore" perl skills. That was helpful...). You have a high post count, which usually indicates ability. In your case it seems to indicate that you have a quota you need to reach per day.

    The same operation I am trying works perfectly fine in Postgres, and MSSQL. So given that two other major RDBMSs are able to perform this operation, it really seems like a shortcoming of Oracle.

    There is nothing dangerous about the operations I'm looking to perform if done intelligently. At best I could try and perform an insert for a pair that already exists, and it would fail because of the PK constraint. In my trigger though I also had a select query testing for the existence of just such a pair to avoid that problem.

    So the question still stands. Is there a way to work around this? This is a pretty trivial thing, and something that should be enforced by triggers. What would be the "Oracle" way to accomplish this?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com/tkyte/Mutate/index.html

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

    http://www.orafaq.com/wiki/PL/SQL_FA...ining_table.3F

    http://trubix.blogspot.com/2007/01/d...ing-table.html

    You need to keep in mind Oracle obsession with its read consistent rule.
    Oracle wants to present to each user a read consistent view of the data as it existed when his transaction started.
    When a trigger has been fired DML is happening against a/this table.
    What data should Oracle return to a SELECT against this table inside a trigger; the data before the DML or after the DML?
    Oracle is now confused & throw the ORA-04091 error.
    Last edited by anacedent; 03-21-08 at 00:52.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2008
    Posts
    10
    Excellent, thanks. I ended up going the package/multiple trigger route, since autonomous_transaction didn't seem like the proper fit. Your links also helped make it clear why Oracle makes it the way it does. Like you said, it's about Oracle trying to present a consistent view, and how a for each row could mess with the rest of the action. Thanks again, I appreciate the help.

Posting Permissions

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