· 2 years ago · Oct 16, 2022, 07:30 PM
1DROP TABLE IF EXISTS сотрудник CASCADE;
2CREATE TABLE IF NOT EXISTS сотрудник (
3 ФИО char(50) NOT NULL,
4 Должность char(50) NULL,
5 Отдел char(50) NULL,
6 Место numeric(10,2) NULL,
7 email char(50) NOT NULL,
8 primary key (email)
9);
10
11DROP TABLE IF EXISTS оборудование CASCADE;
12CREATE TABLE IF NOT EXISTS оборудование (
13 Инвентарный_номер numeric(10,2) NOT NULL,
14 Название char(50) NOT NULL,
15 Производитель char(10) NULL,
16 Цена numeric(7,2) NULL,
17 primary key (Инвентарный_номер)
18);
19
20DROP TABLE IF EXISTS инженер_поддержки CASCADE;
21CREATE TABLE IF NOT EXISTS инженер_поддержки (
22 ФИО char(50) NOT NULL,
23 email char(50) NOT NULL,
24 Телефон char(15) NULL,
25 primary key (email)
26);
27
28DROP TABLE IF EXISTS заявка CASCADE;
29CREATE TABLE IF NOT EXISTS заявка (
30 Номер_заявки numeric(10,2) NOT NULL,
31 Дата DATE NOT NULL,
32 Статус char(10) NOT NULL,
33 Оборудование NUMERIC (10,2) NULL,
34 Вид_работы char(100) NOT NULL,
35 Почта_сотрудника char(50) NOT NULL REFERENCES сотрудник (email),
36 Инженер char(50) NOT NULL,
37 Отчет_о_работе numeric(10,2) NULL UNIQUE,
38 PRIMARY KEY (Номер_заявки),
39 FOREIGN KEY (Оборудование) REFERENCES оборудование (Инвентарный_номер),
40 FOREIGN KEY (Инженер) REFERENCES инженер_поддержки (email)
41);
42
43DROP TABLE IF EXISTS отчет CASCADE;
44CREATE TABLE IF NOT EXISTS отчет (
45 Идентификатор numeric(10,2) NOT NULL,
46 Номер_заявки numeric(10,2) NOT NULL,
47 Дата DATE NOT NULL,
48 Состояние_оборудования char(20) NOT NULL,
49 Проделанная_работа char(100) NOT NULL,
50 Инженер char(50) NOT NULL,
51 FOREIGN KEY (Номер_заявки) REFERENCES заявка (Номер_заявки),
52 FOREIGN KEY (Инженер) REFERENCES инженер_поддержки (email),
53 PRIMARY KEY (Идентификатор),
54 FOREIGN KEY (Идентификатор) REFERENCES заявка (Отчет_о_работе)
55);
56
57DROP TABLE IF EXISTS оборудование_у_сотрудника CASCADE;
58CREATE TABLE IF NOT EXISTS оборудование_у_сотрудника (
59 Инвентарный_номер_оборудования numeric(10,2) NOT NULL,
60 Почта_сотрудника char(50) NOT NULL,
61 Дата DATE NOT NULL,
62 FOREIGN KEY (Инвентарный_номер_оборудования) REFERENCES оборудование (Инвентарный_номер),
63 FOREIGN KEY (Почта_сотрудника) REFERENCES сотрудник (email)
64);
65
66CREATE OR REPLACE FUNCTION check_phone() RETURNS TRIGGER
67LANGUAGE plpgsql
68NOT leakproof
69AS $BODY$
70BEGIN
71 IF NEW.Телефон NOT LIKE '___-___-__-%' THEN
72 NEW.Телефон:=NULL;
73 END IF;
74 RETURN NEW;
75END;
76$BODY$;
77
78CREATE OR REPLACE TRIGGER инженер_поддержки_BEFORE_INSERT BEFORE INSERT OR UPDATE
79ON инженер_поддержки
80FOR EACH ROW
81EXECUTE FUNCTION check_phone();
82
83CREATE OR REPLACE FUNCTION check_date() RETURNS TRIGGER
84LANGUAGE plpgsql
85NOT leakproof
86AS $BODY$
87BEGIN
88 IF NEW.Дата between current_date and '1970-01-01' THEN
89 RETURN NEW;
90 END IF;
91 raise NOTICE 'Invalid date, row wasnt add';
92 RETURN NULL;
93END;
94$BODY$;
95
96CREATE OR REPLACE TRIGGER оборудование_у_сотрудника_BEFORE_INSERT BEFORE INSERT OR UPDATE
97ON оборудование_у_сотрудника
98FOR EACH ROW
99EXECUTE FUNCTION check_date();
100
101CREATE OR REPLACE TRIGGER заявка_BEFORE_INSERT BEFORE INSERT OR UPDATE
102ON заявка
103FOR EACH ROW
104EXECUTE FUNCTION check_date();
105
106CREATE OR REPLACE FUNCTION check_email() RETURNS TRIGGER
107LANGUAGE plpgsql
108NOT leakproof
109AS $BODY$
110BEGIN
111 IF NEW.email NOT LIKE '%@%' THEN
112 raise notice 'Invalid email';
113 return null;
114 END IF;
115 RETURN NEW;
116END;
117$BODY$;
118
119CREATE OR REPLACE TRIGGER сотрудник_BEFORE_INSERT BEFORE INSERT OR UPDATE
120ON сотрудник
121FOR EACH ROW
122EXECUTE FUNCTION check_email();
123
124CREATE OR REPLACE TRIGGER инженер_поддерж_BEFORE_INSERT BEFORE INSERT OR UPDATE
125ON инженер_поддержки
126FOR EACH ROW
127EXECUTE FUNCTION check_email();
128
129INSERT INTO инженер_поддержки VALUES
130('Вася','vasyao@gmail.com','123-456-78-90'),
131('Петя','kloun@mail.ru','234-567-89-01'),
132('Альбедо','best_boy@gmail.com','12eds');
133
134INSERT INTO сотрудник VALUES
135('Дмитрий Андреевич Яустала','программист','КИБ',17,'dmitry@gmail.com'),
136('Дмитрий Андреевич Яустала','программист','КИБ',17,'dmitry2@gmail.com');
137
138INSERT INTO оборудование_у_сотрудника VALUES
139(10, 'dmitry2@gmail.com', '2022-11-11');
140
141
142
143
144SELECT * FROM оборудование_у_сотрудника;