File indexing completed on 2024-04-06 12:22:58
0001 CREATE OR REPLACE TRIGGER beam_insert_tg
0002 AFTER INSERT ON run_iov
0003 REFERENCING NEW AS newrun
0004 FOR EACH ROW
0005
0006 DECLARE
0007 loc VARCHAR2(100);
0008 tbl VARCHAR2(32);
0009 sql_str VARCHAR2(4000);
0010 BEGIN
0011 SELECT location INTO loc
0012 FROM run_tag rtag JOIN location_def ldef ON rtag.location_id = ldef.def_id
0013 WHERE rtag.tag_id = :newrun.tag_id;
0014
0015 IF loc = 'H4B' THEN
0016 tbl := 'RUN_H4_BEAM_DAT';
0017 ELSIF loc = 'H2' THEN
0018 tbl := 'RUN_H2_BEAM_DAT';
0019 ELSE return;
0020 END IF;
0021
0022 sql_str := 'INSERT INTO ' || tbl || '(iov_id) VALUES (:iov_id)';
0023 EXECUTE IMMEDIATE sql_str USING :newrun.iov_id;
0024
0025 FOR col IN (SELECT bs.variable_name, bs.datatype, bs.value, bs.string_value FROM user_tab_cols ucols, beam_source bs
0026 WHERE ucols.column_name = bs.variable_name
0027 AND ucols.table_name = tbl
0028 AND ucols.column_name != 'IOV_ID')
0029 LOOP
0030 sql_str := 'UPDATE ' || tbl || ' SET "' || col.variable_name || '" = :val WHERE iov_id = :iov_id';
0031 IF col.datatype = 'NUMERIC' THEN
0032 EXECUTE IMMEDIATE sql_str USING col.value, :newrun.iov_id;
0033 ELSIF col.datatype = 'TEXTUAL' THEN
0034 EXECUTE IMMEDIATE sql_str USING col.string_value, :newrun.iov_id;
0035 ELSE raise_application_error(-20000, 'UNKNOWN DATATYPE');
0036 END IF;
0037
0038 END LOOP;
0039 END;
0040 /
