12 Replies Latest reply: Mar 30, 2014 5:28 PM by Danny*D201 RSS

    APEX Trigger Coding Help

    Doolius

      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

        • 1. Re: APEX Trigger Coding Help
          Martin1

          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

          • 2. Re: APEX Trigger Coding Help
            TexasApexDeveloper

            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

            • 3. Re: APEX Trigger Coding Help
              Doolius

              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

              • 4. Re: APEX Trigger Coding Help
                TexasApexDeveloper

                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

                  

                • 5. Re: APEX Trigger Coding Help
                  fac586

                  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.

                  • 6. Re: APEX Trigger Coding Help
                    Nicolette

                    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

                    • 7. Re: APEX Trigger Coding Help
                      Doolius

                      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

                      • 8. Re: APEX Trigger Coding Help
                        Danny*D201

                        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;
                        
                        
                        
                        • 9. Re: APEX Trigger Coding Help
                          Doolius

                          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;
                          
                          
                          • 10. Re: APEX Trigger Coding Help
                            Danny*D201

                            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.

                            • 11. Re: APEX Trigger Coding Help
                              Doolius

                              Good catch! Thanks!

                               

                              And if the user updates more than one column (which they usually will) they want the column farthest right one the page to be shown for the Update_Column.

                               

                              So the way I am putting them in the trigger (top to bottom) is the one farthest right at the top and working my way farthest left.

                               

                              Ex. for the above trigger:

                              col5col4col3rpt_sail_dateBooking_dt

                               

                              So since booking_dt is farthest right of all the columns, it is put at the top of my trigger. Is this the correct format?

                               

                              Thanks,

                              Steven

                              • 12. Re: APEX Trigger Coding Help
                                Danny*D201

                                Hi Steven, yes, if that's the case, you are doing it right. cheers. Danny