SQL & PL/SQL

Hi,

I have a self referencing table, used to store information on projects in an organization. There is a pimary key (modifier) and a foreign key to the modifier field (parentModifier)

Note: (modifier = project in the organization)

Basically, the parentModifier cannot be equal to the modifier in the same table, or equal to any of its children, otehrwise you get wierd recursive relationships. Its like saying you cannot be your own father, or one of your children cannot be your father. To get a list of the modifiers the parentModifier cannot be, the following statement can be used:

select
modifier
from
modifier
where
modifier = 'A'
and
level >= 1
connect by prior
modifier = parentModifier
start with
modifier = 'A'
order by
level;

So, now, I guess the way to do this is perform that query in a trigger before each row is being updated, so the pseudo code would be something like:

BEFORE UPDATE ON modifier
FOR EACH ROW
DECLARE

modifierToChange varchar2(255);
modifierList ???;

BEGIN

select
modifier
into
modifierList
from
modifier
where
modifier = 'A'
and
level >= 1
connect by prior
modifier = parentModifier
start with
modifier = 'A'
order by
level;

if modifierToChange in modifierList
return error
else
execute query
end if

END

As you can see my PL/SQL is limitied. At the moment I can handle this at the application layer (in php), but if the admin going to SQL Plus and starts to fiddle, they can easy break the system, by setting an invalid relationship.

I was wondeirng, if anyone could give me some help or advice, it would be fantastic,

Thanks!