Forum Stats

  • 3,828,121 Users
  • 2,260,865 Discussions
  • 7,897,483 Comments

Discussions

Updating section of the trigger not working

gkthomas
gkthomas Member Posts: 426 Bronze Badge
edited May 30, 2015 4:37PM in APEX Discussions

I am using a trigger in Apex. Can someone tell me what is wrong in this trigger statement.  The Inserting and Deleting sections work as expected in the case statement. 

But when it comes to updating it is not executing based on the field condition. it is always executing first one in updating section  Please help

**********************************************************************

create or replace TRIGGER AIU_BASE_ACTIONS
   AFTER
    INSERT OR
    UPDATE OF STATUS,PRIORITY OR
    DELETE
   ON BASE_TANKS FOR EACH ROW

BEGIN


  CASE
    WHEN INSERTING THEN
    PL/SQL Statement

 

  WHEN UPDATING('STATUS')   THEN  -- This part is executing always even if I update the priority field

   PL/SQL Statement
 

  WHEN UPDATING('PRIORITY') THEN

   PL/SQL Statement

 

WHEN DELETING THEN

   PL/SQL Statement

END CASE;
   
END;

*************************************************************************

Tagged:
Kiran Pawar

Answers

  • Jitendra
    Jitendra Member Posts: 933
    edited May 30, 2015 4:26PM

    Hi gkthomas,

    I have tried the code given below and this is working for me.

    modified your code according to my testing, try this

    create or replace TRIGGER AIU_BASE_ACTIONS
      AFTER
        INSERT OR
        UPDATE OF STATUS,PRIORITY OR
        DELETE
      ON BASE_TANKS
      FOR EACH ROW
    BEGIN 
     CASE
        WHEN INSERTING THEN
          PL/SQL Statement
        WHEN UPDATING THEN
          IF :New.STATUS != :Old.STATUS THEN
              PL/SQL Statement
          ELSIF :New.PRIORITY != :Old.PRIORITY THEN
              PL/SQL Statement
          END IF;
        WHEN DELETING THEN
            PL/SQL Statement
     END CASE;
    END;
    
    
    

    Hope this helps you,

    Regards,

    Jitendra

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,981 Red Diamond
    edited May 30, 2015 4:37PM
    gkthomas wrote:
    
    I am using a trigger in Apex. Can someone tell me what is wrong in this trigger statement.  The Inserting and Deleting sections work as expected in the case statement.
    But when it comes to updating it is not executing based on the field condition. it is always executing first one in updating section  Please help
    **********************************************************************
    create or replace TRIGGER AIU_BASE_ACTIONS
      AFTER
        INSERT OR
        UPDATE OF STATUS,PRIORITY OR
        DELETE
      ON BASE_TANKS FOR EACH ROW BEGIN
      CASE
        WHEN INSERTING THEN
        PL/SQL Statement   WHEN UPDATING('STATUS')  THEN  -- This part is executing always even if I update the priority field   PL/SQL Statement   WHEN UPDATING('PRIORITY') THEN   PL/SQL Statement WHEN DELETING THEN   PL/SQL Statement END CASE;
       
    END; *************************************************************************

    What DML code is being executed by APEX to cause the trigger to fire?

    Only one branch of the case statement can be executed, which will always be the first matching condition. If your page is using a standard wizard-generated APEX ARP or MRU process then all of the columns linked to items in the page will be updated, including both STATUS and PRIORITY. Since the STATUS clause appears first, that is the only logic that will ever be executed following an update.

    If you want both the STATUS and PRIORITY logic to be executed on an update involving both columns, change the code to:

    create or replace trigger aiu_base_actions
      after
        insert or
        update of status, priority or
        delete
      on base_tanks
      for each row
    
    begin
    
      case
    
        when inserting
        then
          pl/sql statement
       
        when updating
        then
          if updating('STATUS')
          then
            pl/sql statement
          end if;
         
          if updating('PRIORITY')
          then
            pl/sql statement
          end if;
       
        when deleting
        then
          pl/sql statement
         
      end case;
       
    end;
    
    

    If you have other requirements, then specify them clearly: how to get answers from forum

    Kiran Pawar
  • fac586
    fac586 Senior Technical Architect Member Posts: 20,981 Red Diamond
    edited May 30, 2015 4:31PM
    Jitendra wrote:
    
    I have tried the code given below and this is working for me.
    modified your code according to my testing, try this
    
    1. create or replace TRIGGER AIU_BASE_ACTIONS 
    2.   AFTER 
    3.     INSERT OR 
    4.     UPDATE OF STATUS,PRIORITY OR 
    5.     DELETE 
    6.   ON BASE_TANKS 
    7.   FOR EACH ROW 
    8. BEGIN  
    9. CASE 
    10.     WHEN INSERTING THEN 
    11.       PL/SQL Statement 
    12.     WHEN UPDATING THEN 
    13.       IF :New.STATUS != :Old.STATUS THEN 
    14.           PL/SQL Statement 
    15.       ELSIF :New.PRIORITY != :Old.PRIORITY THEN 
    16.           PL/SQL Statement 
    17.       END IF; 
    18.     WHEN DELETING THEN 
    19.         PL/SQL Statement 
    20. END CASE; 
    21. END; 
    create or replace TRIGGER AIU_BASE_ACTIONS
      AFTER
        INSERT OR
        UPDATE OF STATUS,PRIORITY OR
        DELETE
      ON BASE_TANKS
      FOR EACH ROW
    BEGIN 
     CASE
        WHEN INSERTING THEN
          PL/SQL Statement
        WHEN UPDATING THEN
          IF :New.STATUS != :Old.STATUS THEN
              PL/SQL Statement
          ELSIF :New.PRIORITY != :Old.PRIORITY THEN
              PL/SQL Statement
          END IF;
        WHEN DELETING THEN
            PL/SQL Statement
     END CASE;
    END;
    
    
    

    That does not work in all cases. If both STATUS and PRIORITY are updated then the PRIORITY branch will not be executed.

This discussion has been closed.