· 7 years ago · Nov 12, 2018, 05:18 AM
1CREATE TABLE Abonent
2(
3 Tel_number INTEGER NOT NULL ,
4 Birth_date DATE NULL ,
5 Full_name VARCHAR2(20) NULL ,
6 Placement_id INTEGER NOT NULL
7);
8
9
10
11CREATE UNIQUE INDEX XPKAbonent ON Abonent
12(Tel_number ASC);
13
14
15
16ALTER TABLE Abonent
17 ADD CONSTRAINT XPKAbonent PRIMARY KEY (Tel_number);
18
19
20
21CREATE TABLE Placement
22(
23 Name VARCHAR2(20) NULL ,
24 Type INTEGER NULL ,
25 Id INTEGER NOT NULL ,
26 Unit_id INTEGER NOT NULL
27);
28
29
30
31CREATE UNIQUE INDEX XPKPlacement ON Placement
32(Id ASC);
33
34
35
36ALTER TABLE Placement
37 ADD CONSTRAINT XPKPlacement PRIMARY KEY (Id);
38
39
40
41CREATE TABLE Talk
42(
43 Id INTEGER NOT NULL ,
44 Talk_date DATE NULL ,
45 Duration INTEGER NULL ,
46 Ab_number INTEGER NOT NULL
47);
48
49
50
51CREATE UNIQUE INDEX XPKTalk ON Talk
52(Id ASC);
53
54
55
56ALTER TABLE Talk
57 ADD CONSTRAINT XPKTalk PRIMARY KEY (Id);
58
59
60
61CREATE TABLE Unit
62(
63 Id INTEGER NOT NULL ,
64 Name VARCHAR2(20) NULL
65);
66
67
68
69CREATE UNIQUE INDEX XPKUnit ON Unit
70(Id ASC);
71
72
73
74ALTER TABLE Unit
75 ADD CONSTRAINT XPKUnit PRIMARY KEY (Id);
76
77
78
79ALTER TABLE Abonent
80 ADD (CONSTRAINT R_18 FOREIGN KEY (Placement_id) REFERENCES Placement (Id));
81
82
83
84ALTER TABLE Placement
85 ADD (CONSTRAINT R_17 FOREIGN KEY (Unit_id) REFERENCES Unit (Id));
86
87
88
89ALTER TABLE Talk
90 ADD (CONSTRAINT R_19 FOREIGN KEY (Ab_number) REFERENCES Abonent (Tel_number));
91
92
93
94CREATE TRIGGER tI_Abonent BEFORE INSERT ON Abonent for each row
95-- ERwin Builtin Trigger
96-- INSERT trigger on Abonent
97DECLARE NUMROWS INTEGER;
98BEGIN
99 /* ERwin Builtin Trigger */
100 /* Placement Abonent on child insert restrict */
101 /* ERWIN_RELATION:CHECKSUM="0000f185", PARENT_OWNER="", PARENT_TABLE="Placement"
102 CHILD_OWNER="", CHILD_TABLE="Abonent"
103 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
104 FK_CONSTRAINT="R_18", FK_COLUMNS="Placement_id" */
105 SELECT count(*) INTO NUMROWS
106 FROM Placement
107 WHERE
108 /* %JoinFKPK(:%New,Placement," = "," AND") */
109 :new.Placement_id = Placement.Id;
110 IF (
111 /* %NotnullFK(:%New," IS NOT NULL AND") */
112
113 NUMROWS = 0
114 )
115 THEN
116 raise_application_error(
117 -20002,
118 'Cannot insert Abonent because Placement does not exist.'
119 );
120 END IF;
121
122
123-- ERwin Builtin Trigger
124END;
125/
126
127CREATE TRIGGER tD_Abonent AFTER DELETE ON Abonent for each row
128-- ERwin Builtin Trigger
129-- DELETE trigger on Abonent
130DECLARE NUMROWS INTEGER;
131BEGIN
132 /* ERwin Builtin Trigger */
133 /* Abonent Talk on parent delete restrict */
134 /* ERWIN_RELATION:CHECKSUM="0000c69a", PARENT_OWNER="", PARENT_TABLE="Abonent"
135 CHILD_OWNER="", CHILD_TABLE="Talk"
136 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
137 FK_CONSTRAINT="R_19", FK_COLUMNS="Ab_number" */
138 SELECT count(*) INTO NUMROWS
139 FROM Talk
140 WHERE
141 /* %JoinFKPK(Talk,:%Old," = "," AND") */
142 Talk.Ab_number = :old.Tel_number;
143 IF (NUMROWS > 0)
144 THEN
145 raise_application_error(
146 -20001,
147 'Cannot delete Abonent because Talk exists.'
148 );
149 END IF;
150
151
152-- ERwin Builtin Trigger
153END;
154/
155
156CREATE TRIGGER tU_Abonent AFTER UPDATE ON Abonent for each row
157-- ERwin Builtin Trigger
158-- UPDATE trigger on Abonent
159DECLARE NUMROWS INTEGER;
160BEGIN
161 /* ERwin Builtin Trigger */
162 /* Abonent Talk on parent update restrict */
163 /* ERWIN_RELATION:CHECKSUM="0001ee9d", PARENT_OWNER="", PARENT_TABLE="Abonent"
164 CHILD_OWNER="", CHILD_TABLE="Talk"
165 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
166 FK_CONSTRAINT="R_19", FK_COLUMNS="Ab_number" */
167 IF
168 /* %JoinPKPK(:%Old,:%New," <> "," OR ") */
169 :old.Tel_number <> :new.Tel_number
170 THEN
171 SELECT count(*) INTO NUMROWS
172 FROM Talk
173 WHERE
174 /* %JoinFKPK(Talk,:%Old," = "," AND") */
175 Talk.Ab_number = :old.Tel_number;
176 IF (NUMROWS > 0)
177 THEN
178 raise_application_error(
179 -20005,
180 'Cannot update Abonent because Talk exists.'
181 );
182 END IF;
183 END IF;
184
185 /* ERwin Builtin Trigger */
186 /* Placement Abonent on child update restrict */
187 /* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Placement"
188 CHILD_OWNER="", CHILD_TABLE="Abonent"
189 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
190 FK_CONSTRAINT="R_18", FK_COLUMNS="Placement_id" */
191 SELECT count(*) INTO NUMROWS
192 FROM Placement
193 WHERE
194 /* %JoinFKPK(:%New,Placement," = "," AND") */
195 :new.Placement_id = Placement.Id;
196 IF (
197 /* %NotnullFK(:%New," IS NOT NULL AND") */
198
199 NUMROWS = 0
200 )
201 THEN
202 raise_application_error(
203 -20007,
204 'Cannot update Abonent because Placement does not exist.'
205 );
206 END IF;
207
208
209-- ERwin Builtin Trigger
210END;
211/
212
213
214CREATE TRIGGER tI_Placement BEFORE INSERT ON Placement for each row
215-- ERwin Builtin Trigger
216-- INSERT trigger on Placement
217DECLARE NUMROWS INTEGER;
218BEGIN
219 /* ERwin Builtin Trigger */
220 /* Unit Placement on child insert restrict */
221 /* ERWIN_RELATION:CHECKSUM="0000e4f5", PARENT_OWNER="", PARENT_TABLE="Unit"
222 CHILD_OWNER="", CHILD_TABLE="Placement"
223 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
224 FK_CONSTRAINT="R_17", FK_COLUMNS="Unit_id" */
225 SELECT count(*) INTO NUMROWS
226 FROM Unit
227 WHERE
228 /* %JoinFKPK(:%New,Unit," = "," AND") */
229 :new.Unit_id = Unit.Id;
230 IF (
231 /* %NotnullFK(:%New," IS NOT NULL AND") */
232
233 NUMROWS = 0
234 )
235 THEN
236 raise_application_error(
237 -20002,
238 'Cannot insert Placement because Unit does not exist.'
239 );
240 END IF;
241
242
243-- ERwin Builtin Trigger
244END;
245/
246
247CREATE TRIGGER tD_Placement AFTER DELETE ON Placement for each row
248-- ERwin Builtin Trigger
249-- DELETE trigger on Placement
250DECLARE NUMROWS INTEGER;
251BEGIN
252 /* ERwin Builtin Trigger */
253 /* Placement Abonent on parent delete restrict */
254 /* ERWIN_RELATION:CHECKSUM="0000cd56", PARENT_OWNER="", PARENT_TABLE="Placement"
255 CHILD_OWNER="", CHILD_TABLE="Abonent"
256 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
257 FK_CONSTRAINT="R_18", FK_COLUMNS="Placement_id" */
258 SELECT count(*) INTO NUMROWS
259 FROM Abonent
260 WHERE
261 /* %JoinFKPK(Abonent,:%Old," = "," AND") */
262 Abonent.Placement_id = :old.Id;
263 IF (NUMROWS > 0)
264 THEN
265 raise_application_error(
266 -20001,
267 'Cannot delete Placement because Abonent exists.'
268 );
269 END IF;
270
271
272-- ERwin Builtin Trigger
273END;
274/
275
276CREATE TRIGGER tU_Placement AFTER UPDATE ON Placement for each row
277-- ERwin Builtin Trigger
278-- UPDATE trigger on Placement
279DECLARE NUMROWS INTEGER;
280BEGIN
281 /* ERwin Builtin Trigger */
282 /* Placement Abonent on parent update restrict */
283 /* ERWIN_RELATION:CHECKSUM="0001e294", PARENT_OWNER="", PARENT_TABLE="Placement"
284 CHILD_OWNER="", CHILD_TABLE="Abonent"
285 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
286 FK_CONSTRAINT="R_18", FK_COLUMNS="Placement_id" */
287 IF
288 /* %JoinPKPK(:%Old,:%New," <> "," OR ") */
289 :old.Id <> :new.Id
290 THEN
291 SELECT count(*) INTO NUMROWS
292 FROM Abonent
293 WHERE
294 /* %JoinFKPK(Abonent,:%Old," = "," AND") */
295 Abonent.Placement_id = :old.Id;
296 IF (NUMROWS > 0)
297 THEN
298 raise_application_error(
299 -20005,
300 'Cannot update Placement because Abonent exists.'
301 );
302 END IF;
303 END IF;
304
305 /* ERwin Builtin Trigger */
306 /* Unit Placement on child update restrict */
307 /* ERWIN_RELATION:CHECKSUM="00000000", PARENT_OWNER="", PARENT_TABLE="Unit"
308 CHILD_OWNER="", CHILD_TABLE="Placement"
309 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
310 FK_CONSTRAINT="R_17", FK_COLUMNS="Unit_id" */
311 SELECT count(*) INTO NUMROWS
312 FROM Unit
313 WHERE
314 /* %JoinFKPK(:%New,Unit," = "," AND") */
315 :new.Unit_id = Unit.Id;
316 IF (
317 /* %NotnullFK(:%New," IS NOT NULL AND") */
318
319 NUMROWS = 0
320 )
321 THEN
322 raise_application_error(
323 -20007,
324 'Cannot update Placement because Unit does not exist.'
325 );
326 END IF;
327
328
329-- ERwin Builtin Trigger
330END;
331/
332
333
334CREATE TRIGGER tI_Talk BEFORE INSERT ON Talk for each row
335-- ERwin Builtin Trigger
336-- INSERT trigger on Talk
337DECLARE NUMROWS INTEGER;
338BEGIN
339 /* ERwin Builtin Trigger */
340 /* Abonent Talk on child insert restrict */
341 /* ERWIN_RELATION:CHECKSUM="0000dbbb", PARENT_OWNER="", PARENT_TABLE="Abonent"
342 CHILD_OWNER="", CHILD_TABLE="Talk"
343 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
344 FK_CONSTRAINT="R_19", FK_COLUMNS="Ab_number" */
345 SELECT count(*) INTO NUMROWS
346 FROM Abonent
347 WHERE
348 /* %JoinFKPK(:%New,Abonent," = "," AND") */
349 :new.Ab_number = Abonent.Tel_number;
350 IF (
351 /* %NotnullFK(:%New," IS NOT NULL AND") */
352
353 NUMROWS = 0
354 )
355 THEN
356 raise_application_error(
357 -20002,
358 'Cannot insert Talk because Abonent does not exist.'
359 );
360 END IF;
361
362
363-- ERwin Builtin Trigger
364END;
365/
366
367CREATE TRIGGER tU_Talk AFTER UPDATE ON Talk for each row
368-- ERwin Builtin Trigger
369-- UPDATE trigger on Talk
370DECLARE NUMROWS INTEGER;
371BEGIN
372 /* ERwin Builtin Trigger */
373 /* Abonent Talk on child update restrict */
374 /* ERWIN_RELATION:CHECKSUM="0000dae0", PARENT_OWNER="", PARENT_TABLE="Abonent"
375 CHILD_OWNER="", CHILD_TABLE="Talk"
376 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
377 FK_CONSTRAINT="R_19", FK_COLUMNS="Ab_number" */
378 SELECT count(*) INTO NUMROWS
379 FROM Abonent
380 WHERE
381 /* %JoinFKPK(:%New,Abonent," = "," AND") */
382 :new.Ab_number = Abonent.Tel_number;
383 IF (
384 /* %NotnullFK(:%New," IS NOT NULL AND") */
385
386 NUMROWS = 0
387 )
388 THEN
389 raise_application_error(
390 -20007,
391 'Cannot update Talk because Abonent does not exist.'
392 );
393 END IF;
394
395
396-- ERwin Builtin Trigger
397END;
398/
399
400
401CREATE TRIGGER tD_Unit AFTER DELETE ON Unit for each row
402-- ERwin Builtin Trigger
403-- DELETE trigger on Unit
404DECLARE NUMROWS INTEGER;
405BEGIN
406 /* ERwin Builtin Trigger */
407 /* Unit Placement on parent delete restrict */
408 /* ERWIN_RELATION:CHECKSUM="0000ce63", PARENT_OWNER="", PARENT_TABLE="Unit"
409 CHILD_OWNER="", CHILD_TABLE="Placement"
410 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
411 FK_CONSTRAINT="R_17", FK_COLUMNS="Unit_id" */
412 SELECT count(*) INTO NUMROWS
413 FROM Placement
414 WHERE
415 /* %JoinFKPK(Placement,:%Old," = "," AND") */
416 Placement.Unit_id = :old.Id;
417 IF (NUMROWS > 0)
418 THEN
419 raise_application_error(
420 -20001,
421 'Cannot delete Unit because Placement exists.'
422 );
423 END IF;
424
425
426-- ERwin Builtin Trigger
427END;
428/
429
430CREATE TRIGGER tU_Unit AFTER UPDATE ON Unit for each row
431-- ERwin Builtin Trigger
432-- UPDATE trigger on Unit
433DECLARE NUMROWS INTEGER;
434BEGIN
435 /* ERwin Builtin Trigger */
436 /* Unit Placement on parent update restrict */
437 /* ERWIN_RELATION:CHECKSUM="0000f226", PARENT_OWNER="", PARENT_TABLE="Unit"
438 CHILD_OWNER="", CHILD_TABLE="Placement"
439 P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
440 FK_CONSTRAINT="R_17", FK_COLUMNS="Unit_id" */
441 IF
442 /* %JoinPKPK(:%Old,:%New," <> "," OR ") */
443 :old.Id <> :new.Id
444 THEN
445 SELECT count(*) INTO NUMROWS
446 FROM Placement
447 WHERE
448 /* %JoinFKPK(Placement,:%Old," = "," AND") */
449 Placement.Unit_id = :old.Id;
450 IF (NUMROWS > 0)
451 THEN
452 raise_application_error(
453 -20005,
454 'Cannot update Unit because Placement exists.'
455 );
456 END IF;
457 END IF;
458
459
460-- ERwin Builtin Trigger
461END;
462/
463
464-----------------------------------------------------------------------------------------
465
466ALTER TABLE Talk
467DROP CONSTRAINT R_19;
468
469
470
471ALTER TABLE Abonent
472DROP CONSTRAINT R_18;
473
474
475
476ALTER TABLE Placement
477DROP CONSTRAINT R_17;
478
479
480
481ALTER TABLE Talk
482DROP PRIMARY KEY CASCADE DROP INDEX;
483
484
485
486ALTER TABLE Abonent
487DROP PRIMARY KEY CASCADE DROP INDEX;
488
489
490
491ALTER TABLE Placement
492DROP PRIMARY KEY CASCADE DROP INDEX;
493
494
495
496ALTER TABLE Unit
497DROP PRIMARY KEY CASCADE DROP INDEX;
498
499
500
501DROP INDEX XPKTalk;
502
503
504
505DROP TABLE Talk CASCADE CONSTRAINTS PURGE;
506
507
508
509DROP INDEX XPKAbonent;
510
511
512
513DROP TABLE Abonent CASCADE CONSTRAINTS PURGE;
514
515
516
517DROP INDEX XPKPlacement;
518
519
520
521DROP TABLE Placement CASCADE CONSTRAINTS PURGE;
522
523
524
525DROP INDEX XPKUnit;
526
527
528
529DROP TABLE Unit CASCADE CONSTRAINTS PURGE;
530
531
532
533
534--------------------------------------------------------------------------------------------------
535---UNITS---
536INSERT
537INTO UNIT
538VALUES (1, 'First unit');
539---PLACEMENTS---
540INSERT
541INTO PLACEMENT
542 (Name, Id, Unit_id)
543VALUES ('First placement', 1001, 1);
544
545INSERT
546INTO PLACEMENT
547 (Name, Id, Unit_id)
548VALUES ('Second placement', 1002, 1);
549---ABONENTS---
550INSERT
551INTO ABONENT
552VALUES (
553 3211766,
554 TO_DATE('1983/05/03', 'yyyy/mm/dd'),
555 'Falcon Fred',
556 1001
557 );
558
559INSERT
560INTO ABONENT
561VALUES (
562 3212882,
563 TO_DATE('1967/04/22', 'yyyy/mm/dd'),
564 'Ingram Irene',
565 1001
566 );
567
568INSERT
569INTO ABONENT
570VALUES (
571 3217491,
572 TO_DATE('1970/01/21', 'yyyy/mm/dd'),
573 'Ahmad Jabbar',
574 1002
575 );
576
577---TALKS---
578INSERT
579INTO TALK
580VALUES (
581 10002,
582 TO_DATE('2018/05/03 22:12:43', 'yyyy/mm/dd hh24:mi:ss'),
583 203,
584 3211766
585 );
586
587INSERT
588INTO TALK
589VALUES (
590 10003,
591 TO_DATE('2018/05/03 23:59:02', 'yyyy/mm/dd hh24:mi:ss'),
592 126,
593 3211766
594 );
595
596INSERT
597INTO TALK
598VALUES (
599 10001,
600 TO_DATE('2018/04/12 11:23:08', 'yyyy/mm/dd hh24:mi:ss'),
601 596,
602 3212882
603 );
604
605INSERT
606INTO TALK
607VALUES (
608 10004,
609 TO_DATE('2018/06/01 15:01:28', 'yyyy/mm/dd hh24:mi:ss'),
610 339,
611 3217491
612 );