EcalCondDB/sql/update_tag_and_version.sql

0001
0002
0003 CREATE OR REPLACE function test_update_tag_and_version
0004 ( cndc_table IN VARCHAR2,
0005   tag IN varchar2,
0006   version IN integer)  return INTEGER IS
0007  /*

0008  *

0009  * Procedure to attribute a new version number if a tag exists with that name

0010  */
0011   sql_str VARCHAR(1000);
0012   num_tags INTEGER;
0013   last_version INTEGER;
0014   new_version INTEGER;
0015   cur_version INTEGER;
0016
0017   BEGIN
0018
0019   cur_version := version;
0020         IF version IS NULL THEN
0021         new_version :=0;
0022         cur_version :=0;
0023         END IF;
0024
0025     -- Make sure that this tag does not exist

0026     sql_str := 'SELECT count(*) FROM ' || cndc_table || ' WHERE tag = :s ';
0027     EXECUTE IMMEDIATE sql_str INTO num_tags USING tag ;
0028
0029     IF num_tags = 0 THEN
0030         new_version := cur_version;
0031     END IF;
0032
0033     -- Make sure that if it exists the proposed tag is higher

0034
0035     sql_str := 'SELECT max(version) FROM ' || cndc_table || ' WHERE tag = :s ';
0036     EXECUTE IMMEDIATE sql_str INTO last_version USING tag ;
0037
0038     IF last_version IS NOT NULL THEN
0039         IF last_version>=cur_version THEN
0040           new_version:= last_version+1;
0041         ELSIF 1=1 THEN
0042           new_version:= cur_version;
0043         END IF;
0044     END IF;
0045
0046     return new_version;
0047 end;
0048 /
0049 show errors;
0050
0051
0052
0053 CREATE OR REPLACE TRIGGER ecal_configuration_dat_ver_tg
0054   BEFORE INSERT ON ecal_run_configuration_dat
0055   FOR EACH ROW
0056     begin
0057   select test_update_tag_and_version('ECAL_RUN_CONFIGURATION_DAT', :new.tag, :new.version) into :new.version from dual;
0058 end;
0059 /
0060 SHOW ERRORS;
0061