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_at_news.ses.cio.eds.com>
I have an interesting question here.
);
);
end loop;
end;
Date: Mon, 17 May 1999 12:26:41 -0400
Message-ID: <7hpg10$kt5$1_at_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 )
);
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;
TIA
Peter Huegler
phuegler_at_bsco.com
Received on Mon May 17 1999 - 18:26:41 CEST