| 1 |
--This is a fairly simple trigger to delete child features of features that |
| 2 |
--are being deleted as long as the child features dont have other parents |
| 3 |
--(that is, they are orphan features). Items to consider/improve: |
| 4 |
-- |
| 5 |
-- * the relationship type isnt considered; you may want to only delete |
| 6 |
-- part_of, or only delete part_of and derives_from but not other |
| 7 |
-- relationships. |
| 8 |
-- * as written, it will infintely recurse, that is, it doesnt care how |
| 9 |
-- many levels of relationships you have (typically you will have |
| 10 |
-- three: gene, transcript, and exon); maybe you dont want that |
| 11 |
-- |
| 12 |
--To install this trigger in your database, execute this on the command line: |
| 13 |
-- |
| 14 |
-- psql DATABASENAME < delete-trigger.plpgsql |
| 15 |
-- |
| 16 |
--replacing DATABASENAME with the name of your database. |
| 17 |
|
| 18 |
|
| 19 |
DROP TRIGGER tr_feature_del ON feature; |
| 20 |
CREATE OR REPLACE function fn_feature_del() RETURNS TRIGGER AS ' |
| 21 |
DECLARE |
| 22 |
id_count int; |
| 23 |
old_f_id feature.feature_id%TYPE; |
| 24 |
feat_rel_row feature_relationship%ROWTYPE; |
| 25 |
BEGIN |
| 26 |
RAISE NOTICE ''enter f_d, feature uniquename:%, type_id:%'',OLD.uniquename, OLD.type_id; |
| 27 |
old_f_id:=OLD.feature_id; |
| 28 |
|
| 29 |
--look for children of the feature to be deleted |
| 30 |
FOR feat_rel_row IN SELECT * FROM feature_relationship WHERE object_id = old_f_id LOOP |
| 31 |
|
| 32 |
--see if it has any other parents |
| 33 |
SELECT INTO id_count count(object_id) FROM feature_relationship WHERE subject_id = feat_rel_row.subject_id and object_id != old_f_id; |
| 34 |
|
| 35 |
--if not, delete it (which will cause this trigger to be called again |
| 36 |
IF id_count > 0 THEN |
| 37 |
--dont delete anything |
| 38 |
RETURN OLD; |
| 39 |
ELSE |
| 40 |
--no other parents |
| 41 |
DELETE FROM feature WHERE feature_id = feat_rel_row.subject_id; |
| 42 |
END IF; |
| 43 |
|
| 44 |
END LOOP; |
| 45 |
|
| 46 |
RAISE NOTICE ''leave f_d ....''; |
| 47 |
RETURN OLD; |
| 48 |
END; |
| 49 |
'LANGUAGE 'plpgsql'; |
| 50 |
|
| 51 |
GRANT ALL ON FUNCTION fn_feature_del() TO PUBLIC; |
| 52 |
|
| 53 |
CREATE TRIGGER tr_feature_del BEFORE DELETE ON feature for EACH ROW EXECUTE PROCEDURE fn_feature_del(); |
| 54 |
|