--This is a fairly simple trigger to delete child features of features that --are being deleted as long as the child features dont have other parents --(that is, they are orphan features). Items to consider/improve: -- -- * the relationship type isnt considered; you may want to only delete -- part_of, or only delete part_of and derives_from but not other -- relationships. -- * as written, it will infintely recurse, that is, it doesnt care how -- many levels of relationships you have (typically you will have -- three: gene, transcript, and exon); maybe you dont want that -- --To install this trigger in your database, execute this on the command line: -- -- psql DATABASENAME < delete-trigger.plpgsql -- --replacing DATABASENAME with the name of your database. DROP TRIGGER tr_feature_del ON feature; CREATE OR REPLACE function fn_feature_del() RETURNS TRIGGER AS ' DECLARE id_count int; old_f_id feature.feature_id%TYPE; feat_rel_row feature_relationship%ROWTYPE; BEGIN RAISE NOTICE ''enter f_d, feature uniquename:%, type_id:%'',OLD.uniquename, OLD.type_id; old_f_id:=OLD.feature_id; --look for children of the feature to be deleted FOR feat_rel_row IN SELECT * FROM feature_relationship WHERE object_id = old_f_id LOOP --see if it has any other parents SELECT INTO id_count count(object_id) FROM feature_relationship WHERE subject_id = feat_rel_row.subject_id and object_id != old_f_id; --if not, delete it (which will cause this trigger to be called again IF id_count > 0 THEN --dont delete anything RETURN OLD; ELSE --no other parents DELETE FROM feature WHERE feature_id = feat_rel_row.subject_id; END IF; END LOOP; RAISE NOTICE ''leave f_d ....''; RETURN OLD; END; 'LANGUAGE 'plpgsql'; GRANT ALL ON FUNCTION fn_feature_del() TO PUBLIC; CREATE TRIGGER tr_feature_del BEFORE DELETE ON feature for EACH ROW EXECUTE PROCEDURE fn_feature_del();