mutating trigger problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: mutating trigger problem

Hybrid View

  1. #1
    Join Date
    Apr 2014
    Posts
    1

    mutating trigger problem

    I create a trigger which is now giving a mutating error.
    Here is the trigger:
    CREATE GLOBAL TEMPORARY TABLE gtt_ai_fin_transaction
    (
    transaction_nbr number null,
    gl_date date null,
    transaction_amount number(18,6) null,
    discount_taken_amount number(18,6) null,
    after_balance_amount number(18,6) null,
    after_status char(1) null
    )
    ON COMMIT DELETE ROWS
    tablespace temp
    /

    CREATE or replace TRIGGER ai_fin_transaction
    AFTER INSERT ON fin_transaction
    REFERENCING NEW AS NEW
    FOR EACH ROW
    DECLARE TransactionNumber number;
    TransactionAmount number(18,6);
    DiscountTakenAmount number(18,6);
    AfterBalanceAmount number(18,6);
    InvoiceNbr number;

    CURSOR transactions IS
    WITH MaxGLdate (invoice_nbr,gl_date) AS
    (SELECT ft.invoice_nbr,
    MAX(COALESCE(ft.gl_date,ft.transaction_date))
    FROM fin_transaction ft
    WHERE ft.invoice_nbr = :new.invoice_nbr
    GROUP BY ft.invoice_nbr
    )
    SELECT :new.invoice_nbr
    FROM dual
    JOIN MaxGLdate mgld
    ON :new.invoice_nbr = mgld.invoice_nbr
    WHERE :new.gl_date < mgld.gl_date;

    CURSOR recalculate IS
    SELECT transaction_nbr,
    transaction_amount,
    discount_taken_amount
    FROM gtt_ai_fin_transaction
    ORDER BY gl_date,
    transaction_nbr;

    BEGIN

    IF (:new.invoice_nbr IS NOT NULL) THEN

    UPDATE fin_invoice
    SET last_transaction_date = :new.transaction_date
    WHERE invoice_nbr = :new.invoice_nbr;

    END IF;

    IF (:new.payment_nbr IS NOT NULL) THEN

    UPDATE fin_payment
    SET last_transaction_date = :new.transaction_date
    WHERE payment_nbr = :new.payment_nbr;

    END IF;

    OPEN transactions;

    LOOP

    FETCH transactions INTO InvoiceNbr;
    EXIT WHEN transactions%NOTFOUND;

    DELETE FROM gtt_ai_fin_transaction;

    INSERT INTO gtt_ai_fin_transaction
    SELECT transaction_nbr,
    COALESCE(gl_date,transaction_date),
    CASE WHEN transaction_type IN ('PAY','CON','CRD','PAD','ADJ','ACC')
    THEN transaction_amount * -1
    ELSE transaction_amount
    END,
    CASE WHEN transaction_type IN ('PAY','CON','CRD','PAD','ADJ','ACC')
    THEN discount_taken_amount * -1
    ELSE discount_taken_amount
    END,
    0.00,
    NULL
    FROM fin_transaction
    WHERE invoice_nbr = InvoiceNbr
    AND payment_nbr IS NOT NULL
    ORDER BY COALESCE(gl_date,transaction_date),
    transaction_nbr;

    SELECT after_balance_amount
    INTO AfterBalanceAmount
    FROM fin_transaction
    WHERE invoice_nbr = InvoiceNbr
    AND payment_nbr IS NULL;

    OPEN recalculate;

    LOOP

    FETCH recalculate INTO TransactionNumber,
    TransactionAmount,
    DiscountTakenAmount;
    EXIT WHEN recalculate%NOTFOUND;

    AfterBalanceAmount := AfterBalanceAmount + TransactionAmount + DiscountTakenAmount;

    UPDATE gtt_ai_fin_transaction
    SET after_balance_amount = AfterBalanceAmount,
    after_status = CASE WHEN AfterBalanceAmount = 0.00
    THEN 'C'
    ELSE 'O'
    END
    WHERE transaction_nbr = TransactionNumber;

    END LOOP;

    UPDATE fin_transaction
    SET fin_transaction.after_balance_amount =
    (SELECT after_balance_amount
    FROM gtt_ai_fin_transaction
    WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr
    ),
    fin_transaction.after_status =
    (SELECT after_status
    FROM gtt_ai_fin_transaction
    WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr
    )
    WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr;

    END LOOP;

    END;

    The trigger compiles ok with no errors;
    When I try to test it using this data:
    insert into fin_transaction (TRANSACTION_NBR,invoice_nbr,transaction_amount,transaction_type,transaction_date,after_balance_amou nt,after_status,financial_source)
    values (319,999,10000.00,'INV',SysDate,10000.00,'O','A/R')
    ;
    I get this error:
    Error starting at line : 1 in command -
    insert into fin_transaction (TRANSACTION_NBR,invoice_nbr,transaction_amount,transaction_type,transaction_date,after_balance_amou nt,after_status,financial_source)
    values (319,999,10000.00,'INV',SysDate,10000.00,'O','A/R')
    Error report -
    SQL Error: ORA-04091: table GRAIND16.FIN_TRANSACTION is mutating, trigger/function may not see it
    ORA-06512: at "GRAIND16.AI_FIN_TRANSACTION", line 8
    ORA-06512: at "GRAIND16.AI_FIN_TRANSACTION", line 47
    ORA-04088: error during execution of trigger 'GRAIND16.AI_FIN_TRANSACTION'
    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.

    I can run the SQL statements individually and do not have any problems.
    I have examined the trigger and, for the life of me, I don't see where the problem is.
    Can anyone assist me???

    Thanks
    Murray

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    ORA-04088: error during execution of trigger 'GRAIND16.AI_FIN_TRANSACTION'
    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.
    The error message is telling you everything that you need to know. You should not use a trigger for
    this because in a row by row trigger the only data that you can look at it the data for the one row
    your are affecting at that moment. Generally with a temporary table you load the table with data,
    then you run some process against all of the data in the temp table.

    So you need to change the procedure to not be a trigger, load all of the data that you want, then
    manipulate the data finally commit. When you commit all of the data will go away. An example on
    using a global temporary table would be aggregate the changes to the table in another table.
    You would have an after insert by row trigger that would insert all of the data into a temporary
    table, then an after statement trigger would aggregate the data and insert into a aggregated table
    and when you commit the data in the temp table would be deleted.

    IMHO, You need to go back and think about what you are really trying to do and find a simpler way to do it.
    Also even if it did work, it would be horribly inefficient.
    this space intentionally left blank

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