Forum Stats

  • 3,851,480 Users
  • 2,263,986 Discussions
  • 7,904,731 Comments

Discussions

APEX Trigger Coding Help

Doolius
Doolius Member Posts: 115
edited Mar 30, 2014 6:28PM in APEX Discussions

I'm having trouble coding this into APEX sql:

create or replace TRIGGER "OEF_ITV_NDN_TRU"
     BEFORE UPDATE ON OEF_ITV_NDN
     FOR EACH ROW
BEGIN
     :NEW.UPDATED_DATE := SYSDATE;
     :NEW.UPDATED_BY := nvl(v('P1_USER_ID'),USER);
     IF :OLD.BOOKING_DT :<> :NEW.BOOKING_DT
          THEN NEW.UPDATE_COLUMN := "BOOKING DATE";
     ELSE IF :OLD.RPT_SAIL_DATE :<> NEW.RPT_SAIL_DATE 
          THEN NEW.UPDATE_COLUMN := "REPORTED SAIL DATE";
     END IF;
END

The origional trigger that worked only had the :NEW.UPDATED_DATE and :NEW.UPDATED_BY. The part that is giving me the trouble is the "IF" satement.

Thanks,

Steven

Best Answer

  • Danny*D
    Danny*D Member Posts: 114 Bronze Badge
    edited Mar 26, 2014 7:32PM Answer ✓

    ok Steven, you're very lucky got all the top gurus looking into this simple trigger. I am very sure they will sort it out for you

    I am not a top gun, but can't help noticing some simple mistakes in the trigger that I want to point out.

    1. with :OLD you have the column at the front, with NEW, sometimes you don't.

       e.g       THEN NEW.UPDATE_COLUMN := "REPORTED SAIL DATE";

    2. this if else if end if block is not valid 

         IF :OLD.BOOKING_DT  != :NEW.BOOKING_DT  

         THEN :NEW.UPDATE_COLUMN := "BOOKING DATE";

         ELSE IF :OLD.RPT_SAIL_DATE  != :NEW.RPT_SAIL_DATE

         THEN NEW.UPDATE_COLUMN := "REPORTED SAIL  DATE";

         END IF;

    3. Double quotes in char value definately wrong

    4. If you have a null value in either :OLD.BOOKING_DT  or :NEW.BOOKING_DT (same goes for RPT_SAIL_DATE) your != test will not work,

        e.g BOOKING_DT changed from NULL to some dates, or from some dates to NULL, the != will not pick it up.

        I used decode. becasue decode considers NULLs to be equal. decode is overloaded for numbers, dates and strings

    I did a quick rewrite hope it helps

    CREATE OR REPLACE TRIGGER "OEF_ITV_NDN_TRU" BEFORE
      UPDATE ON OEF_ITV_NDN FOR EACH ROW
    BEGIN
      :NEW.UPDATED_DATE := SYSDATE;
      :NEW.UPDATED_BY   := NVL(V('P1_USER_ID'),USER);
      BEGIN 
        SELECT
        DECODE(:OLD.BOOKING_DT,:NEW.BOOKING_DT,DECODE(:OLD.RPT_SAIL_DATE,:NEW.RPT_SAIL_DATE,:OLD.UPDATE_COLUMN,'REPORTED SAIL DATE'),'BOOKING DATE')
        INTO :NEW.UPDATE_COLUMN
        FROM DUAL;
      -- if BOOKING_DT changed :NEW.UPDATE_COLUMN = 'BOOKING DATE', else if RPT_SAIL_DATE changed :NEW.UPDATE_COLUMN = 'REPORTED SAIL DATE', if either, keep the update columns old value
      EXCEPTION WHEN OTHERS THEN
        :NEW.UPDATE_COLUMN := 'ERRORED';
      END;
    END;
    
    
    
«1

Answers

  • Martin1
    Martin1 Member Posts: 548 Bronze Badge

    Hi Steven,

    try this

    IF :OLD.BOOKING_DT ^= :NEW.BOOKING_DT 

      THEN NEW.UPDATE_COLUMN := "BOOKING DATE"; 

    ELSE IF :OLD.RPT_SAIL_DATE ^= NEW.RPT_SAIL_DATE  

      THEN NEW.UPDATE_COLUMN := "REPORTED SAIL DATE"; 

    END IF;

    Regards,

    Martin

  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,966 Gold Crown
    edited Mar 26, 2014 11:00AM

    Or maybe this:

    create or replace TRIGGER "OEF_ITV_NDN_TRU" 

         BEFORE UPDATE ON OEF_ITV_NDN 

    FOR EACH ROW 

    BEGIN 

         :NEW.UPDATED_DATE := SYSDATE;

         :NEW.UPDATED_BY := nvl(v('P1_USER_ID'),USER); 

         IF :OLD.BOOKING_DT  != :NEW.BOOKING_DT

    THEN :NEW.UPDATE_COLUMN := "BOOKING DATE"; 

    ELSE IF :OLD.RPT_SAIL_DATE  != :NEW.RPT_SAIL_DATE  

    THEN NEW.UPDATE_COLUMN := "REPORTED SAIL DATE"; 

    END IF; 

    END 


    Your colons were out of place to start with, so the code would not work...


    Thank you,

    Tony Miller
    LuvMuffin Software
    Ruckersville, VA

  • Doolius
    Doolius Member Posts: 115
    edited Mar 26, 2014 11:24AM

    Tony,

    I get the below error:

    Compilation failed, line 9 (10:12:30) The line numbers associated with compilation errors are relative to the first BEGIN statement. This only affects the compilation of database triggers.PLS-00103: Encountered the symbol ";" when expecting one of the following: if
    
    
    create or replace TRIGGER "OEF_ITV_NDN_TRU"
         BEFORE UPDATE ON 
    OEF_ITV_NDN FOR EACH ROW BEGIN      :NEW.UPDATED_DATE
    := SYSDATE;      :NEW.UPDATED_BY :=
    nvl(v('P1_USER_ID'),USER);      IF :OLD.BOOKING_DT  != :NEW.BOOKING_DT      THEN :NEW.UPDATE_COLUMN :=
    "BOOKING DATE";      ELSE IF :OLD.RPT_SAIL_DATE  != :NEW.RPT_SAIL_DATE      THEN
    NEW.UPDATE_COLUMN :=
    "REPORTED SAIL
    DATE";      END IF; END;

    The good thing is that it is the least amount of errors I've gotten so far.

    If I were to take the ":OLD" out and just had the column names would it mean the same thing? Only reason I'm asking is because the "OLD" is not a different color like "NEW" is in APEX.

    Steven

  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,966 Gold Crown

    Change this line: THEN :NEW.UPDATE_COLUMN := "BOOKING DATE";  to THEN :NEW.UPDATE_COLUMN := "BOOKING DATE"


    Remove the semi-colon in the then statement...


    Thank you,

    Tony Miller
    LuvMuffin Software
    Ruckersville, VA

      

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,196 Red Diamond
    TexasApexDeveloper wrote:
    
    Change this line: THEN :NEW.UPDATE_COLUMN := "BOOKING DATE";  to THEN :NEW.UPDATE_COLUMN := "BOOKING DATE"
    
    Remove the semi-colon in the then statement...

    No, don't. There's nothing wrong with that. The problem is using double quotes (") to delimit the string literals on lines 8 and 10. SQL and PL/SQL use single quotes (') as string delimiters.

  • Nicolette
    Nicolette Member Posts: 526 Silver Badge

    Steven

    Besides the uses of double quotes instead of single quotes as mentioned by fac586.

    You also have

    if
    then
      do your stuff
    else if
    then
      do something else
    end if;
    

    This should be

    if
    then
      do your stuff
    elsif
    then
      do something else
    end if;
    

    For more information see the documentation PL/SQL Control Statements

    Nicolette

  • Doolius
    Doolius Member Posts: 115

    Unfortunately I still get the same error when the double quotes are replaced with single quotes.

    Could it be causing problems because of the ":OLD"?

    Is " :OLD.BOOKING_DT" the same as " BOOKING_DT "(as in the current value for the column BOOKING_DT column in the OEF_ITV_NDN table) ?

    or do they mean two different things?

    Steven

  • Danny*D
    Danny*D Member Posts: 114 Bronze Badge
    edited Mar 26, 2014 7:32PM Answer ✓

    ok Steven, you're very lucky got all the top gurus looking into this simple trigger. I am very sure they will sort it out for you

    I am not a top gun, but can't help noticing some simple mistakes in the trigger that I want to point out.

    1. with :OLD you have the column at the front, with NEW, sometimes you don't.

       e.g       THEN NEW.UPDATE_COLUMN := "REPORTED SAIL DATE";

    2. this if else if end if block is not valid 

         IF :OLD.BOOKING_DT  != :NEW.BOOKING_DT  

         THEN :NEW.UPDATE_COLUMN := "BOOKING DATE";

         ELSE IF :OLD.RPT_SAIL_DATE  != :NEW.RPT_SAIL_DATE

         THEN NEW.UPDATE_COLUMN := "REPORTED SAIL  DATE";

         END IF;

    3. Double quotes in char value definately wrong

    4. If you have a null value in either :OLD.BOOKING_DT  or :NEW.BOOKING_DT (same goes for RPT_SAIL_DATE) your != test will not work,

        e.g BOOKING_DT changed from NULL to some dates, or from some dates to NULL, the != will not pick it up.

        I used decode. becasue decode considers NULLs to be equal. decode is overloaded for numbers, dates and strings

    I did a quick rewrite hope it helps

    CREATE OR REPLACE TRIGGER "OEF_ITV_NDN_TRU" BEFORE
      UPDATE ON OEF_ITV_NDN FOR EACH ROW
    BEGIN
      :NEW.UPDATED_DATE := SYSDATE;
      :NEW.UPDATED_BY   := NVL(V('P1_USER_ID'),USER);
      BEGIN 
        SELECT
        DECODE(:OLD.BOOKING_DT,:NEW.BOOKING_DT,DECODE(:OLD.RPT_SAIL_DATE,:NEW.RPT_SAIL_DATE,:OLD.UPDATE_COLUMN,'REPORTED SAIL DATE'),'BOOKING DATE')
        INTO :NEW.UPDATE_COLUMN
        FROM DUAL;
      -- if BOOKING_DT changed :NEW.UPDATE_COLUMN = 'BOOKING DATE', else if RPT_SAIL_DATE changed :NEW.UPDATE_COLUMN = 'REPORTED SAIL DATE', if either, keep the update columns old value
      EXCEPTION WHEN OTHERS THEN
        :NEW.UPDATE_COLUMN := 'ERRORED';
      END;
    END;
    
    
    
  • Doolius
    Doolius Member Posts: 115
    edited Mar 27, 2014 10:12AM

    Thank you Danny. That worked like a charm.

    I have a few more columns that need to be added so if I'm understand the code right it would need to look something like this:

    CREATE OR REPLACE TRIGGER "OEF_ITV_NDN_TRU" BEFORE 
      UPDATE ON OEF_ITV_NDN FOR EACH ROW 
    BEGIN 
      :NEW.UPDATED_DATE := SYSDATE; 
      :NEW.UPDATED_BY   := NVL(V('P1_USER_ID'),USER); 
      BEGIN  
        SELECT 
        DECODE(:OLD.BOOKING_DT,:NEW.BOOKING_DT,DECODE(
             :OLD.RPT_SAIL_DATE,:NEW.RPT_SAIL_DATE,DECODE(
                   :OLD.COL3, :NEW.COL3, DECODE(
                         :OLD.COL4, :NEW.COL4, DECODE(
                               :OLD.COL5, :NEW.COL5:OLD.UPDATE_COLUMN,'COL5'),
                         'COL4'),
                   'COL3'),
             'REPORTED SAIL DATE'),
       'BOOKING DATE') 
        INTO :NEW.UPDATE_COLUMN 
        FROM DUAL; 
      -- if BOOKING_DT changed :NEW.UPDATE_COLUMN = 'BOOKING DATE', else if RPT_SAIL_DATE changed :NEW.UPDATE_COLUMN = 'REPORTED SAIL DATE', if either, keep the update columns old value 
      EXCEPTION WHEN OTHERS THEN 
        :NEW.UPDATE_COLUMN := 'ERRORED'; 
      END; 
    END;
    
    
  • Danny*D
    Danny*D Member Posts: 114 Bronze Badge

    Hi Steven,

    yes, that's the logic if you just want one value to go into UPDATE_COLUMN.

    question is what if all columns (or more than one columns) changed, which one do you put in to the UPDATE_COLUMN?

    P.S, you missed a comma in line 12, before :OLD.UPDATE_COLUMN

    Cheers.

This discussion has been closed.