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