MPpns0tu

1DROP TABLE IF EXISTS H_Students;
2CREATE TABLE H_Students(
3	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
4	BeginDate DATETIME,
5    EndDate DATETIME,
6    
7    Id INTEGER,
8    Name VARCHAR(150),
9	Num INTEGER,
10	ClassNum INTEGER,
11	ClassLetter CHAR(1),
12	Birthday DATE,
13	EGN CHAR(10),
14	PhysicsExamResult NUMERIC(3,2)
15);
16
17DROP TABLE IF EXISTS H_Subjects;
18CREATE TABLE H_Subjects(
19	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
20	BeginDate DATETIME,
21    EndDate DATETIME,
22
23	Id INTEGER,
24	Name VARCHAR(100)
25);
26
27DROP TABLE IF EXISTS H_StudentMarks;
28CREATE TABLE H_StudentMarks(
29	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
30	BeginDate DATETIME,
31    EndDate DATETIME,
32    
33	StudentId INTEGER,
34	SubjectId INTEGER,
35	ExamDate DATETIME,
36	Mark NUMERIC(3,2)
37);
38
39DROP TABLE IF EXISTS H_MarkWords;
40CREATE TABLE H_MarkWords(
41
42	UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
43	BeginDate DATETIME,
44    EndDate DATETIME,
45
46	RangeStart NUMERIC(3,2),
47	RangeEnd NUMERIC(3,2),
48	MarkAsWord VARCHAR(15)
49);
50
51
52-- TRIGERS H_Students:
53DROP TRIGGER IF exists H_StudentsInsert;
54DELIMITER $
55CREATE TRIGGER H_StudentsInsert
56AFTER INSERT
57ON Students FOR EACH ROW
58BEGIN 
59	INSERT INTO H_Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN, BeginDate) VALUES(new.Id, new.Num, new.Name, new.ClassNum, new.ClassLetter, new.Birthday, new.EGN, sysdate());
60END$
61DELIMITER ;
62
63
64DROP TRIGGER IF EXISTS H_StudentsUpdate;
65DELIMITER $
66CREATE TRIGGER H_StudentsUpdate
67AFTER UPDATE
68ON Students FOR EACH ROW
69BEGIN
70	UPDATE H_Students
71    SET EndDate = sysdate()
72    WHERE H_Students.Id = old.Id AND H_Students.EndDate is NULL;
73	INSERT INTO H_Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN, BeginDate) VALUES(new.Id, new.Name, new.Num, new.ClassNum, new.ClassLetter, new.Birthday, new.EGN, sysdate());
74END$
75DELIMITER ;
76
77
78DROP TRIGGER IF exists H_StudentsDelete;
79DELIMITER $
80CREATE TRIGGER H_StudentsDelete
81AFTER DELETE
82ON Students FOR EACH ROW
83BEGIN 
84	UPDATE H_Students
85    SET EndDate = sysdate()
86    WHERE UID = old.Id;
87END$
88DELIMITER ;
89
90/*INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Асидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );*/
91
92UPDATE Students
93SET NAME = 'Бсидор Иванов'
94WHERE NAME = 'Асидор Иванов';
95
96DELETE FROM Students
97WHERE Name = 'Бсидор Иванов';
98
99-- TRIGERS H_Subjects
100DROP TRIGGER IF exists H_SubjectsInsert;
101DELIMITER $
102CREATE TRIGGER H_SubjectsInsert
103AFTER INSERT
104ON Subjects FOR EACH ROW
105BEGIN
106	INSERT INTO H_Subjects(Id, Name, BeginDate) VALUES(new.Id, new.Name, sysdate());
107END$
108DELIMITER ;
109
110DROP TRIGGER IF EXISTS H_SubjectsUpdate;
111DELIMITER $
112CREATE TRIGGER H_SubjectsUpdate
113AFTER UPDATE
114ON Subjects FOR EACH ROW
115BEGIN
116	UPDATE H_Subjects
117    SET EndDate = sysdate()
118    WHERE H_Subjects.Id = old.Id AND H_Subjects.EndDate is NULL;
119	INSERT INTO H_Subjects(Id, Name, BeginDate) VALUES(new.Id, new.Name, sysdate());
120END$
121DELIMITER ;
122
123
124DROP TRIGGER IF exists H_SubjectsDelete;
125DELIMITER $
126CREATE TRIGGER H_SubjectsDelete
127AFTER DELETE
128ON Subjects FOR EACH ROW
129BEGIN 
130	UPDATE H_Subjects
131    SET EndDate = sysdate()
132    WHERE H_Subjects.Id = old.Id AND H_Subjects.EndDate is NULL;
133END$
134DELIMITER ;
135
136
137-- TRIGERS H_StudentMarks:
138DROP TRIGGER IF exists H_StudentMarksInsert;
139DELIMITER $
140CREATE TRIGGER H_StudentMarksInsert
141AFTER INSERT
142ON StudentMarks FOR EACH ROW
143BEGIN 
144	INSERT INTO H_StudentMarks(StudentId, SubjectId, ExamDate, Mark, BeginDate) VALUES(new.StudentId, new.SubjectId, new.ExamDate, new.Mark, sysdate());
145END$
146DELIMITER ;
147
148
149DROP TRIGGER IF EXISTS H_StudentMarksUpdate;
150DELIMITER $
151CREATE TRIGGER H_StudentMarksUpdate
152AFTER UPDATE
153ON StudentMarks FOR EACH ROW
154BEGIN
155	UPDATE H_Students
156    SET EndDate = sysdate()
157    WHERE H_StudentMarks.StudentId = old.StudentId AND H_StudentMarks.SubjectId = old.SubjectId AND old.Mark = H_StudentMarks.Mark  AND old.ExamDate = H_Student.ExamDate AND H_Students.EndDate is NULL;
158	INSERT INTO H_StudentMarks(StudentId, SubjectId, ExamDate, Mark, BeginDate) VALUES(new.StudentId, new.SubjectId, new.ExamDate, new.Mark, sysdate());
159END$
160DELIMITER ;
161
162
163DROP TRIGGER IF exists H_StudentMarksDelete;
164DELIMITER $
165CREATE TRIGGER H_StudentMarksDelete
166AFTER DELETE
167ON StudentMarks FOR EACH ROW
168BEGIN 
169	UPDATE H_StudentMarks
170    SET EndDate = sysdate()
171    WHERE H_StudentMarks.StudentId = old.StudentId AND H_StudentMarks.SubjectId = old.SubjectId AND old.Mark = H_StudentMarks.Mark  AND old.ExamDate = H_Student.ExamDate AND H_Students.EndDate is NULL;
172END$
173DELIMITER ;
174
175
176-- TRIGERS H_MarkWords
177
178DROP TRIGGER IF exists H_MarkWordsInsert;
179DELIMITER $
180CREATE TRIGGER H_MarkWordsInsert
181AFTER INSERT
182ON MarkWords FOR EACH ROW
183BEGIN 
184	INSERT INTO H_MarkWords(RangeStart, RangeEnd, MarkAsWord, BeginDate) VALUES(new.RangeStart, new.RangeEnd, new.MarkAsWord, sysdate());
185END$
186DELIMITER ;
187
188
189DROP TRIGGER IF EXISTS H_MarkWordsUpdate;
190DELIMITER $
191CREATE TRIGGER H_MarkWordsUpdate
192AFTER UPDATE
193ON MarkWords FOR EACH ROW
194BEGIN
195	UPDATE H_Students
196    SET EndDate = sysdate()
197    WHERE H_Mark.RangeEnd=old.RangeEnd AND H_Mark.RangeStart=old.RangeStart AND H_Mark.MarkAsWord =old.MarkAsWord AND H_Mark.EndDate is NULL;
198	INSERT INTO H_MarkWords(RangeStart, RangeEnd, MarkAsWord, BeginDate) VALUES(new.RangeStart, new.RangeEnd, new.MarkAsWord, sysdate());
199END$
200DELIMITER ;
201
202
203DROP TRIGGER IF exists H_MarkWordsDelete;
204DELIMITER $
205CREATE TRIGGER H_MarkWordsDelete
206AFTER DELETE
207ON MarkWords FOR EACH ROW
208BEGIN 
209	UPDATE H_Students
210    SET EndDate = sysdate()
211    WHERE H_Mark.RangeEnd=old.RangeEnd AND H_Mark.RangeStart=old.RangeStart AND H_Mark.MarkAsWord =old.MarkAsWord AND H_Mark.EndDate is NULL;
212END$
213DELIMITER ;