[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 | |
---|
| 30 | show errors trigger alkis_beziehung_ins; |
---|
| 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 | |
---|
| 42 | show errors trigger alkis_beziehungen_a_ins |
---|
| 43 | |
---|
| 44 | CREATE OR REPLACE TRIGGER delete_feature_trigger |
---|
| 45 | BEFORE INSERT ON "DELETE" |
---|
| 46 | FOR EACH ROW |
---|
| 47 | DECLARE |
---|
| 48 | s varchar2(2047); |
---|
| 49 | gml_id varchar2(13); |
---|
| 50 | endete varchar2(20); |
---|
| 51 | n INTEGER; |
---|
| 52 | BEGIN |
---|
| 53 | :NEW.context := lower(:NEW.context); |
---|
| 54 | gml_id := substr(:NEW.featureid, 1, 16); |
---|
| 55 | |
---|
| 56 | IF :NEW.context IS NULL THEN |
---|
| 57 | :NEW.context := 'delete'; |
---|
| 58 | END IF; |
---|
| 59 | |
---|
| 60 | IF :NEW.context='delete' THEN |
---|
| 61 | SELECT to_char(sysdate, 'UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') INTO endete FROM dual; |
---|
| 62 | |
---|
| 63 | ELSIF :NEW.context='replace' THEN |
---|
| 64 | :NEW.safetoignore := lower(:NEW.safetoignore); |
---|
| 65 | |
---|
| 66 | IF :NEW.safetoignore IS NULL THEN |
---|
| 67 | raise_application_error(-20100, :NEW.featureid || ': safeToIgnore nicht gesetzt.'); |
---|
| 68 | ELSIF :NEW.safetoignore<>'true' AND :NEW.safetoignore<>'false' THEN |
---|
| 69 | raise_application_error(-20100, :NEW.featureid || ': safeToIgnore ''' || :NEW.safetoignore || ''' ungÃŒltig (''true'' oder ''false'' erwartet).'); |
---|
| 70 | END IF; |
---|
| 71 | |
---|
| 72 | IF :NEW.replacedBy IS NULL OR length(:NEW.replacedBy)<16 THEN |
---|
| 73 | IF :NEW.safetoignore = 'true' THEN |
---|
| 74 | dbms_output.put_line( :NEW.featureid || ': Nachfolger ''' || :NEW.replacedBy || ''' nicht richtig gesetzt - ignoriert' ); |
---|
| 75 | :NEW.ignored := 'true'; |
---|
| 76 | RETURN; |
---|
| 77 | ELSE |
---|
| 78 | raise_application_error(-20100, :NEW.featureid || ': Nachfolger ''' || :NEW.replacedBy || ''' nicht richtig gesetzt - Abbruch'); |
---|
| 79 | END IF; |
---|
| 80 | END IF; |
---|
| 81 | |
---|
| 82 | IF length(:NEW.replacedBy)=16 THEN |
---|
| 83 | EXECUTE IMMEDIATE 'SELECT MAX(beginnt) FROM ' || :NEW.typename || |
---|
| 84 | ' WHERE gml_id=''' || :NEW.replacedBy || ''' AND endet IS NULL' |
---|
| 85 | INTO endete; |
---|
| 86 | ELSE |
---|
| 87 | -- replaceBy mit Timestamp |
---|
| 88 | EXECUTE IMMEDIATE 'SELECT beginnt FROM ' || :NEW.typename || |
---|
| 89 | ' WHERE identifier=''urn:adv:oid:' || :NEW.replacedBy || '''' |
---|
| 90 | INTO endete; |
---|
| 91 | IF endete IS NULL THEN |
---|
| 92 | EXECUTE IMMEDIATE 'SELECT MAX(beginnt) FROM ' || :NEW.typename || |
---|
| 93 | ' WHERE gml_id=''' || substr(:NEW.replacedBy,1,16) || ''' AND endet IS NULL' |
---|
| 94 | INTO endete; |
---|
| 95 | END IF; |
---|
| 96 | END IF; |
---|
| 97 | |
---|
| 98 | IF endete IS NULL THEN |
---|
| 99 | IF :NEW.safetoignore = 'true' THEN |
---|
| 100 | dbms_output.put_line(:NEW.featureid || ': Nachfolger ''' || :NEW.replacedBy || ''' nicht gefunden - ignoriert'); |
---|
| 101 | :NEW.ignored := 'true'; |
---|
| 102 | RETURN; |
---|
| 103 | ELSE |
---|
| 104 | raise_application_error(-20100, :NEW.featureid || ': Nachfolger ''' || :NEW.replacedBy || ''' nicht gefunden - Abbruch'); |
---|
| 105 | END IF; |
---|
| 106 | END IF; |
---|
| 107 | ELSE |
---|
| 108 | raise_application_error(-20100, :NEW.featureid || ': UngÃŒltiger Kontext ' || :NEW.context || '''delete'' oder ''replace'' erwartet).'); |
---|
| 109 | END IF; |
---|
| 110 | |
---|
| 111 | s := 'UPDATE ' || :NEW.typename |
---|
| 112 | || ' SET endet=''' || endete || '''' |
---|
| 113 | || ' WHERE gml_id=''' || gml_id || '''' |
---|
| 114 | || ' AND endet IS NULL' |
---|
| 115 | || ' AND beginnt<''' || endete || ''''; |
---|
| 116 | EXECUTE IMMEDIATE s; |
---|
| 117 | n := SQL%ROWCOUNT; |
---|
| 118 | IF n<>1 THEN |
---|
| 119 | dbms_output.put_line( 'SQL: ' || s); |
---|
| 120 | IF :NEW.context = 'delete' OR :NEW.safetoignore = 'true' THEN |
---|
| 121 | dbms_output.put_line( :NEW.featureid || ': Untergangsdatum von ' || n || ' Objekten statt nur einem auf ' || endete || ' gesetzt - ignoriert' ); |
---|
| 122 | :NEW.ignored := 'true'; |
---|
| 123 | RETURN; |
---|
| 124 | ELSE |
---|
| 125 | raise_application_error(-20100, :NEW.featureid || ': Untergangsdatum von ' || n || ' Objekten statt nur einem auf ' || endete || ' gesetzt - Abbruch' ); |
---|
| 126 | END IF; |
---|
| 127 | END IF; |
---|
| 128 | |
---|
| 129 | :NEW.ignored := 'false'; |
---|
| 130 | RETURN; |
---|
| 131 | END delete_feature_trigger; |
---|
| 132 | / |
---|
| 133 | |
---|
| 134 | show errors trigger delete_feature_trigger; |
---|