· 6 years ago · Nov 24, 2019, 08:18 AM
1USE master;
2GO
3IF DB_ID (N'Lab9') IS NOT NULL
4DROP DATABASE Lab9;
5GO
6CREATE DATABASE Lab9
7GO
8--------------------------------
9USE Lab9;
10GO
11CREATE TABLE University(
12 UniversityID INT PRIMARY KEY,
13 FullName VARCHAR(100) NOT NULL,
14 City VARCHAR(50) NOT NULL,
15 Phone VARCHAR (20),
16 Email VARCHAR(50),
17 )
18GO
19CREATE TABLE Student(
20 StudentID INT PRIMARY KEY IDENTITY(1,1),
21 Phone VARCHAR (20) NOT NULL,
22 FullName VARCHAR(100) NOT NULL,
23 StudyGroup VARCHAR(15) NOT NULL,
24 CityFrom VARCHAR(50) NOT NULL,
25 UniversityID INT,
26 CONSTRAINT FK_Student_Competition FOREIGN KEY (UniversityID) REFERENCES University (UniversityID))
27GO
28INSERT University(UniversityID, FullName, City, Phone, Email)
29 VALUES (1, 'BMSTU', 'Moscow', '+7 499 263-63-91', 'bauman@bmstu.ru');
30INSERT University(UniversityID, FullName, City, Phone, Email)
31 VALUES (2, 'HSE', 'Moscow', '+7 495 771-32-32', 'hse@hse.ru');
32INSERT University(UniversityID, FullName, City, Phone, Email)
33 VALUES (3, 'MSU', 'Moscow', '+7 495 939-10-00', 'cmc@cs.msu.su');
34INSERT University(UniversityID, FullName, City, Phone, Email)
35 VALUES (4, 'MIPT', 'Dolgoprudny', '+7 495 408 45 54', 'info@mipt.ru');
36
37INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
38 VALUES ('+7 985 130 54 36','Kochanova Alena', 'IU9-51', 'Nizhny Novgorod', 1);
39INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
40 VALUES ('+7 910 127 11 81','Valitov Eldar', 'BPMI172-2017', 'Nizhny Novgorod', 2);
41INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
42 VALUES ('+7 930 125 12 34', 'Filonenko Ivan', 'IU9-51', 'Moscow', 1);
43INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
44 VALUES ('+7 251 231 23 12', 'Smith Sam', 'BPMI172-2017', 'Voronezh', 2);
45
46SELECT *FROM University;
47SELECT *FROM Student;
48GO
49
50CREATE VIEW StudentUniversityView AS
51 SELECT
52 Student.Phone AS StudentPhone,
53 Student.FullName AS StudentName,
54 Student.StudyGroup AS GroupStudent,
55 University.FullName AS UniversityName,
56 Student.CityFrom AS StudentCity
57 FROM Student INNER JOIN University ON Student.UniversityID = University.UniversityID
58GO
59SELECT *FROM StudentUniversityView
60GO
61
62--FIRSTPART
63CREATE TRIGGER trigger_insert_Student
64 ON Student
65 INSTEAD OF INSERT
66 AS
67 BEGIN
68 IF EXISTS (
69 SELECT * FROM inserted
70 WHERE Phone in (SELECT Phone FROM Student) or Phone = ''
71 )
72 BEGIN
73 RAISERROR('Please change the phone', 16, 1)
74 ROLLBACK TRANSACTION
75 RETURN
76 END
77 IF EXISTS (
78 SELECT * FROM inserted
79 WHERE FullName = ''
80 )
81 BEGIN
82 RAISERROR('Please get student fullname', 16, 1)
83 ROLLBACK TRANSACTION
84 RETURN
85 END
86 INSERT INTO Student (Phone, FullName, StudyGroup, CityFrom, UniversityID)
87 SELECT Phone, FullName, StudyGroup, CityFrom, UniversityId
88 FROM inserted
89 PRINT 'Add Student'
90 END
91GO
92CREATE TRIGGER trigger_delete_Student
93 ON Student
94 AFTER DELETE
95 AS
96 BEGIN
97 PRINT 'Delete Student'
98 END
99GO
100CREATE TRIGGER trigger_update_Student
101 ON Student
102 AFTER UPDATE
103 AS
104 BEGIN
105 IF UPDATE(UniversityId)
106 BEGIN
107 RAISERROR ('ERROR ID', 16, 1)
108 ROLLBACK TRANSACTION
109 RETURN
110 END
111 IF UPDATE(Phone)
112 BEGIN
113 RAISERROR('You cant modify phone', 16, 1)
114 ROLLBACK TRANSACTION
115 RETURN
116 END
117 IF UPDATE(StudyGroup)
118 BEGIN
119 IF EXISTS(
120 SELECT *FROM inserted
121 WHERE StudyGroup = ''
122 )
123 BEGIN
124 RAISERROR('Get studygroup', 16, 1)
125 ROLLBACK TRANSACTION
126 RETURN
127 END
128 END
129 IF UPDATE(CityFrom)
130 BEGIN
131 RAISERROR('Student from city name cant be modify', 16, 1)
132 ROLLBACK TRANSACTION
133 RETURN
134 END
135 END
136GO
137
138--select * from Student
139--update Student set Phone = '+7' + Phone
140--select * from Student
141
142--TESTS
143-------------------------------------INSERT-OK-----------------------------
144INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
145 VALUES ('+7 930 121 75 12', 'Pinskay Nika', 'IU9-51', 'Moscow', 1);
146INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
147 VALUES ('+7 521 231 12 74', 'Lysenko Anastasia', 'IU9-53', 'Lugansk', 1);
148INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
149 VALUES ('+7 214 56 24 89', 'Erukhimova Natalia', 'B01-901', 'Nizhny Novgorod', 4);
150INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
151 VALUES ('+7 853 251 52 63', 'Dunaeva Anastasia', '105', 'Nizhny Novgorod', 3);
152INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
153 VALUES ('+7 232 121 14 55', 'Petrov Andrew', '105', 'Voronezh', 3);
154SELECT *FROM Student;
155GO
156-------------------------INSERT-ERROR--------------------------------------
157INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
158 VALUES ('+7 232 121 14 55', 'Petrov Cat', '505', 'Novosibirsk', 3);
159INSERT Student(Phone, FullName, StudyGroup, CityFrom, UniversityID)
160 VALUES ('+7 232 121 55 55', '', '905', 'Novosibirsk', 3);
161SELECT *FROM Student;
162GO
163--------------------------DELETE----------------
164DELETE FROM Student WHERE (CityFrom = 'Voronezh')
165SELECT *FROM Student;
166GO
167-------------------------------UPDATE-OK--------------------------------
168UPDATE Student SET FullName = 'Filon ok' WHERE (FullName = 'Filonenko Ivan')
169UPDATE Student SET UniversityID = 3 WHERE (FullName = 'Filon ok')
170UPDATE Student SET StudyGroup = '305' WHERE (FullName = 'Filon ok')
171UPDATE Student SET StudyGroup = 'IU9-51' WHERE (StudyGroup = 'IU9-53')
172SELECT *FROM Student;
173GO
174------------------------------UPDATE-ERROR----------------------------
175UPDATE Student SET Phone = NULL WHERE (FullName = 'Kochanova Alena')
176UPDATE Student SET StudyGroup = '' WHERE (FullName = 'Kochanova Alena')
177UPDATE Student SET UniversityID = NULL WHERE (FullName = 'Pinskay Nika')
178UPDATE Student SET CityFrom = 'Moscow' WHERE (CityFrom = 'Nizhny Novgorod')
179UPDATE Student SET Phone = '+7 985 130 54 36' WHERE (FullName = 'Pinskay Nika')
180SELECT *FROM Student;
181GO
182
183--select * from StudentUniversityView
184
185------------------------------------SECONDPART----------------------------------
186CREATE TRIGGER trigger_insert_view
187 ON StudentUniversityView
188 INSTEAD OF INSERT
189 AS
190 BEGIN
191 IF EXISTS (
192 SELECT * FROM inserted
193 WHERE UniversityName NOT IN (SELECT FullName FROM University)
194 )
195 BEGIN
196 RAISERROR('Nonexistent University!', 16, 1)
197 ROLLBACK TRANSACTION
198 RETURN
199 END
200 INSERT INTO Student (Phone, FullName, StudyGroup, CityFrom, UniversityID)
201 SELECT
202 inserted.StudentPhone,
203 inserted.StudentName,
204 inserted.GroupStudent,
205 inserted.StudentCity,
206 (SELECT UniversityID FROM University WHERE FullName = inserted.UniversityName)
207 FROM inserted
208 END
209GO
210
211CREATE TRIGGER trigger_update_view
212 ON StudentUniversityView
213 INSTEAD OF UPDATE
214 AS
215 BEGIN
216 IF UPDATE(UniversityName)
217 BEGIN
218 UPDATE University
219 SET FullName = ins.UniversityName
220 FROM
221 (SELECT *, row_number() over (order by UniversityId) as row_num from inserted) as ins
222 JOIN
223 (SELECT *, row_number() over (order by UniversityId) as row_num from deleted) as del
224 ON ins.row_num = del.row_num
225 WHERE University.FullName = del.UniversityName
226 END
227 /*IF UPDATE (StudentName) OR UPDATE (GroupStudent)
228 BEGIN
229 UPDATE Student
230 SET FullName = inserted.StudentName, StudyGroup = inserted.GroupStudent
231 FROM inserted
232 Join Student
233 ON Student.Phone = inserted.StudentPhone
234 END*/
235 END
236GO
237
238
239CREATE TRIGGER trigger_delete_view
240 ON StudentUniversityView
241 INSTEAD OF DELETE
242 AS
243 BEGIN
244 MERGE Student USING(SELECT StudentPhone FROM deleted) AS StudentUniversityView(StudPhone)
245 ON Student.Phone = StudentUniversityView.StudPhone
246 WHEN MATCHED THEN DELETE;
247 --delete from Student where Phone in (select StudentPhone from deleted)
248 END
249GO
250
251-- -----------------------------TESTS---------------------------------
252-------------------------INSERT----------------------------------------
253INSERT INTO StudentUniversityView
254 VALUES ('+7 963 124 78 98', 'Mankus Danus', 'B01-902', 'MIPT', 'Saratov')
255SELECT * FROM StudentUniversityView
256-------------------------INSERT-ERROR--------------------------------------
257INSERT INTO StudentUniversityView
258 VALUES ('+7 963 451 56 78', 'Petrov Ivan', 'A102', 'MAI', 'Samara')
259SELECT * FROM StudentUniversityView
260GO
261INSERT INTO StudentUniversityView
262 VALUES ('+7 926 123 54 78', '', 'A02-105', 'MIPT', 'Samara')
263SELECT * FROM StudentUniversityView
264GO
265INSERT StudentUniversityView
266 VALUES ('+7 985 130 54 36','Hello World', 'IU9-51', 'MVTU', 'Moscow');
267SELECT * FROM StudentUniversityView
268GO
269
270-----------------------------------------UPDATE------------------------------------------
271UPDATE StudentUniversityView SET UniversityName = 'MVTU' WHERE (UniversityName = 'BMSTU')
272SELECT * FROM StudentUniversityView
273GO
274UPDATE StudentUniversityView SET GroupStudent = 'IU-5' WHERE (GroupStudent = 'IU9-51')
275SELECT * FROM StudentUniversityView
276GO
277UPDATE StudentUniversityView SET StudentName = 'Filonenko ' + 'Ivan' WHERE (StudentName = 'Filon ok')
278SELECT * FROM StudentUniversityView
279GO
280--------------------------------------UPDATE-ERROR---------------------------------------
281UPDATE StudentUniversityView SET GroupStudent = '' WHERE (UniversityName = 'MIPT')
282SELECT * FROM StudentUniversityView
283GO
284UPDATE StudentUniversityView SET StudentCity = 'Moscow' WHERE (UniversityName = 'MIPT')
285SELECT * FROM StudentUniversityView
286GO
287 UPDATE StudentUniversityView SET StudentPhone = '+7 985 124 78 88' WHERE (StudentName = 'Valitov Eldar')
288SELECT * FROM StudentUniversityView
289GO
290
291--------------------------------------------DELETE-----------------------------------
292DELETE FROM StudentUniversityView WHERE (UniversityName = 'MIPT')
293SELECT * FROM StudentUniversityView
294GO
295
296DELETE FROM StudentUniversityView WHERE (StudentCity = 'Moscow')
297SELECT * FROM StudentUniversityView
298GO
299
300
301
302
303
304
305
306/*
307 SET FullName = inserted.UniversityName
308 FROM inserted
309 Join University
310 ON University.FullName = inserted.UniversityName
311 DECLARE @Name VARCHAR(100) = (SELECT UniversityName FROM inserted)
312 UPDATE Student
313 SET UniversityID = (SELECT UniversityID FROM University WHERE FullName = @Name)
314 FROM inserted
315 Join Student
316 ON Student.Phone = inserted.StudentPhone*/
317
318/*CREATE TRIGGER trigger_delete_view
319 ON StudentUniversityView
320 INSTEAD OF DELETE
321 AS
322 BEGIN
323 DELETE FROM Student FROM Student s JOIN deleted d on d.IdUniversity = s.UniversityID
324 WHERE s.UniversityID NOT IN (SELECT DISTINCT UniversityID FROM University)
325 DECLARE @ID INT = (SELECT IdUniversity FROM deleted)
326 DELETE Student WHERE UniversityID = @ID
327 DELETE University WHERE UniversityID = @ID
328 END
329GO*/
330--MERGE Student USING(SELECT StudentName FROM deleted) AS StudentUniversityView(StudName)
331 --ON Student.FullName = StudentUniversityView.StudName
332 --WHEN MATCHED THEN DELETE;
333
334/*
335CREATE TRIGGER trigger_update_view
336 ON StudentUniversityView
337 INSTEAD OF UPDATE
338 AS
339 BEGIN
340 IF UPDATE(StudentName) or UPDATE(StudentCity)
341 BEGIN
342 RAISERROR('Student name cant be modify', 16, 1)
343 ROLLBACK TRANSACTION
344 RETURN
345 END
346 IF UPDATE(IdUniversity) OR UPDATE(GroupStudent)
347 BEGIN
348 IF EXISTS(
349 SELECT *FROM inserted
350 WHERE IdUniversity = '' or GroupStudent = ''
351 )
352 BEGIN
353 RAISERROR('No university or studygroup', 16, 1)
354 ROLLBACK TRANSACTION
355 RETURN
356 END
357 END
358 UPDATE University
359 SET FullName = ins.UniversityName, City = ins.UniversityCity
360 FROM
361 (SELECT *, row_number() over (order by UniversityId) as row_num from inserted) as ins
362 JOIN
363 (SELECT *, row_number() over (order by UniversityId) as row_num from deleted) as del
364 ON ins.row_num = del.row_num
365 WHERE University.FullName = del.UniversityName AND University.City = del.UniversityCity
366 AND University.UniversityID = del.IdUniversity
367 UPDATE Student
368 SET StudyGroup = ins.GroupStudent
369 FROM
370 (SELECT *, row_number() over (order by UniversityId) as row_num from inserted) as ins
371 JOIN
372 (SELECT *, row_number() over (order by UniversityId) as row_num from deleted) as del
373 ON ins.row_num = del.row_num
374 WHERE Student.StudyGroup = del.GroupStudent
375 AND Student.UniversityID = del.IdUniversity
376 END
377GO
378*/
379
380
381/*DECLARE @Phone VARCHAR(20) = (SELECT StudentPhone FROM inserted)
382 IF UPDATE (StudentPhone)
383 BEGIN
384 IF (@Phone IN (SELECT Phone FROM Student)) OR @Phone = '' OR @Phone = NULL
385 BEGIN
386 RAISERROR('Please change the phone', 16, 1)
387 ROLLBACK TRANSACTION
388 RETURN
389 END
390 END
391 IF UPDATE (GroupStudent) OR UPDATE (UniversityName)
392 BEGIN
393 IF EXISTS(
394 SELECT *FROM inserted
395 WHERE GroupStudent = '' OR GroupStudent = NULL or UniversityName = '' OR UniversityName = NULL
396 )
397 BEGIN
398 RAISERROR('No university or studygroup', 16, 1)
399 ROLLBACK TRANSACTION
400 RETURN
401 END
402 DECLARE @Name VARCHAR(100) = (SELECT UniversityName FROM inserted)
403 IF (@Name NOT IN (SELECT FullName FROM University))
404 BEGIN
405 RAISERROR('Nonexistent University! Please add the university', 16, 1)
406 ROLLBACK TRANSACTION
407 RETURN
408 END
409 END
410 IF UPDATE (StudentCity)
411 BEGIN
412 RAISERROR('Student city cant be modify', 16, 1)
413 ROLLBACK TRANSACTION
414 RETURN
415 END*/