Trying to write exception into trigger 1

[stuckDuck]

Im updating a trigger. I am trying to create an exception such that if v_pv_count > 0 then do not go into the loop.
It is not working. Help appreciated.


SELECT COUNT(*) INTO V_PV_COUNT
FROM C_ACTION
WHERE C_CNO = :NEW.CSM_CNO
AND C_TYPE = 'BLD'
AND A_MENU_ID = 'B'
AND A_CODE = 'B021';

IF NEW.C_TYPE IN('BLD') AND :NEW.A_MENU_ID in ('I','J') AND :NEW.CS_ASSI_TO IS NULL) THEN

IF (V_PV_COUNT >0) THEN
:NEW.CS_ASSI_TO :='XX';
END IF;

ELSE IF (V_PV_COUNT <=0) THEN
for rw in c_act_par_with_b_inp loop
:new.cs_assi_to := rw.prc_b_combo_area;
end loop;
END IF;


END IF;

 

[carp]

I think this might be closer to what you are trying to do:

SELECT COUNT(*) INTO V_PV_COUNT
            FROM C_ACTION
            WHERE C_CNO = :NEW.CSM_CNO
            AND C_TYPE = 'BLD'
            AND A_MENU_ID = 'B'
            AND A_CODE = 'B021';
            
       IF (:NEW.C_TYPE IN('BLD') AND :NEW.A_MENU_ID in ('I','J') AND :NEW.CS_ASSI_TO IS NULL) THEN
           
            IF (V_PV_COUNT >0) THEN
                :NEW.CS_ASSI_TO :='XX';
            ELSIF  (V_PV_COUNT <=0) THEN              
                     for rw in c_act_par_with_b_inp loop
                    :new.cs_assi_to := rw.prc_b_combo_area;
                    end loop;                     
           END IF;                                       
  END IF;

although

SELECT COUNT(*) INTO V_PV_COUNT
            FROM C_ACTION
            WHERE C_CNO = :NEW.CSM_CNO
            AND C_TYPE = 'BLD'
            AND A_MENU_ID = 'B'
            AND A_CODE = 'B021';
            
  IF (:NEW.C_TYPE IN('BLD') AND :NEW.A_MENU_ID in ('I','J') AND :NEW.CS_ASSI_TO IS NULL) THEN
          IF (V_PV_COUNT >0) THEN
                :NEW.CS_ASSI_TO :='XX';
          ELSE         
                     for rw in c_act_par_with_b_inp loop
                    :new.cs_assi_to := rw.prc_b_combo_area;
                    end loop;                     
           END IF;
 END IF;

is a little more compact and efficient. I think the following would be even better:

SELECT COUNT(*) INTO V_PV_COUNT
            FROM C_ACTION
            WHERE C_CNO = :NEW.CSM_CNO
            AND C_TYPE = 'BLD'
            AND A_MENU_ID = 'B'
            AND A_CODE = 'B021';

IF (:NEW.C_TYPE IN('BLD') AND :NEW.A_MENU_ID in ('I','J') AND :NEW.CS_ASSI_TO IS NULL AND v_pv_count <= 0) THEN
      for rw in c_act_par_with_b_inp loop
             :new.cs_assi_to := rw.prc_b_combo_area;
      end loop;                     
ELSE
       :NEW.CS_ASSI_TO :='XX';
END IF;

Please let us know if this takes care of the problem for you.

 

[stuckDuck]

Thankyou for your help. There are some changes and now the code should assign XX to BLD (i,J) if the B021 is present. I added a while loop, but not able to test it yet.


SELECT COUNT(*) INTO V_PV_COUNT
FROM C_ACTION
WHERE C_CNO = :NEW.CSM_CNO
AND C_TYPE = 'BLD'
AND A_MENU_ID = 'B'
AND A_CODE = '021';

while v_pv_count >0 loop
IF NEW.C_TYPE IN('BLD') AND NEW.A_MENU_ID in ('I','J')) AND NEW.CS_ASSI_TO IS NULL) THEN
:NEW.CS_ASSI_TO :='XX';
END IF;
exit loop


IF NEW.C_TYPE IN('BLD') AND :NEW.A_MENU_ID in ('I','J') AND :NEW.CS_ASSI_TO IS NULL AND v_pv_count <= 0) THEN
for rw in c_act_par_with_b_inp loop
new.cs_assi_to := rw.prc_b_combo_area;
end loop;

END IF;

 

[stuckDuck]

I fixed it!!!!!!

Thanks abunch. this is now closed. I'll edit w/ solution for reference later.