Results 1 to 2 of 2

Thread: Mutating Table

  1. #1
    Join Date
    Mar 2006
    Posts
    12

    Unanswered: Mutating Table

    The only solution for the problem of the 'MUTATING TABLE' (ORA-04091) from trigger, is creating two trigers?

    Problem:

    CREATE TABLE orders (order_id INTEGER, quantity DEC(17,2));

    CREATE OR REPLACE TRIGGER orders_after_insert
    AFTER INSERT
    ON orders
    FOR EACH ROW

    DECLARE
    m_count number;

    BEGIN

    SELECT COUNT(*)
    INTO m_count
    FROM orders
    WHERE order_id = :NEW.order_id

    END;
    .
    run

    SQL> INSERT INTO orders VALUES (1,1);

    ERROR en línea 1:
    ORA-04091: la tabla SCOTT.ORDERS está mutando, puede que el disparador/la
    función no puedan verla
    ORA-06512: en "SCOTT.TEST3", línea 11
    ORA-06512: en "SCOTT.ORDERS_AFTER_INSERT", línea 6
    ORA-04088: error durante la ejecución del disparador
    'SCOTT.ORDERS_AFTER_INSERT'


    Solution:


    CREATE TABLE orders (order_id INTEGER, quantity DEC(17,2));
    CREATE TABLE orders_tmp (order_id INTEGER, quantity DEC(17,2));

    CREATE OR REPLACE TRIGGER orders_after_insert
    AFTER INSERT
    ON orders
    FOR EACH ROW

    DECLARE
    v_quantity number;

    BEGIN

    INSERT INTO orders_tmp VALUES (:NEW.order_id, :new.quantity);

    END;
    .
    run


    CREATE OR REPLACE TRIGGER R1_1
    AFTER INSERT OR UPDATE
    ON orders
    DECLARE
    m_order_id INTEGER;
    m_count INTEGER;

    BEGIN

    SELECT COUNT(*)
    INTO m_count
    FROM orders
    WHERE order_id IN (SELECT order_id FROM orders_tmp);

    DELETE FROM orders_tmp;

    END;
    .
    run

    SQL> INSERT INTO orders VALUES (1,1);

    1 fila creada.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In your case, solution is in dropping the trigger as you don't need it. That is, if this code represents your actual situation and is not just an example.

    In other words, why do you need that trigger? You are selecting number of orders for that order_id and do nothing with it. If you do nothing, why do you have it?

Posting Permissions

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