10 Replies Latest reply: Mar 21, 2013 4:43 AM by Purvesh K RSS

    Trigger Issue

    user11876003
      In case of following trigger

      CREATE OR REPLACE TRIGGER gwp_trigger
      AFTER INSERT OR UPDATE
      ON T_DEBIT_CREDIT_NOTE
      REFERENCING OLD AS OLD NEW AS NEW
      FOR EACH ROW
      DECLARE
      --vdebitnumber VARCHAR2(50) := :NEW.DCN_DEBIT_CREDIT_NOTE_NO;
      BEGIN
      gwp_report_pak_trigger(vdebitnumber => :NEW.DCN_DEBIT_CREDIT_NOTE_NO);

      END gwp_trigger;


      It is not working AFTER INSERT But It work UPDATE can anyone explain why trigger is not fire after INSERT
        • 1. Re: Trigger Issue
          ranit B
          What is - gwp_report_pak_trigger ? Purpose?

          Try adding an EXCEPTION block to the trigger check if some exception is happening.
          • 2. Re: Trigger Issue
            996818
            These virtual columns :NEW & :OLD is not applicable for INSERT statement because insert itself new and dont have a old value, Hence your insert will not work.
            • 3. Re: Trigger Issue
              Karthick_Arp
              user11876003 wrote:
              In case of following trigger

              CREATE OR REPLACE TRIGGER gwp_trigger
              AFTER INSERT OR UPDATE
              ON T_DEBIT_CREDIT_NOTE
              REFERENCING OLD AS OLD NEW AS NEW
              FOR EACH ROW
              DECLARE
              --vdebitnumber VARCHAR2(50) := :NEW.DCN_DEBIT_CREDIT_NOTE_NO;
              BEGIN
              gwp_report_pak_trigger(vdebitnumber => :NEW.DCN_DEBIT_CREDIT_NOTE_NO);

              END gwp_trigger;


              It is not working AFTER INSERT But It work UPDATE can anyone explain why trigger is not fire after INSERT
              How did you determine that the trigger is not working?
              • 4. Re: Trigger Issue
                S10390
                --vdebitnumber VARCHAR2(50) := :NEW.DCN_DEBIT_CREDIT_NOTE_NO;

                You have commented the variable. And try to add an exception block to catch the exception on what going wrong with the insert.
                • 5. Re: Trigger Issue
                  BCV
                  Hi,

                  Its Perfectly working for me ,Check Your Codings With my example.

                  Create one Package,
                  CREATE OR REPLACE PACKAGE psdba.cv_dummy_pkg
                  IS
                     PROCEDURE cv_proce2 (lv_n_empno IN NUMBER);
                  END;
                  /
                  
                  CREATE OR REPLACE PACKAGE BODY PSDBA.cv_dummy_pkg
                  AS
                     
                  
                     PROCEDURE cv_proce2 (lv_n_empno IN NUMBER)
                     AS
                     BEGIN
                        insert into cv(salary) values (lv_n_empno);
                  --        FROM CV
                  --       WHERE empno = lv_n_empno;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           raise_application_error (-20001, 'NOT A VALID DATA');
                     END;
                  
                     
                  END;
                  /
                  After That Create Trigger,
                  CREATE OR REPLACE TRIGGER mt_idu_tr
                     AFTER INSERT OR DELETE OR UPDATE
                     ON mt
                     REFERENCING NEW AS NEW OLD AS OLD
                     FOR EACH ROW
                  BEGIN
                     cv_dummy_pkg.cv_proce2 (lv_n_empno => :NEW.a);
                  --commit;
                  --end;
                  END mt_idu_tr;
                  Then update and Insert into table 'mt'.
                  Coded Operation Is Done through Package Which Is In Trigger.



                  Regards
                  BCV.

                  Edited by: BCV on Mar 21, 2013 11:41 AM
                  • 6. Re: Trigger Issue
                    Purvesh K
                    How do you ascertain, it does not work?

                    See below, it works for me:
                    drop table test_table;
                    drop table test_table_temp;
                    
                    create table test_table (col number);
                    create table test_table_temp (col varchar2(50));
                    
                    create or replace
                    trigger trg_test_table
                    after insert or update
                    on test_table
                    for each row
                    begin
                      insert into test_table_temp values ('New Value of Col is ' || :new.col);
                    end trg_test_table;
                    
                    insert into test_table values (1);
                    insert into test_table values (2);
                    update test_Table set col = 100 * col where col = 1;
                    
                    select *
                      from test_table_temp;
                    
                    COL                                                
                    -------------------------------------------------- 
                    New Value of Col is 1                              
                    New Value of Col is 2                              
                    New Value of Col is 100
                    Perhaps, the procedure gwp_report_pak_trigger, is not processing the data you are passing as parameter due to some conditional by-pass or there is an exception that is being suppressed like when others then dbms_output.put_line(SQLERRM) and is not followed by a RAISE.

                    So, unless you post us how the Trigger after Insert does not work, it would not be possible to provide much help to you. Also, it would be lot helpful if you could post the code for gwp_report_pak_trigger.
                    • 7. Re: Trigger Issue
                      user11876003
                      Thanks for your reply ,
                      When I update a record in T_DEBIT_CREDIT_NOTE table then that record is insert into T_REPORT_DNCN_GWP_THU table,
                      but after INSERT in to T_DEBIT_CREDIT_NOTE table
                      doesn't Insert record to T_REPORT_DNCN_GWP_THU table , therefore I mention trigger is not working for INSERT
                      • 8. Re: Trigger Issue
                        Purvesh K
                        user11876003 wrote:
                        Thanks for your reply ,
                        When I update a record in T_DEBIT_CREDIT_NOTE table then that record is insert into T_REPORT_DNCN_GWP_THU table,
                        but after INSERT in to T_DEBIT_CREDIT_NOTE table
                        doesn't Insert record to T_REPORT_DNCN_GWP_THU table , therefore I mention trigger is not working for INSERT
                        Okay. As I mentioned earlier, there is no reason why an Update should work and Insert should not. Not, at least, you have some condition to by-pass the DML's or perhaps an exception that is preventing the DML modified data from logging into your destination table.
                        Just a thought in my mind, since you did not mention, Does the table T_REPORT_DNCN_GWP_THU exist in same schema?

                        Also, I shall stick to what I said earlier, unless we see the procedure that logs data into T_REPORT_DNCN_GWP_THU table, we would not be in a position to comment on the reason of happening.

                        To demonstrate an example on your lines, to log message into a table using a Procedure:
                        drop table test_table;
                        drop table test_table_temp;
                         
                        create table test_table (col number);
                        create table test_table_temp (col varchar2(50));
                        
                        create or replace procedure logMessage(msg varchar2)
                        is
                        begin
                          insert into test_table_temp values (msg);
                        end;
                        
                        create or replace
                        trigger trg_test_table
                        after insert or update
                        on test_table
                        for each row
                        begin
                          logMessage('New Value of Col is ' || :new.col);
                        end trg_test_table;
                         
                        insert into test_table values (1);
                        insert into test_table values (2);
                        update test_table set col = 100 * col where col = 1;
                        
                        select *
                          from test_table_temp;
                        
                        COL                                                
                        -------------------------------------------------- 
                        New Value of Col is 1                              
                        New Value of Col is 2                              
                        New Value of Col is 100 
                        But now, if you have procedure implemented in below way, the exception logged may have been suppressed.
                        create or replace procedure logMessage(msg varchar2)
                        is
                        begin
                          insert into test_table_temp values (msg);
                        exception
                          when others then
                            dbms_output.put_line(SQLERRM);
                        end;
                        Did you try debugging the procedure? What was the outcome?
                        • 9. Re: Trigger Issue
                          user11876003
                          In my procedure gwp_report_pak_trigger(vdebitnumber => :NEW.DCN_DEBIT_CREDIT_NOTE_NO);
                          like below


                          CREATE OR REPLACE PROCEDURE gwp_report_pak_trigger(vDebitNumber STRING)
                          IS
                          PRAGMA AUTONOMOUS_TRANSACTION;
                          v_agent_code VARCHAR2(80);
                          v_stake_code VARCHAR2(100);
                          v_agent_name varchar2(2500);
                          v_debit_no VARCHAR2(220);
                          v_policy_no VARCHAR2(200);
                          v_pol_start VARCHAR2(200);
                          v_deb_date DATE;
                          v_prod_code VARCHAR2(200);
                          v_pol_status VARCHAR2(2500);
                          v_pol_hol_name VARCHAR2(2500);
                          v_dncn_indicator VARCHAR2(2050);
                          --v_query_column VARCHAR2(2500);
                          v_basic NUMBER(20,6);
                          v_srcc NUMBER(20,6);
                          v_tc NUMBER(20,6);
                          v_gwp NUMBER(20,6);
                          v_adminfee NUMBER(20,6);
                          v_tax NUMBER(20,6);
                          v_stamp NUMBER(20,6);
                          v_policyfee NUMBER(20,6);
                          v_nbt NUMBER(20,6);
                          v_crsf NUMBER(20,6);
                          v_total NUMBER(20,6);
                          v_assurance_code VARCHAR2(25);
                          v_matchvoucher VARCHAR2(1500);
                          v_third VARCHAR2(50);
                          v_curr VARCHAR2(3);
                          v_rate NUMBER(20,6);
                          v_pol_code VARCHAR2(20);
                          v_address VARCHAR2(300);
                          v_si NUMBER(20,6);
                          v_vehicle VARCHAR2(300);
                          v_match_unmatch VARCHAR2(10);
                          v_daterange VARCHAR2(20);
                          v_prod_name VARCHAR2(20);
                          v_narration VARCHAR2(3000);
                          v_unadjusted_amt NUMBER(30,6);
                          v_sirange VARCHAR2(3);
                          v_usage VARCHAR2(50);
                          v_risktype VARCHAR2(100);
                          v_bis_party VARCHAR2(200);
                          v_chassis VARCHAR2(350);
                          v_engin VARCHAR2(350);
                          vCreditStatus VARCHAR2(350);
                          vStatusWord VARCHAR2(250);
                          vPayingParty VARCHAR2(350);

                          CURSOR vgwpList
                          IS
                          SELECT distinct
                          AGENT_NAME,
                          STAK_CODE,
                          AGENT_CODE,
                          DEBIT_DATE,
                          DNCN_NO,
                          PRODUCT_CODE,
                          POLICY_NO,
                          DECODE(substr(DNCN_NO,1,2),'DN',
                          DECODE(END_TYPE,'RN','RENEWAL','RE','REINSTATE','IS','NEW','AE','ADDITION','CA','CANCEL','DE','DELETION','PP','PPW-CANC','N/A'),'CN',
                          DECODE(END_TYPE,'RN','RENEWAL','RE','REINSTATE','IS','NEW','AE','ADDITION','CA','CANCEL','DE','DELETION','PP','PPW-CANC','N/A')) POL_STATUS,
                          POL_HOL_NAME,
                          DNCN_INDICATOR,
                          BASIC,
                          SRCC,
                          TC,
                          ADMIN_FEE,
                          TAX,
                          STAMP,
                          POLICY_FEE,
                          NBT,
                          CRSF,
                          ASSCODE,
                          MATCH_VOUCHER,
                          THD,
                          CURRANCY,
                          RATE,
                          POL_HOL_CODE,
                          ADDRESS,
                          SI,
                          VEHNO,
                          POLICY_START,
                          MATCH_IND,
                          TRUNC((((86400*(SYSDATE - DEBIT_DATE))/60)/60)/24) DATE_RANGE,
                          DECODE(PRODUCT_CODE,'TEL','WCI','TME','MED','TCRME','MED','TMEI','MED','THG','MED',LOB_CODE) DEPT,
                          NARRATION_DCN,
                          UNADJUST_AMT,
                          DEB_TOTAL
                          ,(CASE WHEN SI <500000 THEN 'A'
                          WHEN SI >=500000 and SI<=1000000 THEN 'B'
                          WHEN SI >1000000 and SI <=1500000 THEN 'C'
                          WHEN SI >1500000 and SI <=2000000 THEN 'D'
                          WHEN SI >2000000 and SI <=2500000 THEN 'E'
                          WHEN SI >2500000 and SI <=3000000 THEN 'F'
                          WHEN SI >3000000 and SI <=4000000 THEN 'G'
                          WHEN SI >4000000 and SI <=5000000 THEN 'H'
                          WHEN SI >5000000 and SI <=6000000 THEN 'I'
                          WHEN SI >6000000 and SI <=7000000 THEN 'J'
                          WHEN SI >7000000 and SI <=10000000 THEN 'K'
                          WHEN SI >10000000 THEN 'L' END ) SI_RANGE ,
                          DECODE (CLASS_OF_VEHICLE,'2','Hiring','1','Private','3','Rent','4','Agricultural Usage','5','Route') USAGE_
                          ,RISK_TYPE,
                          BIS_PARTY,
                          CHASSIS_NO,
                          ENGINE_NO

                          FROM(
                          SELECT
                          POL.POL_POLICY_NUMBER POLICY_NO,
                          DCN.DCN_DEBIT_CREDIT_NOTE_NO DNCN_NO ,
                          nvl(trunc(( SELECT
                          max(pcs.pcs_payment_due_date)
                          FROM
                          t_payment_cycle pcs
                          WHERE
                          pcs.pcs_pmt_pol_policy_id = pol.pol_policy_id
                          AND pcs.pcs_pmt_policy_payment_id IS NOT NULL
                          AND pcs.pcs_payment_reference_no = dcn.dcn_debit_credit_note_no
                          )),dcn.dcn_date) POLICY_START,
                          DCN.DCN_DATE DEBIT_DATE,
                          DCN.DCN_DR_CR_INDICATOR DNCN_INDICATOR ,
                          DCN.DCN_NARRATION NARRATION,
                          DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',DCN.DCN_TRAN_AMOUNT , (-1)*DCN.DCN_TRAN_AMOUNT) DEB_TOTAL,
                          PROD.NAME PRODUCT_NAME,
                          PROD.PRODUCT_CODE PRODUCT_CODE,
                          NVL((SELECT REPLACE(REPLACE(REPLACE(PPT.PPA_PTY_PARTY_CODE,chr(10),''),chr(13),''),'-','')
                          FROM T_POLICY_PARTY PPT
                          WHERE PPT.PPA_POL_POLICY_ID = POL.POL_POLICY_ID
                          AND PPT.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','BROKER','DIRECT','HNB_BANK','HNB_STAFF','DIR_SP','DIR_SPIND','ASSU_STAFF','Agent')
                          AND PPT.PPA_EFFECTIVE_END_DATE IS NULL
                          AND rownum=1),'-') AGENT_CODE,
                          NVL((SELECT PPT.ppa_pty_party_name
                          FROM T_POLICY_PARTY PPT
                          WHERE PPT.PPA_POL_POLICY_ID = POL.POL_POLICY_ID
                          AND PPT.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','BROKER','DIRECT','HNB_BANK','HNB_STAFF','DIR_SP','DIR_SPIND','ASSU_STAFF','Agent')
                          AND PPT.PPA_EFFECTIVE_END_DATE IS NULL
                          AND rownum=1),'-') AGENT_NAME,
                          NVL((SELECT PPT.ppa_shr_stake_holder_fn_code
                          FROM T_POLICY_PARTY PPT
                          WHERE PPT.PPA_POL_POLICY_ID = POL.POL_POLICY_ID
                          AND PPT.PPA_SHR_STAKE_HOLDER_FN_CODE IN ('AGENT','BROKER','DIRECT','HNB_BANK','HNB_STAFF','DIR_SP','DIR_SPIND','ASSU_STAFF','Agent')
                          AND PPT.PPA_EFFECTIVE_END_DATE IS NULL
                          AND rownum=1),'-') STAK_CODE,
                          ( SELECT pp.PPA_PTY_PARTY_NAME
                          FROM t_policy_party pp
                          WHERE pp.ppa_pol_policy_id=pol.pol_policy_id
                          AND pp.ppa_effective_end_date IS NULL
                          AND pp.ppa_shr_stake_holder_fn_code = 'POLICY-HOL'
                          and rownum=1) POL_HOL_NAME,
                          ( select p.value_description FROM
                          t_policy_risk_property t,
                          t_parameter_value p
                          WHERE t.prp_prk_pol_policy_id= pol.pol_policy_id
                          AND p.parameter_id = t.prp_par_parameter_id
                          AND p.alphanumeric_code_value =t.prp_value_alpha_code
                          AND t.prp_effective_end_date IS NULL
                          AND t.prp_par_parameter_name like 'Vehicle Type %' AND ROWNUM=1
                          ) RISK_TYPE,
                          substr(dcn.dcn_endorsement_number,1,2) END_TYPE,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'IPMO02' and rownum=1),0.00) BASIC,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'IPMO04' and rownum=1),0.00) SRCC,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'IPMO05' and rownum=1),0.00) TC,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'LCLO31' and rownum=1),0.00) ADMIN_FEE ,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'LCLO15' and rownum=1),0.00) TAX ,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'LCLO05' and rownum=1),0.00) STAMP ,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'IOIO01' and rownum=1),0.00) POLICY_FEE,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'LCLO03' and rownum=1),0.00) NBT,
                          nvl(( SELECT DECODE(DCN.DCN_DR_CR_INDICATOR,'DR',atd.ATD_CREDIT_AMOUNT , (-1)*atd.atd_debit_amount)
                          FROM
                          t_accounting_trn_detail atd
                          ,t_account acc
                          WHERE
                          atd.atd_atn_accounting_trn_id=dcn.dcn_atn_acc_tran_id
                          AND acc.acc_account_id=atd.atd_acc_account_id
                          AND acc.acc_account_type = 'LCLO04'),0.00) CRSF,
                          nvl(( SELECT pop.pop_value_alpha_code
                          FROM t_policy_property pop
                          WHERE pop.pop_pol_policy_id=pol.pol_policy_id
                          AND pop.pop_effective_end_date IS NULL
                          AND pop.pop_par_parameter_name = 'Assurance Code'),POL.pol_policy_branch_code) ASSCODE,
                          (select t.prp_value_alpha_code from t_policy_risk_property t
                          WHERE t.prp_prk_pol_policy_id=pol.pol_policy_id
                          AND t.prp_effective_end_date IS NULL
                          AND t.prp_par_parameter_name like 'Class of vehicle%' AND ROWNUM=1) CLASS_OF_VEHICLE,
                          GetRVNumbersForDNthu(DCN.dcn_debit_credit_note_no) MATCH_VOUCHER,
                          decode((SELECT c.rv_meaning FROM cg_ref_codes c WHERE c.rv_domain='LOB'AND c.rv_low_value=PROD.lob and rownum=1) ,'MOTOR INSURANCE',decode((SELECT count(prc.prc_policy_risk_cover_id)
                          FROM t_policy_risk_cover prc, t_cover cvr, t_policy_risk_cover_detail rcd
                          WHERE prc.prc_prk_pol_policy_id=pol.pol_policy_id AND prc.prc_cvr_cover_id=cvr.cover_id
                          AND cvr.cover_code='COMPR' AND rcd.rcd_prc_prk_policy_risk_id=prc.prc_prk_policy_risk_id
                          and rcd.rcd_prc_prk_pol_policy_id=prc.prc_prk_pol_policy_id
                          AND rcd.rcd_prc_policy_risk_cover_id=prc.prc_policy_risk_cover_id
                          AND rcd.rcd_effective_end_date IS NULL and rownum=1),0,'THIRD_PARTY','COMPR'),'') THD,
                          dcn.dcn_tran_currency_rate RATE
                          ,dcn.dcn_transaction_currency CURRANCY,
                          ( SELECT pp.ppa_pty_party_code
                          FROM t_policy_party pp
                          WHERE pp.ppa_pol_policy_id=pol.pol_policy_id
                          AND pp.ppa_effective_end_date IS NULL
                          AND pp.ppa_shr_stake_holder_fn_code = 'POLICY-HOL'
                          and rownum=1) POL_HOL_CODE,
                          NVL((select con.con_address_line_1||con.con_address_line_2||con.con_address_line_3 InsuredAddress
                          from t_policy_party pr1 ,t_contact con
                          where pr1.ppa_pol_policy_id =pol.pol_policy_id
                          and pr1.ppa_shr_stake_holder_fn_code = 'POLICY-HOL'
                          and pr1.ppa_effective_end_date is null
                          and pr1.ppa_pfy_pty_party_id=con.con_pty_party_id
                          and con.con_effective_end_date is null
                          and con.con_type_of_contact like 'Mailing%'
                          and rownum=1),' ') ADDRESS,
                          (SELECT pdl.pod_sum_insured_assured FROM t_policy_detail pdl
                          WHERE pdl.pod_pol_policy_id=POL.POL_POLICY_ID
                          and pdl.pod_effective_end_date IS NULL and pdl.pod_effective_end_date IS NULL and rownum=1) SI
                          ,nvl((SELECT
                          prp.prp_value_description
                          FROM
                          t_policy_risk_property prp
                          WHERE
                          prp.prp_prk_pol_policy_id=pol.pol_policy_id
                          AND prp.prp_effective_end_date IS NULL
                          AND prp.prp_prk_policy_risk_id IS NOT NULL
                          AND prp.prp_par_parameter_name ='Registration Number'
                          AND ROWNUM=1),'N/A') VEHNO,
                          DCN.dcn_matched_ind MATCH_IND,
                          DECODE(PROD.LOB,'01','FIRE','02','MISC','03','MARINE','04','MOTOR','05','ENGG') LOB_CODE,
                          DCN.dcn_narration NARRATION_DCN,
                          DCN.dcn_unadjusted_amount UNADJUST_AMT,
                          (SELECT PRV.VALUE_DESCRIPTION
                          FROM T_POLICY_PROPERTY polp,
                          T_PARAMETER_VALUE PRV
                          WHERE polp.POP_POL_POLICY_ID=pol.pol_policy_id
                          AND POLP.POP_PAR_PARAMETER_ID=PRV.PARAMETER_ID
                          AND PRV.ALPHANUMERIC_CODE_VALUE=polp.pop_value_alpha_code
                          AND polp.pop_par_parameter_name ='Business Party'
                          AND polp.pop_effective_end_date is null
                          and rownum=1) BIS_PARTY,
                          (SELECT replace(replace(replace(prp.prp_value_description,chr(10),''),chr(13),''),'-','')
                          FROM t_policy_risk_property prp
                          WHERE prp.prp_prk_pol_policy_id=pol.pol_policy_id
                          AND prp.prp_effective_end_date IS NULL
                          AND prp.prp_prk_policy_risk_id IS NOT NULL
                          AND prp.prp_par_parameter_name ='Chassis_No' AND ROWNUM=1) CHASSIS_NO,
                          (SELECT replace(replace(replace(prp.prp_value_description,chr(10),''),chr(13),''),'-','')
                          FROM t_policy_risk_property prp
                          WHERE prp.prp_prk_pol_policy_id=pol.pol_policy_id
                          AND prp.prp_effective_end_date IS NULL
                          AND prp.prp_prk_policy_risk_id IS NOT NULL
                          AND prp.prp_par_parameter_name ='Engine No.'
                          AND ROWNUM=1 ) ENGINE_NO

                          FROM T_DEBIT_CREDIT_NOTE DCN ,
                          T_POLICY POL,
                          T_PRODUCT PROD
                          WHERE DCN.DCN_POL_POLICY_ID=POL.POL_POLICY_ID
                          AND POL.POL_PRT_PRODUCT_ID =PROD.PRODUCT_ID
                          AND DCN.DCN_DEBIT_CREDIT_NOTE_NO=vDebitNumber
                          -- AND DCN.DCN_DATE <= to_date('30/12/2012','DD/MM/YYYY')
                          -- AND DCN.dcn_narration NOT LIKE 'Credit Note for PPW Cancellation against debit note%'
                          --AND DCN.DCN_CREATED_BY NOT LIKE 'PPWMIG'
                          AND DCN.DCN_DEBIT_CREDIT_NOTE_NO NOT LIKE 'DC%'
                          AND DCN.DCN_DEBIT_CREDIT_NOTE_NO NOT LIKE 'CC%'
                          AND DCN.DCN_CREATED_BY <> 'PPWMIG'
                          AND PROD.LOB NOT IN ('LIFE')
                          AND DCN.DCN_CLM_CLAIM_ID IS NULL
                          ORDER BY POL.POL_POLICY_NUMBER , DCN.DCN_DEBIT_CREDIT_NOTE_NO) ;

                          BEGIN

                          OPEN vgwpList;

                          LOOP
                          FETCH vgwpList

                          INTO
                          v_agent_name
                          ,v_stake_code
                          ,v_agent_code
                          ,v_deb_date
                          ,v_debit_no
                          ,v_prod_code
                          ,v_policy_no
                          ,v_pol_status
                          ,v_pol_hol_name
                          ,v_dncn_indicator
                          ,v_basic
                          ,v_srcc
                          ,v_tc
                          ,v_adminfee
                          ,v_tax
                          ,v_stamp
                          ,v_policyfee
                          ,v_nbt
                          ,v_crsf
                          ,v_assurance_code
                          ,v_matchvoucher
                          ,v_third
                          ,v_curr
                          ,v_rate
                          ,v_pol_code
                          ,v_address
                          ,v_si
                          ,v_vehicle
                          ,v_pol_start
                          ,v_match_unmatch
                          ,v_daterange
                          ,v_prod_name
                          ,v_narration
                          ,v_unadjusted_amt
                          ,v_total
                          ,v_sirange
                          ,v_usage
                          ,v_risktype
                          ,v_bis_party
                          ,v_chassis
                          ,v_engin;

                          EXIT WHEN vgwpList%NOTFOUND;

                          v_gwp:=(v_basic+v_srcc+v_tc);
                          --v_total:= (v_gwp+v_adminfee+v_tax+v_stamp+v_policyfee+v_nbt+v_crsf);
                          ---This 2012-12-03
                          BEGIN
                          IF substr(v_debit_no,1,2)='CN' AND (v_pol_status !='PPW-CANC' OR v_pol_status != 'N/A') THEN
                          BEGIN
                          select t4.pop_value_alpha_code
                          INTO vCreditStatus
                          from t_policy_property t4
                          where t4.pop_pol_policy_id =(SELECT pol.pol_policy_id FROM T_POLICY POL WHERE POL.pol_Policy_number=v_policy_no)
                          and t4.pop_par_parameter_name = 'PhysicalRefund'
                          and t4.pop_effective_end_date is null;

                          IF vCreditStatus ='YES' THEN
                          BEGIN
                          SELECT ATN.ATN_INVOLVED_PARTY_NAME
                          INTO vPayingParty
                          FROM T_ACCOUNTING_TRANSACTION ATN
                          WHERE ATN.ATN_VOUCHER_NUMBER=v_debit_no;
                          IF vPayingParty LIKE 'HNB ASSURANCE PLC%' THEN
                          vStatusWord := 'UTILIZATION';
                          ELSE
                          vStatusWord := 'PHYSICAL REFUND';
                          END IF;

                          END;

                          ELSIF vCreditStatus ='NO' THEN
                          vStatusWord := 'NON PHYSICAL';
                          END IF;

                          EXCEPTION
                          WHEN OTHERS THEN
                          vStatusWord := 'N/A';
                          END;
                          ELSE
                          vStatusWord := '';
                          END IF;
                          END;


                          insert into T_REPORT_DNCN_GWP_THU (AGENT_NAME,BUSINESS_PARTY,AGENT_CODE,DEBIT_DATE,DNCN_NO,DEPT,POL_NO,
                          POL_STATUS,POL_HOL_NAME,BASIC,SRCC,TC,GWP,ADMIN_FEE,TAX,STAMP,POLICY_FEE,NBT,CRSF,TOTAL,
                          ASSURANCE_CODE,MATCHING_VOU,THIRD_PARTY,CURR,RATE,POL_HOL_CODE,ADDRESS,SI,PRODUCT,VEH_NO,START_DATE,
                          MATCH_UNMATCH_IND,DATE_IND,REMARKS,UNADJUSTED_AMT,SI_RANGE,USAGE_OF_VEHICLE,RISK_TYPE,BIS_PARTY,CHASSIS_NO,ENGIN_NO,REFUND_IND)
                          VALUES(v_agent_name,v_stake_code,v_agent_code,v_deb_date,v_debit_no,v_prod_name,v_policy_no,
                          v_pol_status,v_pol_hol_name,v_basic,v_srcc,v_tc,v_gwp,v_adminfee,v_tax,v_stamp,v_policyfee,v_nbt,
                          v_crsf,v_total,v_assurance_code,v_matchvoucher,v_third,v_curr,v_rate,v_pol_code,v_address,v_si
                          ,v_prod_code,v_vehicle,v_pol_start,v_match_unmatch,v_daterange,v_narration,v_unadjusted_amt ,v_sirange,v_usage,v_risktype,v_bis_party,v_chassis,v_engin,vStatusWord);
                          commit;

                          END LOOP;

                          CLOSE vgwpList;

                          end GWP_REPORT_PAK_TRIGGER;




                          I am using same table T_DEBIT_CREDIT_NOTE select statement
                          • 10. Re: Trigger Issue
                            Purvesh K
                            Perhaps, a condition in the Cursor is not returning any data.
                            where dcn.dcn_pol_policy_id = pol.pol_policy_id
                                    and pol.pol_prt_product_id = prod.product_id
                                    and dcn.dcn_debit_credit_note_no = vdebitnumber           --> Debit Number passed from Procedure
                            My guess is, there is no entry in t_debit_credit_note dcn for the Debit number passed to the procedure and
                            exit
                            when vgwplist%notfound;
                            causes the Loop to terminate once it does not find any data for the Debit Number.

                            Please have a check in the above mentioned table, as It might hold some data when you are executing an Update but not when an Insert is fired.

                            Although, I agree that you have a pretty long SQL, but doing a Row by Row processing is killing the performance, especially, when this would be executing for Each row of your Base Trigger table. Imagine the time taken for completion, when a procedure is executed a Million or more times!!!