· 6 years ago · Dec 19, 2019, 06:44 PM
1
2CREATE TABLE Anketa
3(
4 Id_ank INTEGER NOT NULL ,
5 Pol VARCHAR2(100) NOT NULL ,
6 Vozrast INTEGER NOT NULL ,
7 City VARCHAR2(100) NOT NULL ,
8 Predel NUMBER(100) NOT NULL
9);
10
11
12
13CREATE UNIQUE INDEX XPKAnketa ON Anketa
14(Id_ank ASC);
15
16
17
18ALTER TABLE Anketa
19 ADD CONSTRAINT XPKAnketa PRIMARY KEY (Id_ank);
20
21
22
23CREATE TABLE Polzovatel
24(
25 id_user INTEGER NOT NULL ,
26 Name VARCHAR2(100) NOT NULL ,
27 Secondname VARCHAR(100) NOT NULL ,
28 Login VARCHAR2(50) NOT NULL ,
29 Id_ank INTEGER NOT NULL
30);
31
32
33
34CREATE UNIQUE INDEX XPKUser ON Polzovatel
35(id_user ASC,Id_ank ASC);
36
37
38
39ALTER TABLE Polzovatel
40 ADD CONSTRAINT XPKUser PRIMARY KEY (id_user,Id_ank);
41
42
43
44CREATE TABLE Post
45(
46 id_post INTEGER NOT NULL ,
47 id_status INTEGER NOT NULL ,
48 date_razm DATE NULL ,
49 id_user INTEGER NOT NULL ,
50 Id_ank INTEGER NOT NULL
51);
52
53
54
55CREATE UNIQUE INDEX XPKPost ON Post
56(id_post ASC);
57
58
59
60ALTER TABLE Post
61 ADD CONSTRAINT XPKPost PRIMARY KEY (id_post);
62
63
64
65CREATE TABLE Status
66(
67 id_status INTEGER NOT NULL ,
68 Volume INTEGER NOT NULL
69);
70
71
72
73CREATE UNIQUE INDEX XPKStatus ON Status
74(id_status ASC);
75
76
77
78ALTER TABLE Status
79 ADD CONSTRAINT XPKStatus PRIMARY KEY (id_status);
80
81
82
83ALTER TABLE Polzovatel
84 ADD (CONSTRAINT R_15 FOREIGN KEY (Id_ank) REFERENCES Anketa (Id_ank));
85
86
87
88ALTER TABLE Post
89 ADD (CONSTRAINT R_8 FOREIGN KEY (id_status) REFERENCES Status (id_status));
90
91
92
93ALTER TABLE Post
94 ADD (CONSTRAINT R_14 FOREIGN KEY (id_user, Id_ank) REFERENCES Polzovatel (id_user, Id_ank));
95
96
97
98CREATE TRIGGER tD_Anketa AFTER DELETE ON Anketa for each row
99-- ERwin Builtin Trigger
100-- DELETE trigger on Anketa
101DECLARE NUMROWS INTEGER;
102BEGIN
103 /* ERwin Builtin Trigger */
104 /* Anketa Polzovatel on parent delete restrict */
105 /* ERWIN_RELATION:CHECKSUM="0000e08a", PARENT_OWNER="", PARENT_TABLE="Anketa"
106 CHILD_OWNER="", CHILD_TABLE="Polzovatel"
107 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
108 FK_CONSTRAINT="R_15", FK_COLUMNS="Id_ank" */
109 SELECT count(*) INTO NUMROWS
110 FROM Polzovatel
111 WHERE
112 /* %JoinFKPK(Polzovatel,:%Old," = "," AND") */
113 Polzovatel.Id_ank = :old.Id_ank;
114 IF (NUMROWS > 0)
115 THEN
116 raise_application_error(
117 -20001,
118 'Cannot delete Anketa because Polzovatel exists.'
119 );
120 END IF;
121
122
123-- ERwin Builtin Trigger
124END;
125/
126
127CREATE TRIGGER tU_Anketa AFTER UPDATE ON Anketa for each row
128-- ERwin Builtin Trigger
129-- UPDATE trigger on Anketa
130DECLARE NUMROWS INTEGER;
131BEGIN
132 /* ERwin Builtin Trigger */
133 /* Anketa Polzovatel on parent update restrict */
134 /* ERWIN_RELATION:CHECKSUM="0000fc5b", PARENT_OWNER="", PARENT_TABLE="Anketa"
135 CHILD_OWNER="", CHILD_TABLE="Polzovatel"
136 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
137 FK_CONSTRAINT="R_15", FK_COLUMNS="Id_ank" */
138 IF
139 /* %JoinPKPK(:%Old,:%New," <> "," OR ") */
140 :old.Id_ank <> :new.Id_ank
141 THEN
142 SELECT count(*) INTO NUMROWS
143 FROM Polzovatel
144 WHERE
145 /* %JoinFKPK(Polzovatel,:%Old," = "," AND") */
146 Polzovatel.Id_ank = :old.Id_ank;
147 IF (NUMROWS > 0)
148 THEN
149 raise_application_error(
150 -20005,
151 'Cannot update Anketa because Polzovatel exists.'
152 );
153 END IF;
154 END IF;
155
156
157-- ERwin Builtin Trigger
158END;
159/
160
161
162CREATE TRIGGER tI_Polzovatel BEFORE INSERT ON Polzovatel for each row
163-- ERwin Builtin Trigger
164-- INSERT trigger on Polzovatel
165DECLARE NUMROWS INTEGER;
166BEGIN
167 /* ERwin Builtin Trigger */
168 /* Anketa Polzovatel on child insert restrict */
169 /* ERWIN_RELATION:CHECKSUM="0000eb32", PARENT_OWNER="", PARENT_TABLE="Anketa"
170 CHILD_OWNER="", CHILD_TABLE="Polzovatel"
171 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
172 FK_CONSTRAINT="R_15", FK_COLUMNS="Id_ank" */
173 SELECT count(*) INTO NUMROWS
174 FROM Anketa
175 WHERE
176 /* %JoinFKPK(:%New,Anketa," = "," AND") */
177 :new.Id_ank = Anketa.Id_ank;
178 IF (
179 /* %NotnullFK(:%New," IS NOT NULL AND") */
180
181 NUMROWS = 0
182 )
183 THEN
184 raise_application_error(
185 -20002,
186 'Cannot insert Polzovatel because Anketa does not exist.'
187 );
188 END IF;
189
190
191-- ERwin Builtin Trigger
192END;
193/
194
195CREATE TRIGGER tD_Polzovatel AFTER DELETE ON Polzovatel for each row
196-- ERwin Builtin Trigger
197-- DELETE trigger on Polzovatel
198DECLARE NUMROWS INTEGER;
199BEGIN
200 /* ERwin Builtin Trigger */
201 /* Polzovatel Post on parent delete restrict */
202 /* ERWIN_RELATION:CHECKSUM="0000dc79", PARENT_OWNER="", PARENT_TABLE="Polzovatel"
203 CHILD_OWNER="", CHILD_TABLE="Post"
204 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
205 FK_CONSTRAINT="R_14", FK_COLUMNS="id_user""Id_ank" */
206 SELECT count(*) INTO NUMROWS
207 FROM Post
208 WHERE
209 /* %JoinFKPK(Post,:%Old," = "," AND") */
210 Post.id_user = :old.id_user AND
211 Post.Id_ank = :old.Id_ank;
212 IF (NUMROWS > 0)
213 THEN
214 raise_application_error(
215 -20001,
216 'Cannot delete Polzovatel because Post exists.'
217 );
218 END IF;
219
220
221-- ERwin Builtin Trigger
222END;
223/
224
225CREATE TRIGGER tU_Polzovatel AFTER UPDATE ON Polzovatel for each row
226-- ERwin Builtin Trigger
227-- UPDATE trigger on Polzovatel
228DECLARE NUMROWS INTEGER;
229BEGIN
230 /* ERwin Builtin Trigger */
231 /* Polzovatel Post on parent update restrict */
232 /* ERWIN_RELATION:CHECKSUM="00020cd7", PARENT_OWNER="", PARENT_TABLE="Polzovatel"
233 CHILD_OWNER="", CHILD_TABLE="Post"
234 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
235 FK_CONSTRAINT="R_14", FK_COLUMNS="id_user""Id_ank" */
236 IF
237 /* %JoinPKPK(:%Old,:%New," <> "," OR ") */
238 :old.id_user <> :new.id_user OR
239 :old.Id_ank <> :new.Id_ank
240 THEN
241 SELECT count(*) INTO NUMROWS
242 FROM Post
243 WHERE
244 /* %JoinFKPK(Post,:%Old," = "," AND") */
245 Post.id_user = :old.id_user AND
246 Post.Id_ank = :old.Id_ank;
247 IF (NUMROWS > 0)
248 THEN
249 raise_application_error(
250 -20005,
251 'Cannot update Polzovatel because Post exists.'
252 );
253 END IF;
254 END IF;
255
256 /* ERwin Builtin Trigger */
257 /* Anketa Polzovatel on child update restrict */
258 /* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Anketa"
259 CHILD_OWNER="", CHILD_TABLE="Polzovatel"
260 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
261 FK_CONSTRAINT="R_15", FK_COLUMNS="Id_ank" */
262 SELECT count(*) INTO NUMROWS
263 FROM Anketa
264 WHERE
265 /* %JoinFKPK(:%New,Anketa," = "," AND") */
266 :new.Id_ank = Anketa.Id_ank;
267 IF (
268 /* %NotnullFK(:%New," IS NOT NULL AND") */
269
270 NUMROWS = 0
271 )
272 THEN
273 raise_application_error(
274 -20007,
275 'Cannot update Polzovatel because Anketa does not exist.'
276 );
277 END IF;
278
279
280-- ERwin Builtin Trigger
281END;
282/
283
284
285CREATE TRIGGER tI_Post BEFORE INSERT ON Post for each row
286-- ERwin Builtin Trigger
287-- INSERT trigger on Post
288DECLARE NUMROWS INTEGER;
289BEGIN
290 /* ERwin Builtin Trigger */
291 /* Polzovatel Post on child insert restrict */
292 /* ERWIN_RELATION:CHECKSUM="0001ed5a", PARENT_OWNER="", PARENT_TABLE="Polzovatel"
293 CHILD_OWNER="", CHILD_TABLE="Post"
294 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
295 FK_CONSTRAINT="R_14", FK_COLUMNS="id_user""Id_ank" */
296 SELECT count(*) INTO NUMROWS
297 FROM Polzovatel
298 WHERE
299 /* %JoinFKPK(:%New,Polzovatel," = "," AND") */
300 :new.id_user = Polzovatel.id_user AND
301 :new.Id_ank = Polzovatel.Id_ank;
302 IF (
303 /* %NotnullFK(:%New," IS NOT NULL AND") */
304
305 NUMROWS = 0
306 )
307 THEN
308 raise_application_error(
309 -20002,
310 'Cannot insert Post because Polzovatel does not exist.'
311 );
312 END IF;
313
314 /* ERwin Builtin Trigger */
315 /* Status Post on child insert restrict */
316 /* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Status"
317 CHILD_OWNER="", CHILD_TABLE="Post"
318 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
319 FK_CONSTRAINT="R_8", FK_COLUMNS="id_status" */
320 SELECT count(*) INTO NUMROWS
321 FROM Status
322 WHERE
323 /* %JoinFKPK(:%New,Status," = "," AND") */
324 :new.id_status = Status.id_status;
325 IF (
326 /* %NotnullFK(:%New," IS NOT NULL AND") */
327
328 NUMROWS = 0
329 )
330 THEN
331 raise_application_error(
332 -20002,
333 'Cannot insert Post because Status does not exist.'
334 );
335 END IF;
336
337
338-- ERwin Builtin Trigger
339END;
340/
341
342CREATE TRIGGER tU_Post AFTER UPDATE ON Post for each row
343-- ERwin Builtin Trigger
344-- UPDATE trigger on Post
345DECLARE NUMROWS INTEGER;
346BEGIN
347 /* ERwin Builtin Trigger */
348 /* Polzovatel Post on child update restrict */
349 /* ERWIN_RELATION:CHECKSUM="0001f1ba", PARENT_OWNER="", PARENT_TABLE="Polzovatel"
350 CHILD_OWNER="", CHILD_TABLE="Post"
351 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
352 FK_CONSTRAINT="R_14", FK_COLUMNS="id_user""Id_ank" */
353 SELECT count(*) INTO NUMROWS
354 FROM Polzovatel
355 WHERE
356 /* %JoinFKPK(:%New,Polzovatel," = "," AND") */
357 :new.id_user = Polzovatel.id_user AND
358 :new.Id_ank = Polzovatel.Id_ank;
359 IF (
360 /* %NotnullFK(:%New," IS NOT NULL AND") */
361
362 NUMROWS = 0
363 )
364 THEN
365 raise_application_error(
366 -20007,
367 'Cannot update Post because Polzovatel does not exist.'
368 );
369 END IF;
370
371 /* ERwin Builtin Trigger */
372 /* Status Post on child update restrict */
373 /* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Status"
374 CHILD_OWNER="", CHILD_TABLE="Post"
375 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
376 FK_CONSTRAINT="R_8", FK_COLUMNS="id_status" */
377 SELECT count(*) INTO NUMROWS
378 FROM Status
379 WHERE
380 /* %JoinFKPK(:%New,Status," = "," AND") */
381 :new.id_status = Status.id_status;
382 IF (
383 /* %NotnullFK(:%New," IS NOT NULL AND") */
384
385 NUMROWS = 0
386 )
387 THEN
388 raise_application_error(
389 -20007,
390 'Cannot update Post because Status does not exist.'
391 );
392 END IF;
393
394
395-- ERwin Builtin Trigger
396END;
397/
398
399
400CREATE TRIGGER tD_Status AFTER DELETE ON Status for each row
401-- ERwin Builtin Trigger
402-- DELETE trigger on Status
403DECLARE NUMROWS INTEGER;
404BEGIN
405 /* ERwin Builtin Trigger */
406 /* Status Post on parent delete restrict */
407 /* ERWIN_RELATION:CHECKSUM="0000ce79", PARENT_OWNER="", PARENT_TABLE="Status"
408 CHILD_OWNER="", CHILD_TABLE="Post"
409 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
410 FK_CONSTRAINT="R_8", FK_COLUMNS="id_status" */
411 SELECT count(*) INTO NUMROWS
412 FROM Post
413 WHERE
414 /* %JoinFKPK(Post,:%Old," = "," AND") */
415 Post.id_status = :old.id_status;
416 IF (NUMROWS > 0)
417 THEN
418 raise_application_error(
419 -20001,
420 'Cannot delete Status because Post exists.'
421 );
422 END IF;
423
424
425-- ERwin Builtin Trigger
426END;
427/
428
429CREATE TRIGGER tU_Status AFTER UPDATE ON Status for each row
430-- ERwin Builtin Trigger
431-- UPDATE trigger on Status
432DECLARE NUMROWS INTEGER;
433BEGIN
434 /* ERwin Builtin Trigger */
435 /* Status Post on parent update restrict */
436 /* ERWIN_RELATION:CHECKSUM="0000f81d", PARENT_OWNER="", PARENT_TABLE="Status"
437 CHILD_OWNER="", CHILD_TABLE="Post"
438 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
439 FK_CONSTRAINT="R_8", FK_COLUMNS="id_status" */
440 IF
441 /* %JoinPKPK(:%Old,:%New," <> "," OR ") */
442 :old.id_status <> :new.id_status
443 THEN
444 SELECT count(*) INTO NUMROWS
445 FROM Post
446 WHERE
447 /* %JoinFKPK(Post,:%Old," = "," AND") */
448 Post.id_status = :old.id_status;
449 IF (NUMROWS > 0)
450 THEN
451 raise_application_error(
452 -20005,
453 'Cannot update Status because Post exists.'
454 );
455 END IF;
456 END IF;
457
458
459-- ERwin Builtin Trigger
460END;
461/