3 Replies Latest reply on Feb 22, 2013 12:38 PM by ant2474

    Update of hierachical table in tab form page

      I have a hierarchical table called menus. As you can see there ara two branches (63 and 64) than depend on branch 7:

      select id, pid, access from menus order by 2,1;
      ID PID Access
      1     0     3
      2     0     3
      7     2     1
      63 7 1
      64 7 1

      I would like to update the access of branches 63 and 64 if I change the access of their parent branch. Lets say I change branch 7 access to 0, then branches 63 and 64 should update its access level to 0.

      I tried this in an after update trigger:
      create or replace
      after update of acceso on "MENUS"
      for each row
      if :new."ACCESO" != :old."ACCESO" then
      update MENUS set ACCESO = :new.ACCESO
      where id in (select id from (select id, pid from menus
      connect by prior id=pid
      start with pid=:old.id));
      end if;

      Now I am implementing a package with a procedure that fetches the children rows and updates the attribute in those rows. The problem is, this is in Tabular form, and MRU process,

      Where and how should I place the call to the procedure?
        • 1. Re: Update of hierachical table in tab form page
          Please put your code between two
          Would you create a sample app on apex.oracle.com and provide workspace/username/password to be able to help you?
          • 2. Re: Update of hierachical table in tab form page
            Dear Mahmoud

            You can see it at


            User: demo, pass:Mahmoud.

            In the tab Menu, you can see the hierarchy. The cuestion is: when the user sets acceso=1 -> 'Coordinador' at level CPD, all the branches should update to acceso to a level 1 or less.

            As per design you cannot do it by means of a trigger, then you should substitute process ApplyMRU for a PLSQL process to update all the branches.
            • 3. Re: Update of hierachical table in tab form page

              ¿Anyone can help with Tabular forms update plsql process?

              Should I use a plug-in or is there any way to do this in APEX?