2 Replies Latest reply: Feb 20, 2014 9:46 AM by TexasApexDeveloper RSS

    Trigger Error

    Saumyadip Sarkar

      CREATE OR REPLACE TRIGGER  "BU_AR_BAA_AUDIT_EXCH"

       

      before INSERT OR UPDATE on "AR_BAA_AUDIT"             

      for each row

      declare

      v_EXCH_RATE NUMBER;

      BEGIN

      SELECT CONVERSION_RATE INTO v_EXCH_RATE

      FROM CORPORATE_EXCHANGE_RATE

      WHERE CORPORATE_EXCHANGE_RATE.CURRENCY_TO = :new.REBILL_CURRENCY

      AND CORPORATE_EXCHANGE_RATE.MONTH ='Jan-14'  ;

       

       

      :new.EXCHANGE_RATE_RATE := v_EXCH_RATE;

       

       

      :new.REBILL_AMOUNT_IN_USD := :new.EXCHANGE_RATE_RATE * :new.REBILL_AMOUNT;

       

       

      end;

       

      I have written the above trigger. It's working fine when I hard code the CORPORATE_EXCHANGE_RATE.MONTH value.But when I am using :new.MONTH in place of 'Jan-14'  the trigger is throwing error.

       

      I have a column BAA_MONTH in AR_BAA_AUDIT which already contains data.

       

      How do I reference this column in my Select Query highlighted in bold?

        • 1. Re: Trigger Error
          ApexPhil

          I assume you had an ORA-01858 error because it looks like you're comparing a string which isn't in date format with a date field.

           

          Assuming BAA_MONTH is a date field, use this: where to_char(BAA_MONTH, 'Mon-YY') = 'Jan-14'

           

          By the way, it's a good idea to use 4 characters for the year.

           

          Alternatively, you may find this is more efficient for your query:

           

          where BAA_MONTH between to_date('01-jan-2014', 'dd-mon-yyyy') and last_day (to_date('01-jan-2014', 'dd-mon-yyyy'))

          • 2. Re: Trigger Error
            TexasApexDeveloper

            And where is this an APEX specific issue?  You would be better to post to this SQL & PL/SQL support forum...  That is more geared towards specific question in regards to SQL & PL/SQL..

             

            Thank you,

             

            Tony Miller
            LuvMuffin Software
            Ruckersville, VA