Debian Codegraph @

modules/sequence/functions/delete-trigger.plpgsql

raw
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