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