Trigger Help

See more:

Oracle

What is wrong with this trigger When i run this i got the following error..TRIGGER REVALIDATION FAILED. i want to confirm this trigger is correct or not? or how i can i re-write this one

CREATE OR REPLACE TRIGGER ERPTRAIN.TRG_MERCH_CUST_CHECK
BEFORE INSERT
ON ERPTRAIN.OM_MERCH_CUST
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE

  N_MC_CUST_CODE Varchar2(10);


BEGIN
  -- your code here
  -- (Trigger template "Default" could not be loaded.)




        If OM_MERCH_CUST.MC_TO_DT. IS Null

             SELECT OM_MERCH_CUST.MC_CUST_CODE,OM_MERCHINDISER_COMP_DIVN.MCD_DIVN_CODE
        FROM OM_MERCH_CUST, OM_MERCHINDISER_COMP_DIVN
        WHERE(OM_MERCH_CUST.MC_CUST_CODE = 111010)
        And OM_MERCHINDISER_COMP_DIVN.MCD_SYSID = OM_MERCH_CUST.MC_MCD_SYSID
        AND OM_MERCHINDISER_COMP_DIVN.MCD_COMP_CODE = '001' AND OM_MERCHINDISER_COMP_DIVN.MCD_DIVN_CODE = '01'
        AND ((OM_MERCH_CUST.MC_FM_DT >= TO_DATE('01-nov-2010 12:00:00 AM','DD-MON-YYYY HH12:MI:SS AM') AND OM_MERCH_CUST.MC_TO_DT Is Null ) OR OM_MERCH_CUST.MC_TO_DT Is Null)

       Else

                SELECT OM_MERCH_CUST.MC_CUST_CODE,OM_MERCHINDISER_COMP_DIVN.MCD_DIVN_CODE
        FROM OM_MERCH_CUST, OM_MERCHINDISER_COMP_DIVN
        WHERE(OM_MERCH_CUST.MC_CUST_CODE = 111010)
        And OM_MERCHINDISER_COMP_DIVN.MCD_SYSID = OM_MERCH_CUST.MC_MCD_SYSID
        AND OM_MERCHINDISER_COMP_DIVN.MCD_COMP_CODE = '001' AND OM_MERCHINDISER_COMP_DIVN.MCD_DIVN_CODE = '01'
        AND (OM_MERCH_CUST.MC_FM_DT >= TO_DATE('01-nov-2010 12:00:00 AM','DD-MON-YYYY HH12:MI:SS AM') AND    OM_MERCH_CUST.MC_TO_DT <= TO_DATE('30-nov-2010 12:00:00 AM','DD-MON-YYYY HH12:MI:SS AM'))


EXCEPTION

    WHEN DATA FOUND

    raise application_error("This Record Already Exist");



END TRG_MERCH_CUST_CHECK;
/