[201] | 1 | BEGIN EXECUTE IMMEDIATE 'DROP TABLE alkis_beziehungen_insert CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END; |
---|
| 2 | / |
---|
| 3 | |
---|
| 4 | CREATE TABLE alkis_beziehungen_insert( |
---|
| 5 | ogr_fid integer NOT NULL, |
---|
| 6 | beziehung_von character(16), |
---|
| 7 | beziehungsart varchar2(2047), |
---|
| 8 | beziehung_zu character(16), |
---|
| 9 | CONSTRAINT ALKIS_BZI PRIMARY KEY (ogr_fid) |
---|
| 10 | ); |
---|
| 11 | |
---|
| 12 | -- BeziehungssÀtze aufrÀumen |
---|
| 13 | CREATE OR REPLACE TRIGGER alkis_beziehung_ins |
---|
| 14 | AFTER INSERT ON "ALKIS_BEZIEHUNGEN" |
---|
| 15 | REFERENCING NEW AS NEW |
---|
| 16 | FOR EACH ROW |
---|
| 17 | BEGIN |
---|
| 18 | -- avoid table mutation |
---|
| 19 | INSERT |
---|
| 20 | INTO alkis_beziehungen_insert(ogr_fid,beziehung_von,beziehungsart,beziehung_zu) |
---|
| 21 | VALUES ( |
---|
| 22 | :NEW.ogr_fid, |
---|
| 23 | :NEW.beziehung_von, |
---|
| 24 | :NEW.beziehungsart, |
---|
| 25 | :NEW.beziehung_zu |
---|
| 26 | ); |
---|
| 27 | END alkis_beziehung_inserted; |
---|
| 28 | / |
---|
| 29 | |
---|
[209] | 30 | -- show errors trigger alkis_beziehung_ins; |
---|
[201] | 31 | |
---|
| 32 | CREATE OR REPLACE TRIGGER alkis_beziehungen_a_ins |
---|
| 33 | AFTER INSERT ON alkis_beziehungen |
---|
| 34 | BEGIN |
---|
| 35 | FOR a IN (SELECT * FROM alkis_beziehungen_insert) LOOP |
---|
| 36 | DELETE FROM alkis_beziehungen b WHERE a.ogr_fid<b.ogr_fid AND a.beziehung_von=b.beziehung_von AND a.beziehungsart=b.beziehungsart AND a.beziehung_zu=b.beziehung_zu; |
---|
| 37 | END LOOP; |
---|
| 38 | DELETE FROM alkis_beziehungen_insert; |
---|
| 39 | END alkis_beziehungen_a_ins; |
---|
| 40 | / |
---|
| 41 | |
---|
[209] | 42 | -- show errors trigger alkis_beziehungen_a_ins |
---|
[201] | 43 | |
---|
| 44 | CREATE OR REPLACE TRIGGER delete_feature_trigger |
---|
| 45 | BEFORE INSERT ON "DELETE" |
---|
| 46 | FOR EACH ROW |
---|
| 47 | DECLARE |
---|
| 48 | s varchar2(2047); |
---|
[210] | 49 | alt_id varchar2(16); |
---|
| 50 | neu_id varchar2(16); |
---|
| 51 | beginnt varchar2(20); |
---|
[201] | 52 | endete varchar2(20); |
---|
| 53 | n INTEGER; |
---|
| 54 | BEGIN |
---|
[209] | 55 | :NEW.typename := upper(substr(:NEW.typename, 1, 30)); |
---|
| 56 | :NEW.context := lower(:NEW.context); |
---|
[201] | 57 | IF :NEW.context IS NULL THEN |
---|
| 58 | :NEW.context := 'delete'; |
---|
| 59 | END IF; |
---|
| 60 | |
---|
[210] | 61 | -- TIMESTAMP weder in gml_id noch identifier verlÀÃlich. |
---|
| 62 | -- also ggf. aus Datenbank holen |
---|
| 63 | |
---|
| 64 | IF length(:NEW.featureid)=32 THEN |
---|
| 65 | alt_id := substr(:NEW.featureid, 1, 16); |
---|
| 66 | |
---|
| 67 | IF :NEW.featureid<>:NEW.replacedBy THEN |
---|
| 68 | -- Beginnt-Datum aus Timestamp |
---|
| 69 | beginnt := substr(:NEW.featureid, 17, 4) || '-' |
---|
| 70 | || substr(:NEW.featureid, 21, 2) || '-' |
---|
| 71 | || substr(:NEW.featureid, 23, 2) || 'T' |
---|
| 72 | || substr(:NEW.featureid, 26, 2) || ':' |
---|
| 73 | || substr(:NEW.featureid, 28, 2) || ':' |
---|
| 74 | || substr(:NEW.featureid, 30, 2) || 'Z' |
---|
| 75 | ; |
---|
| 76 | END IF; |
---|
| 77 | ELSIF length(:NEW.featureid)=16 THEN |
---|
| 78 | alt_id := :NEW.featureid; |
---|
| 79 | ELSE |
---|
| 80 | raise_application_error(-20100, :NEW.featureid || ': LÀnge 16 oder 32 statt ' || length(:NEW.featureid) || ' erwartet.'); |
---|
| 81 | END IF; |
---|
| 82 | |
---|
| 83 | IF beginnt IS NULL THEN |
---|
| 84 | -- Beginnt-Datum des Àltesten Eintrag, der nicht untergegangen ist |
---|
| 85 | -- => der Satz dessen 'endet' gesetzt werden muà |
---|
| 86 | EXECUTE IMMEDIATE 'SELECT min(beginnt) FROM ' || :NEW.typename |
---|
| 87 | || ' WHERE gml_id=''' || alt_id || '''' |
---|
| 88 | || ' AND endet IS NULL' |
---|
| 89 | INTO beginnt; |
---|
| 90 | END IF; |
---|
| 91 | |
---|
[211] | 92 | IF beginnt IS NULL THEN |
---|
| 93 | IF :NEW.context = 'delete' OR :NEW.safetoignore = 'true' THEN |
---|
| 94 | dbms_output.put_line('Kein Beginndatum fuer Objekt ' || alt_id || ' gefunden.'); |
---|
| 95 | :NEW.ignored := 'true'; |
---|
| 96 | RETURN; |
---|
| 97 | ELSE |
---|
| 98 | raise_application_error(-20100, 'Kein Beginndatum fuer Objekt ' || alt_id || ' gefunden.'); |
---|
| 99 | END IF; |
---|
| 100 | END IF; |
---|
| 101 | |
---|
[201] | 102 | IF :NEW.context='delete' THEN |
---|
[210] | 103 | SELECT to_char(sysdate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') INTO endete FROM dual; |
---|
[201] | 104 | |
---|
| 105 | ELSIF :NEW.context='replace' THEN |
---|
| 106 | :NEW.safetoignore := lower(:NEW.safetoignore); |
---|
| 107 | |
---|
| 108 | IF :NEW.safetoignore IS NULL THEN |
---|
| 109 | raise_application_error(-20100, :NEW.featureid || ': safeToIgnore nicht gesetzt.'); |
---|
| 110 | ELSIF :NEW.safetoignore<>'true' AND :NEW.safetoignore<>'false' THEN |
---|
| 111 | raise_application_error(-20100, :NEW.featureid || ': safeToIgnore ''' || :NEW.safetoignore || ''' ungÃŒltig (''true'' oder ''false'' erwartet).'); |
---|
| 112 | END IF; |
---|
| 113 | |
---|
[210] | 114 | IF length(:NEW.replacedBy)=32 THEN |
---|
| 115 | -- Beginnt-Datum aus Timestamp |
---|
| 116 | neu_id := substr(:NEW.replacedBy, 1, 16); |
---|
| 117 | |
---|
| 118 | IF :NEW.featureid<>:NEW.replacedBy THEN |
---|
| 119 | endete := substr(:NEW.replacedBy, 17, 4) || '-' |
---|
| 120 | || substr(:NEW.replacedBy, 21, 2) || '-' |
---|
| 121 | || substr(:NEW.replacedBy, 23, 2) || 'T' |
---|
| 122 | || substr(:NEW.replacedBy, 26, 2) || ':' |
---|
| 123 | || substr(:NEW.replacedBy, 28, 2) || ':' |
---|
| 124 | || substr(:NEW.replacedBy, 30, 2) || 'Z' |
---|
| 125 | ; |
---|
[201] | 126 | END IF; |
---|
[210] | 127 | ELSIF length(:NEW.replacedBy)=16 THEN |
---|
| 128 | neu_id := :NEW.replacedBy; |
---|
| 129 | ELSIF length(:NEW.replacedBy)<>16 THEN |
---|
| 130 | raise_application_error(-20100, :NEW.replacedBy || ': LÀnge 16 oder 32 statt ' || length(:NEW.replacedBy) || ' erwartet.'); |
---|
[201] | 131 | END IF; |
---|
| 132 | |
---|
| 133 | IF endete IS NULL THEN |
---|
[210] | 134 | -- Beginnt-Datum des neuesten Eintrag, der nicht untergegangen ist |
---|
| 135 | -- => Enddatum fÃŒr vorherigen Satz |
---|
| 136 | EXECUTE IMMEDIATE 'SELECT max(beginnt) FROM ' || :NEW.typename |
---|
| 137 | || ' WHERE gml_id=''' || neu_id || '''' |
---|
| 138 | || ' AND beginnt>''' || beginnt || '''' |
---|
| 139 | || ' AND endet IS NULL' |
---|
| 140 | INTO endete; |
---|
[201] | 141 | END IF; |
---|
| 142 | |
---|
[211] | 143 | IF alt_id<>neu_id THEN |
---|
| 144 | dbms_output.put_line('Objekt ' || alt_id || ' wird durch Objekt ' || neu_id || ' ersetzt.'); |
---|
| 145 | END IF; |
---|
[210] | 146 | |
---|
[211] | 147 | IF endete IS NULL THEN |
---|
| 148 | dbms_output.put_line('Kein Beginndatum fuer Objekt ' || neu_id || '.'); |
---|
| 149 | END IF; |
---|
[210] | 150 | |
---|
[211] | 151 | IF endete IS NULL OR beginnt=endete THEN |
---|
| 152 | raise_application_error(-20100, 'Objekt ' || alt_id || ' wird durch Objekt ' || neu_id || ' ersetzt (leere Lebensdauer?).'); |
---|
| 153 | END IF; |
---|
| 154 | ELSE |
---|
| 155 | raise_application_error(-20100, :NEW.featureid || ': UngÃŒltiger Kontext ' || :NEW.context || '''delete'' oder ''replace'' erwartet).'); |
---|
[210] | 156 | END IF; |
---|
| 157 | |
---|
| 158 | s := 'UPDATE ' || :NEW.typename |
---|
| 159 | || ' SET endet=''' || endete || '''' |
---|
| 160 | || ' WHERE gml_id=''' || alt_id || '''' |
---|
| 161 | || ' AND beginnt=''' || beginnt || '''' |
---|
| 162 | || ' AND endet IS NULL'; |
---|
[201] | 163 | EXECUTE IMMEDIATE s; |
---|
| 164 | n := SQL%ROWCOUNT; |
---|
| 165 | IF n<>1 THEN |
---|
| 166 | dbms_output.put_line( 'SQL: ' || s); |
---|
| 167 | IF :NEW.context = 'delete' OR :NEW.safetoignore = 'true' THEN |
---|
| 168 | dbms_output.put_line( :NEW.featureid || ': Untergangsdatum von ' || n || ' Objekten statt nur einem auf ' || endete || ' gesetzt - ignoriert' ); |
---|
| 169 | :NEW.ignored := 'true'; |
---|
| 170 | RETURN; |
---|
[211] | 171 | ELSIF n=0 THEN |
---|
| 172 | EXECUTE IMMEDIATE 'SELECT endet FROM ' || :NEW.typename || |
---|
| 173 | ' WHERE gml_id=''' || alt_id || '''' || |
---|
| 174 | ' AND beginnt=''' || beginnt || '''' |
---|
| 175 | INTO endete; |
---|
| 176 | |
---|
| 177 | IF NOT endete IS NULL THEN |
---|
| 178 | dbms_output.put_line( :NEW.featureid || ': Objekt bereits ' || endete || ' untergegangen - ignoriert' ); |
---|
| 179 | ELSE |
---|
| 180 | dbms_output.put_line( :NEW.featureid || ': Objekt nicht gefunden - ignoriert' ); |
---|
| 181 | END IF; |
---|
| 182 | |
---|
| 183 | :NEW.ignored := 'true'; |
---|
| 184 | RETURN; |
---|
[201] | 185 | ELSE |
---|
| 186 | raise_application_error(-20100, :NEW.featureid || ': Untergangsdatum von ' || n || ' Objekten statt nur einem auf ' || endete || ' gesetzt - Abbruch' ); |
---|
| 187 | END IF; |
---|
| 188 | END IF; |
---|
| 189 | |
---|
| 190 | :NEW.ignored := 'false'; |
---|
| 191 | END delete_feature_trigger; |
---|
| 192 | / |
---|
| 193 | |
---|
[209] | 194 | -- show errors trigger delete_feature_trigger; |
---|