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; |
---|