[Gmod-schema-cmts] schema/chado/modules/sequence/apollo-bridge apollo-triggers.sql,1.18,1.19

[Scott C. <sco...@us...>]

Update of /cvsroot/gmod/schema/chado/modules/sequence/apollo-bridge
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv12342

Modified Files:
	apollo-triggers.sql 
Log Message:
adding a function to make changes to a gene name propagate down to its children
(currently untested!)


Index: apollo-triggers.sql
===================================================================
RCS file: /cvsroot/gmod/schema/chado/modules/sequence/apollo-bridge/apollo-triggers.sql,v
retrieving revision 1.18
retrieving revision 1.19
diff -C2 -d -r1.18 -r1.19
*** apollo-triggers.sql	11 Feb 2005 15:26:42 -0000	1.18
--- apollo-triggers.sql	14 Feb 2005 22:04:13 -0000	1.19
***************
*** 623,624 ****
--- 623,666 ----
  CREATE TRIGGER feature_relationship_propagatename_tr_i AFTER INSERT ON feature_relationship FOR EACH ROW EXECUTE PROCEDURE feature_relationship_propagatename_fn_i();
  
+ 
+ DROP TRIGGER feature_update_name_tr_u ON feature;
+ 
+ CREATE OR REPLACE FUNCTION feature_fn_u() RETURNS TRIGGER AS
+ '
+ DECLARE
+   f_type	cvterm.name%TYPE;
+   f_type_gene	CONSTANT varchar :=''gene'';
+   f_row         feature%ROW;
+   name_suffix   varchar;
+ BEGIN
+   IF OLD.uniquename <> NEW.uniquename THEN
+       RAISE NOTICE ''You may not change the uniquename of a feature'';
+       RAISE NOTICE ''if you feel you must, contact your database admin'';
+       RETURN OLD;
+   END IF;
+   IF OLD.name = NEW.name THEN
+       --not updating name, so go ahead 
+       RETURN NEW;
+   END IF;
+ 
+   SELECT INTO f_type cv.name FROM feature f, cvterm cv WHERE f.feature_id = OLD.feature_id and f.type_id = cv.cvterm_id; 
+ 
+   IF f_type <> f_type_gene THEN
+       --it's not a gene, so go ahead
+       RETURN NEW;
+   END IF;
+ 
+   --OK, so it's a gene, and were changing the name...
+ 
+   FOR f_row IN SELECT f.* FROM feature f, get_sub_feature_ids(OLD.feature_id) ch WHERE f.feature_id = ch.feature_id LOOP
+       IF f_row.name LIKE OLD.name||''-%'' THEN
+           SELECT INTO name_suffix  substring(name from OLD.name||''(-.+)'');
+           UPDATE feature SET name = NEW.name||name_suffix WHERE feature_id = f_row.feature_id;
+       END IF; 
+   END LOOP;  
+   
+   RETURN NEW; 
+ END;
+ 'LANGUAGE plpgsql;
+ 
+ CREATE TRIGGER feature_update_name_tr_u BEFORE INSERT ON feature FOR EACH ROW EXECUTE PROCEDURE feature_fn_u();