Thread: [Fwd: Re: triggers for heirarchy]

[Fwd: Re: triggers for heirarchy]

From
Torio Dante
Date:
--
Dante D. Torio
Geospatial Ecologist
Service Mer et Observation
Station Biologique de Roscoff
place Georges Teissier
BP 74
29682 ROSCOFF cedex Tel. 02.98.29.23.78
Email: torio@sb-roscoff.fr
http://www.sb-roscoff.fr

Hi All,
I am a newbie to PostgreSQL triggers. I have a problem and needed some
advice on how to write a trigger for a heirarchical tables/data foreign
keys–

I have these tables created in a postgres database called taxon:
family (family_id, family_name),
genus (genus_id, genus_name, family_id)
species(species_id, species_name, genus_id, family_id)

I want to write a trigger to ensure that the family_id stored in species
table is always correctly aligned with that found by looking for the
family via the genus table. The idea is that when I fill up the species
table with the genus id, the trigger should automatically fill in the
family id by referencing to the corresponding family_id present in
corresponding genus/id of the genus table, and in case the family_id in
the genus table is NULL or unknown i can write normally the family_id
during insert.

Thanks in advance. Any feedback is very much appreciated and properly
referred.

cheers!!
Dante

--
Dante D. Torio
Geospatial Ecologist
Service Mer et Observation
Station Biologique de Roscoff
place Georges Teissier
BP 74
29682 ROSCOFF cedex Tel. 02.98.29.23.78
Email: torio@sb-roscoff.fr
http://www.sb-roscoff.fr