Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Unanswered: Trigger: Table Mutating

    I am having table say ABC with columns
    ENTITY GLYEAR GLMONTH GLACC AMOUNT
    1000 2010 OPN 10001020 5000
    1000 2010 MAR 10001020 3000

    I CREATED TRIGGER as below

    CREATE OR REPLACE TRIGGER TRIG1
    AFTER INSERT ON ABC FOR EACH ROW
    DECLARE
    BEGIN
    IF (INSERTING) THEN
    IF :NEW.GLMONTH = 'MAR' THEN
    DELETE FROM TABLE ABC WHERE ENTITY = :NEW.ENTITY AND
    GLYEAR = :NEW.GLYEAR+1 AND
    GLMONTH = 'OPN';
    INSERT INTO ABC
    SELECT :NEW.ENTITY,
    :NEW.GLYEAR+1,
    'OPN' AS GLMONTH,
    GLACC,
    SUM(AMOUNT)
    WHERE ENTITY = :NEW.ENTITY AND
    GLYEAR = :NEW.GLYEAR
    GROUP BY :NEW.ENTITY, :NEW.GLYEAR+1, 'OPN', GLACC;
    END IF;
    END TRIG1;
    END IF;

    TRIGGER IS CREATED SUCCESSFULLY.
    BUT WHEN I AM GOING TO INSERT THERECORD IN ABC GIVES ERROR FOR TABLE IS MUTATING.
    04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"

  2. #2
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    vgurav,

    you have defined a trigger that fires when a record is inserted into the ABC table.

    Code:
    CREATE OR REPLACE TRIGGER TRIG1
    AFTER INSERT ON ABC FOR EACH ROW
    ...
    Part of the trigger code, however, is an insert into the ABC table (which would fire the AFTER INSERT trigger again, causing an insert, that would fire ...)

    Code:
    INSERT INTO ABC
    SELECT :NEW.ENTITY, 
    :NEW.GLYEAR+1,
    'OPN' AS GLMONTH,
    GLACC,
    SUM(AMOUNT)
    WHERE ENTITY = :NEW.ENTITY AND
    GLYEAR = :NEW.GLYEAR
    GROUP BY :NEW.ENTITY, :NEW.GLYEAR+1, 'OPN', GLACC;
    This would initiate eternal executions of this trigger (like a recursion without abort criterion).

    Oracle detects such situations (even when it's circular and more than one table is involved) and raises the ORA-04091 error.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >BUT WHEN I AM GOING TO INSERT THERECORD IN ABC GIVES ERROR FOR TABLE IS MUTATING.

    as a general rule in the trigger Oracle prevents SQL against table upon which the trigger is based.
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    as a general rule in the trigger Oracle prevents SQL against table upon which the trigger is based.
    This is only true for row level triggers.
    For a statement level trigger this is not true.
    Using a statement level trigger is usually the "workaround" for the mutating table problem.

    @vgurav: if you search the internet for that error message I'm sure you'll get millions of solutions to the problem.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by vgurav View Post
    I CREATED TRIGGER as below
    ...
    TRIGGER IS CREATED SUCCESSFULLY.
    I sincerely doubt so.
    • There's a FROM clause missing (in SELECT statement under INSERT).
    • No PL/SQL code ends with an END IF and compiles successfully.

    You should consider posting actual code (copy/paste of a SQL*Plus session would be fine) if you expect help.

    Furthermore, you are trying to handle only "IF :NEW.GLMONTH = 'MAR' ". What would you do with other GLMOTH values? Ignore them?

Posting Permissions

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