CREATE OR REPLACE TRIGGER LOAN_RP
AFTER INSERT OR UPDATE ON FUND_DISBURSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_FA FUND_DISBURSE.FUND_AMT%TYPE;
V_RC NUMBER (10);
V_AC FUND_DISBURSE.AREA_CODE%TYPE;
V_BC FUND_DISBURSE.BRANCH_CODE%TYPE;
V_CC FUND_DISBURSE.COMP_CODE%TYPE;
V_DN FUND_DISBURSE.DAFA_NO%TYPE;
V_PRN NUMBER (15,2);
V_SC NUMBER (15,2);
V_INSN NUMBER (2) :=0;
V_INSD FUND_DISBURSE.DISBURSE_DATE%TYPE;
V_RAMT NUMBER (15,2);
BEGIN
select AREA_CODE, BRANCH_CODE, COMP_CODE,DAFA_NO,DISBURSE_DATE,FUND_AMT
into
V_AC,V_BC,V_CC,V_DN,V_INSD,V_FA
from FUND_DISBURSE
WHERE AREA_CODE = :NEW.AREA_CODE
and BRANCH_CODE = :NEW.BRANCH_CODE
and COMP_CODE = :NEW.COMP_CODE
and DAFA_NO = :NEW.DAFA_NO
AND DISBURSE_DATE =:NEW.DISBURSE_DATE
AND FUND_AMT = :NEW.FUND_AMT;
V_RAMT := V_FA*1.13;
V_INSD := (V_INSD-7) + 14;
for i in 1..50 loop
V_INSD := V_INSD+7;
V_RC := V_FA/1000*25;
V_PRN := V_RC/1.13;
V_SC := V_RC - V_PRN;
V_INSN := V_INSN + 1;
V_RAMT := V_RAMT - V_RC;
DBMS_OUTPUT.PUT_LINE(V_RC);
INSERT INTO F_REPAY_SCHEDULE (AREA_CODE,BRANCH_CODE, COMP_CODE,
DAFA_NO,INSTALL_NO,INSTALL_DATE,INSTALL_AMT, PRN_AMT, SC_AMT)
VALUES (:new.area_code,:new.branch_code,:new.comp_code,:new.dafa_no,V_INSN, V_INSD,
V_RC,
V_PRN,V_SC );
EXIT WHEN V_RAMT < V_RC;
END LOOP;
END;
/
Trigger complied, but can not insert any record.
table is mutating, trigger/function may not see it
How can I solve it. Please suggest.
Sorry again. Thanks to ***mat and all* for helping me**.
I have understood.
Here my new code after correction.
CREATE OR REPLACE TRIGGER LOAN_RP
AFTER INSERT OR UPDATE ON FUND_DISBURSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RC NUMBER (10);
V_PRN NUMBER (15,2);
V_SC NUMBER (15,2);
V_INSN NUMBER (2) :=0;
V_RAMT NUMBER (15,2);
v_insd date;
BEGIN
V_RAMT := :NEW.FUND_AMT*1.13;
V_INSD := (:NEW.DISBURSE_DATE -7 ) + 14;
LOOP
V_INSD := V_INSD+7;
V_RC := :NEW.FUND_AMT/1000*25;
V_PRN := V_RC/1.13;
V_SC := V_RC - V_PRN;
V_INSN := V_INSN + 1;
V_RAMT := V_RAMT - V_RC;
DBMS_OUTPUT.PUT_LINE(V_RC);
INSERT INTO F_REPAY_SCHEDULE (AREA_CODE,BRANCH_CODE, COMP_CODE,
DAFA_NO,INSTALL_NO,INSTALL_DATE,INSTALL_AMT, PRN_AMT, SC_AMT)
VALUES (:new.area_code,:new.branch_code,:new.comp_code,:new.dafa_no,V_INSN, V_INSD,
V_RC, V_PRN,V_SC);
EXIT WHEN V_RAMT < V_RC;
END LOOP;
END;
/
Best Answer
the code erro ORA-04091 is relative the transaction. In a trigger you dont can execute select, unless you declare a new transacation with clause PRAGMA AUTONOMOUS_TRANSACTION.
remember to run a commit or rollback to finalize transaction on the trigger.