Results 1 to 5 of 5

Thread: Mutating table

  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Mutating table

    I have made a simple update-trigger, which never really does anything, but still it fails, when I try to update the table.

    PHP Code:
    create or replace trigger OWNERS_upd1 
    before update on OWNERS
    for each row
    declare
      
    counts      number;
    begin
      select count
    (*) into counts from OWNER where NAME = :new.NAME;
    end;

    Actually I need the counted value for making an update in another table, but since i fails even before I make any updates at all, I need to solve that problem first.
    Last edited by kfc@vd.dk; 03-15-07 at 09:14.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but since i fails even before I make any updates at all, I need to solve that problem first.
    FAILS? What failure? I don't see any failure?
    Exactly what problem are we supposed to solvefor you if you provide NO clues?
    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
    Jun 2003
    Posts
    81
    My title is MUTATING TABLE. I get the Oracle-error:
    ORA-04091 table string.string is mutating, trigger/function may not see it

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I hope you're aware of the side effects of triggers.

    To fix your problem, add

    pragma autonomous_transaction

    in your declarative section of the trigger. Problem is that while you're updating, the trigger may or not you're change.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Beware that if you use AUTONOMOUS_TRANSACTION, your trigger will not be aware of any changes you have made to the table yourself in the current transaction. Usually that is not desirable and you should instead follow Tom Kyte's advice.

Posting Permissions

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