EcalCondDB/sql/update_online_pvss_tb_sm_iov_proc.sql

0001 CREATE OR REPLACE
0002 PROCEDURE update_online_pvss_tb_sm_iov
0003 ( cndc_table IN VARCHAR2,
0004   new_since IN DATE,
0005   new_till IN DATE,
0006   dp_name IN VARCHAR2 ) IS
0007 /*

0008  *

0009  * Procedure to validate an IoV to be inserted and update a previous

0010  * IoV so that there are no overlaps.

0011  */
0012   sql_str VARCHAR(1000);
0013   future_since DATE;
0014   last_since DATE;
0015   last_till DATE;
0016
0017   BEGIN
0018     -- Ensure IoV has positive duration

0019     IF new_till <= new_since THEN
0020        raise_application_error(-20000, 'IoV must have since < till');
0021     END IF;
0022
0023     -- Make sure that there are no IoVs in the future of this one

0024     sql_str := 'SELECT max(since) FROM ' || cndc_table || ' WHERE since > :s and dp_name = :d';
0025     EXECUTE IMMEDIATE sql_str INTO future_since USING new_since, dp_name;
0026
0027     IF future_since IS NOT NULL THEN
0028       raise_application_error(-20010, 'IoVs must be inserted in increasing order:  ' ||
0029                                       'Current highest ''since'' is ' ||
0030                                       to_char(future_since, 'YYYY-MM-DD HH24:MI:SS'));
0031     END IF;
0032
0033     -- Fetch the most recent IoV prior to this one

0034     sql_str := 'SELECT max(since) FROM ' || cndc_table || ' WHERE since <= :s and dp_name = :d';
0035     EXECUTE IMMEDIATE sql_str INTO last_since USING new_since, dp_name;
0036
0037     IF last_since IS NULL THEN
0038         -- table has no data, nothing to do

0039         return;
0040     END IF;
0041
0042     -- Fetch the till of this most recent IoV

0043     sql_str := 'SELECT till FROM ' || cndc_table || ' WHERE since = :s and dp_name = :d';
0044     EXECUTE IMMEDIATE sql_str INTO last_till USING last_since, dp_name;
0045
0046     IF new_since = last_since THEN
0047         -- Attempted to insert overlapping IoV!

0048         raise_application_error(-20020, 'New IOV since overlaps older since');
0049     ELSIF new_since < last_till THEN
0050        -- Truncate the last IoV

0051        sql_str := 'UPDATE ' || cndc_table || ' SET till = :new_since WHERE since = :last_since AND till = :last_till and dp_name = :d';
0052        EXECUTE IMMEDIATE sql_str USING new_since, last_since, last_till, dp_name;
0053     END IF;
0054   END;
0055 /
0056 show errors;
0057
0058 CREATE OR REPLACE
0059 TRIGGER pvss_tb_sm_iov_tg
0060   BEFORE INSERT ON pvss_tb_sm_dat
0061   REFERENCING NEW AS newiov
0062   FOR EACH ROW
0063
0064 begin
0065   update_online_pvss_tb_sm_iov('pvss_tb_sm_dat', :newiov.since, :newiov.till,:newiov.dp_name);
0066 end;
0067 /