Updating section of the trigger not working

gkthomas

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;

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

Jitendra

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

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

fac586

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.