此触发器将插入的值传递给过程,该过程将这些值插入另一个表中.我收到了一个变异表错误.我怎样才能解决这个问题？
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; /
解决方法
问题是由触发器本身引用其上带触发器的表引起的.更改过程以接受TRT_NO作为参数,无需在查询中包含APPOINTMENT,因此将避免变异表异常.根据每个处理的记录数量,您甚至可以将光标合并到UPDATE语句中.
我认为应该这样做,虽然我无法检查数据库.
CREATE OR REPLACE TRIGGER ADD_INVOICE BEFORE INSERT OR UPDATE OF APP_NO,:NEW.APP_DATE,:NEW.TRT_NO); END; /
修订后的程序：
CREATE OR REPLACE PROCEDURE POP_INVOICE( I_APP_NO IN INVOICE.APP_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; /
