[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 | |
---|
[201] | 92 | IF :NEW.context='delete' THEN |
---|
[210] | 93 | SELECT to_char(sysdate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') INTO endete FROM dual; |
---|
[201] | 94 | |
---|
| 95 | ELSIF :NEW.context='replace' THEN |
---|
| 96 | :NEW.safetoignore := lower(:NEW.safetoignore); |
---|
| 97 | |
---|
| 98 | IF :NEW.safetoignore IS NULL THEN |
---|
| 99 | raise_application_error(-20100, :NEW.featureid || ': safeToIgnore nicht gesetzt.'); |
---|
| 100 | ELSIF :NEW.safetoignore<>'true' AND :NEW.safetoignore<>'false' THEN |
---|
| 101 | raise_application_error(-20100, :NEW.featureid || ': safeToIgnore ''' || :NEW.safetoignore || ''' ungÃŒltig (''true'' oder ''false'' erwartet).'); |
---|
| 102 | END IF; |
---|
| 103 | |
---|
[210] | 104 | IF length(:NEW.replacedBy)=32 THEN |
---|
| 105 | -- Beginnt-Datum aus Timestamp |
---|
| 106 | neu_id := substr(:NEW.replacedBy, 1, 16); |
---|
| 107 | |
---|
| 108 | IF :NEW.featureid<>:NEW.replacedBy THEN |
---|
| 109 | endete := substr(:NEW.replacedBy, 17, 4) || '-' |
---|
| 110 | || substr(:NEW.replacedBy, 21, 2) || '-' |
---|
| 111 | || substr(:NEW.replacedBy, 23, 2) || 'T' |
---|
| 112 | || substr(:NEW.replacedBy, 26, 2) || ':' |
---|
| 113 | || substr(:NEW.replacedBy, 28, 2) || ':' |
---|
| 114 | || substr(:NEW.replacedBy, 30, 2) || 'Z' |
---|
| 115 | ; |
---|
[201] | 116 | END IF; |
---|
[210] | 117 | ELSIF length(:NEW.replacedBy)=16 THEN |
---|
| 118 | neu_id := :NEW.replacedBy; |
---|
| 119 | ELSIF length(:NEW.replacedBy)<>16 THEN |
---|
| 120 | raise_application_error(-20100, :NEW.replacedBy || ': LÀnge 16 oder 32 statt ' || length(:NEW.replacedBy) || ' erwartet.'); |
---|
[201] | 121 | END IF; |
---|
| 122 | |
---|
| 123 | IF endete IS NULL THEN |
---|
[210] | 124 | -- Beginnt-Datum des neuesten Eintrag, der nicht untergegangen ist |
---|
| 125 | -- => Enddatum fÃŒr vorherigen Satz |
---|
| 126 | EXECUTE IMMEDIATE 'SELECT max(beginnt) FROM ' || :NEW.typename |
---|
| 127 | || ' WHERE gml_id=''' || neu_id || '''' |
---|
| 128 | || ' AND beginnt>''' || beginnt || '''' |
---|
| 129 | || ' AND endet IS NULL' |
---|
| 130 | INTO endete; |
---|
[201] | 131 | END IF; |
---|
| 132 | ELSE |
---|
| 133 | raise_application_error(-20100, :NEW.featureid || ': UngÃŒltiger Kontext ' || :NEW.context || '''delete'' oder ''replace'' erwartet).'); |
---|
| 134 | END IF; |
---|
| 135 | |
---|
[210] | 136 | IF alt_id<>neu_id THEN |
---|
| 137 | dbms_output.put_line('Objekt ' || alt_id || ' wird durch Objekt ' || neu_id || ' ersetzt.'); |
---|
| 138 | END IF; |
---|
| 139 | |
---|
| 140 | IF beginnt IS NULL THEN |
---|
| 141 | dbms_output.put_line('Kein Beginndatum fuer Objekt ' || alt_id || '.'); |
---|
| 142 | END IF; |
---|
| 143 | |
---|
| 144 | IF endete IS NULL THEN |
---|
| 145 | dbms_output.put_line('Kein Beginndatum fuer Objekt ' || neu_id || '.'); |
---|
| 146 | END IF; |
---|
| 147 | |
---|
| 148 | IF beginnt IS NULL OR endete IS NULL OR beginnt=endete THEN |
---|
| 149 | raise_application_error(-20100, 'Objekt ' || alt_id || ' wird durch Objekt ' || neu_id || ' ersetzt (leere Lebensdauer?).'); |
---|
| 150 | END IF; |
---|
| 151 | |
---|
| 152 | s := 'UPDATE ' || :NEW.typename |
---|
| 153 | || ' SET endet=''' || endete || '''' |
---|
| 154 | || ' WHERE gml_id=''' || alt_id || '''' |
---|
| 155 | || ' AND beginnt=''' || beginnt || '''' |
---|
| 156 | || ' AND endet IS NULL'; |
---|
[201] | 157 | EXECUTE IMMEDIATE s; |
---|
| 158 | n := SQL%ROWCOUNT; |
---|
| 159 | IF n<>1 THEN |
---|
| 160 | dbms_output.put_line( 'SQL: ' || s); |
---|
| 161 | IF :NEW.context = 'delete' OR :NEW.safetoignore = 'true' THEN |
---|
| 162 | dbms_output.put_line( :NEW.featureid || ': Untergangsdatum von ' || n || ' Objekten statt nur einem auf ' || endete || ' gesetzt - ignoriert' ); |
---|
| 163 | :NEW.ignored := 'true'; |
---|
| 164 | RETURN; |
---|
| 165 | ELSE |
---|
| 166 | raise_application_error(-20100, :NEW.featureid || ': Untergangsdatum von ' || n || ' Objekten statt nur einem auf ' || endete || ' gesetzt - Abbruch' ); |
---|
| 167 | END IF; |
---|
| 168 | END IF; |
---|
| 169 | |
---|
| 170 | :NEW.ignored := 'false'; |
---|
| 171 | RETURN; |
---|
| 172 | END delete_feature_trigger; |
---|
| 173 | / |
---|
| 174 | |
---|
[209] | 175 | -- show errors trigger delete_feature_trigger; |
---|