- Timestamp:
- 01/25/13 16:01:22 (11 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/data/konvert/postnas_0.7/alkis-trigger-hist-oracle.sql
r209 r210 47 47 DECLARE 48 48 s varchar2(2047); 49 gml_id varchar2(13); 49 alt_id varchar2(16); 50 neu_id varchar2(16); 51 beginnt varchar2(20); 50 52 endete varchar2(20); 51 53 n INTEGER; … … 53 55 :NEW.typename := upper(substr(:NEW.typename, 1, 30)); 54 56 :NEW.context := lower(:NEW.context); 55 gml_id := substr(:NEW.featureid, 1, 16);56 57 57 IF :NEW.context IS NULL THEN 58 58 :NEW.context := 'delete'; 59 59 END IF; 60 60 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 61 92 IF :NEW.context='delete' THEN 62 SELECT to_char(sysdate, ' UTC','YYYY-MM-DD"T"HH24:MI:SS"Z"') INTO endete FROM dual;93 SELECT to_char(sysdate, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') INTO endete FROM dual; 63 94 64 95 ELSIF :NEW.context='replace' THEN … … 71 102 END IF; 72 103 73 IF :NEW.replacedBy IS NULL OR length(:NEW.replacedBy)<16 THEN 74 IF :NEW.safetoignore = 'true' THEN 75 dbms_output.put_line( :NEW.featureid || ': Nachfolger ''' || :NEW.replacedBy || ''' nicht richtig gesetzt - ignoriert' ); 76 :NEW.ignored := 'true'; 77 RETURN; 78 ELSE 79 raise_application_error(-20100, :NEW.featureid || ': Nachfolger ''' || :NEW.replacedBy || ''' nicht richtig gesetzt - Abbruch'); 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 ; 80 116 END IF; 81 END IF; 82 83 IF length(:NEW.replacedBy)=16 THEN 84 EXECUTE IMMEDIATE 'SELECT MAX(beginnt) FROM ' || :NEW.typename || 85 ' WHERE gml_id=''' || :NEW.replacedBy || ''' AND endet IS NULL' 86 INTO endete; 87 ELSE 88 -- replaceBy mit Timestamp 89 EXECUTE IMMEDIATE 'SELECT beginnt FROM ' || :NEW.typename || 90 ' WHERE identifier=''urn:adv:oid:' || :NEW.replacedBy || '''' 91 INTO endete; 92 IF endete IS NULL THEN 93 EXECUTE IMMEDIATE 'SELECT MAX(beginnt) FROM ' || :NEW.typename || 94 ' WHERE gml_id=''' || substr(:NEW.replacedBy,1,16) || ''' AND endet IS NULL' 95 INTO endete; 96 END IF; 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.'); 97 121 END IF; 98 122 99 123 IF endete IS NULL THEN 100 IF :NEW.safetoignore = 'true' THEN101 dbms_output.put_line(:NEW.featureid || ': Nachfolger ''' || :NEW.replacedBy || ''' nicht gefunden - ignoriert');102 :NEW.ignored := 'true';103 RETURN;104 ELSE105 raise_application_error(-20100, :NEW.featureid || ': Nachfolger ''' || :NEW.replacedBy || ''' nicht gefunden - Abbruch');106 END IF;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; 107 131 END IF; 108 132 ELSE … … 110 134 END IF; 111 135 112 s := 'UPDATE ' || :NEW.typename 113 || ' SET endet=''' || endete || '''' 114 || ' WHERE gml_id=''' || gml_id || '''' 115 || ' AND endet IS NULL' 116 || ' AND beginnt<''' || endete || ''''; 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'; 117 157 EXECUTE IMMEDIATE s; 118 158 n := SQL%ROWCOUNT;
Note: See TracChangeset
for help on using the changeset viewer.