Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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;
/
share|improve this question
    
What's the purpose of that select on fund_disburse? You already have the data in :new. –  Mat Jun 24 at 6:09
    
I want to make a repayment schedule getting data from the fund disburse table. When I will insert a row/records in a fund disburse table that will create a different repayment schedule on the r_repay_schedule table for every row on fund_disburse table. Here's my primary key on Fund_disburse table (area_code, branch_code, comp_code, dafa_no) and that is references on R_repay_schedule as Foreign Key –  K_islam Jun 24 at 7:11
    
Again, what's the purpose for that select on fund_disburse? You've got the whole PK, so you already have the data in :new. –  Mat Jun 24 at 7:16
    
Thanks Mat. I have done it. Thanks for suggestion. Sorry again. I give my new code after my question. –  K_islam Jun 24 at 7:49

1 Answer 1

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.

share|improve this answer
1  
Bad advice. Just don't select from the mutating table in your trigger at all. –  Colin 't Hart Jun 24 at 12:31
    
A trigger belongs to DML operation, thus commit or rollback must not be done inside a trigger. –  Wernfried Aug 5 at 18:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.