1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
--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();
|
