1 Reply Latest reply on Feb 18, 2013 9:51 AM by John Stegeman

    Hierarchical table attribute update

      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;

      Though I get an Ora-04088. What am I doing wrong? And what would be the best way to do this?