Enforcing Hierarchical Relationships

From: Peter A Huegler <phuegler_at_epix.net>
Date: Mon, 17 May 1999 12:26:41 -0400
Message-ID: <7hpg10$kt5$1@news.ses.cio.eds.com>

I have an interesting question here.

I have a table with a foreign key reference to itself. That is like the following employee table:

create table employee
(

   emp_id         number(9)
      constraint employee_pk primary key,
   name           varchar2(50),
   manager_id     number(9)

);

alter table employee
add
(

   constraint employee_fk

      foreign key( manager_id )
      references employee( emp_id )

);

Now I am trying to add a check on the database to not allow a circular reference. That is like below:

emp_id name manager_id

------  -----------------  ----------
  1     John Doe               3
  2     Bill Hemmings          1
  3     Anne Francis           2

I could not figure out how to check this using a constraint. So my next idea was to use a trigger. I tried the following

create or replace trigger check_employee before update on employee
for each row
declare

   cursor emp_reports is

      select            emp_id
      from              employee
      start with        manager_id=:new.emp_id
      connect by prior  emp_id=manager_id;

begin

   for curr_emp in emp_reports loop

      if :new.manager_id = curr_emp.emp_id then
         raise_application_error( -20501, 'Employee cannot be managed by
this manager');
         return;
      end if;

   end loop;
end;

This trigger caused a table mutating error which is consistent with the oracle documentation. Does anyone have any ideas on how I can implement this check into the database? I need to do this check whenever the manager_id is updated.

TIA Peter Huegler
phuegler_at_bsco.com Received on Mon May 17 1999 - 11:26:41 CDT