ORA-04091: table is mutating

26 Views Asked by At

I want to create a trigger that it will update the total_price after insert a record. The trigger will invoke a procedure , then the procedure will invoke a function. Here are the function, procedure and trigger coding:

--Function of Finding total_price

Create or replace FUNCTION invoice_total_price_function
(p_invoice_num IN invoice_line.invoice_num%TYPE)
RETURN NUMBER
IS
v_total_price NUMBER :=0;
BEGIN
        SELECT SUM(price * order_quantity)
        INTO v_total_price
        FROM book, invoice_line
        WHERE invoice_line.isbn_code = book.isbn_code
        AND invoice_num = p_invoice_num;
        RETURN v_total_price;
    END invoice_total_price_function;

--Procedure of update invoice's total price

Create or replace PROCEDURE update_total_price_procedure
    (p_invoice_num IN invoice.invoice_num%TYPE)
    IS
    BEGIN
        UPDATE invoice
        SET total_price = invoice_total_price_function(p_invoice_num)
        where invoice_num = p_invoice_num;
    END update_total_price_procedure;

-- Trigger after insert a invoice line

CREATE OR REPLACE TRIGGER update_total_price_trigger
    AFTER INSERT OR UPDATE ON Invoice_line
    FOR EACH ROW
BEGIN
    update_total_price_procedure(:new.invoice_num);
END;

All the function, procedure and trigger can be created normally, but there are some error to restrict me from inserting:

ORA-04091: table SQL_SOOXEZEDQXOAMIDHOXAYZAFQM.INVOICE_LINE is mutating, trigger/function may not see it
ORA-06512: at "SQL_SOOXEZEDQXOAMIDHOXAYZAFQM.INVOICE_TOTAL_PRICE_FUNCTION", line 7
ORA-06512: at "SQL_SOOXEZEDQXOAMIDHOXAYZAFQM.UPDATE_TOTAL_PRICE_PROCEDURE", line 5
ORA-06512: at "SQL_SOOXEZEDQXOAMIDHOXAYZAFQM.UPDATE_TOTAL_PRICE_TRIGGER", line 2
ORA-06512: at "SYS.DBMS_SQL", line 1721
0

There are 0 best solutions below