How can I fix this mutating table from a trigger procedure

Question

This trigger will pass the inserted values ​​to the procedure, which will insert these values ​​into another table. I get an error with a table change. How can i fix this?

CREATE OR REPLACE TRIGGER ADD_INVOICE BEFORE INSERT OR UPDATE OF APP_NO,C_NO ON APPOINTMENT FOR EACH ROW DECLARE BEGIN POP_INVOICE(:NEW.APP_NO,:NEW.C_NO,:NEW.APP_DATE); END; / CREATE OR REPLACE PROCEDURE POP_INVOICE( I_APP_NO IN INVOICE.APP_NO%TYPE, I_C_NO IN INVOICE.C_NO%TYPE, I_INV_DATE IN INVOICE.INV_DATE%TYPE) AS CURSOR C_POP IS SELECT PRICE FROM TREATMENT T,APPOINTMENT A WHERE T.TRT_NO=A.TRT_NO AND A.APP_NO=I_APP_NO; V_BILL INVOICE.BILL%TYPE; BEGIN OPEN C_POP; FETCH C_POP INTO V_BILL; UPDATE INVOICE SET INV_NO=INV_IDSEQ.NEXTVAL, APP_NO=I_APP_NO, C_NO=I_C_NO, BILL=V_BILL, INV_DATE=I_INV_DATE; END; / 

Answer 1

The problem is caused by a link to a table with a trigger on it inside the trigger itself. Changing the procedure for accepting TRT_NO as a parameter eliminates the need to include APPOINTMENT in the query and therefore eliminates the exception of the mutating table. Depending on the number of records for each treatment, you can even include a cursor in your UPDATE statement.

I think this should do it, although I could not verify the database.

 CREATE OR REPLACE TRIGGER ADD_INVOICE BEFORE INSERT OR UPDATE OF APP_NO,C_NO ON APPOINTMENT FOR EACH ROW DECLARE BEGIN POP_INVOICE(:NEW.APP_NO,:NEW.C_NO,:NEW.APP_DATE,:NEW.TRT_NO); END; / 

Revised procedure:

 CREATE OR REPLACE PROCEDURE POP_INVOICE( I_APP_NO IN INVOICE.APP_NO%TYPE, I_C_NO IN INVOICE.C_NO%TYPE, I_INV_DATE IN INVOICE.INV_DATE%TYPE, I_TRT_NO IN APPOINTMENT.TRT_NO%TYPE ) AS CURSOR C_POP IS SELECT PRICE FROM TREATMENT T WHERE T.TRT_NO = I_TRT_NO; V_BILL INVOICE.BILL%TYPE; BEGIN OPEN C_POP; FETCH C_POP INTO V_BILL; CLOSE C_POP; INSERT INVOICE (inv_no, app_no, c_no, bill, inv_date) VALUES (INV_IDSEQ.NEXTVAL, I_APP_NO, I_C_NO, V_BILL, I_INV_DATE); END; /