· 6 years ago · Jan 17, 2020, 07:38 PM
1USE master;
2GO
3
4--удаление соединений
5DECLARE @kill varchar(8000) = '';
6
7SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'
8FROM sys.dm_exec_connections AS c
9JOIN sys.dm_exec_sessions AS s
10 ON c.session_id = s.session_id
11WHERE c.session_id <> @@SPID
12ORDER BY c.connect_time ASC
13
14EXEC(@kill)
15GO
16
17--Пункт 1
18--Создать в базах данных пункта 1 задания 13
19--связанные таблицы.
20
21--создаем таблицу производителей машин
22USE Lab13_1
23GO
24
25IF OBJECT_ID(N'Manufacturer') IS NOT NULL
26DROP TABLE Manufacturer;
27GO
28
29CREATE TABLE Manufacturer
30(
31 ManufacturerId INT PRIMARY KEY NOT NULL,
32 ManufacturerName VARCHAR (30) NOT NULL,
33 Country VARCHAR (50) NOT NULL
34)
35
36
37--создаем таблицу машин
38USE Lab13_2
39GO
40
41IF OBJECT_ID(N'Car') IS NOT NULL
42DROP TABLE Car;
43GO
44
45CREATE TABLE Car
46(
47 CarId INT PRIMARY KEY NOT NULL,
48 CarName VARCHAR (30) NOT NULL,
49 Price MONEY NOT NULL,
50 ProductionYear INT NOT NULL,
51 ManufacturerId INT NOT NULL,
52)
53
54
55--Пункт 2
56--Создать необходимые элементы базы данных
57--(представления, триггеры), обеспечивающие работу
58--с данными связанных таблиц (выборку, вставку,
59--изменение, удаление)
60
61--Создание триггеров
62
63--создание триггера на удаление производителя
64USE Lab13_1
65GO
66
67IF OBJECT_ID('dbo.Manufacturer_DeleteTrigger', 'TR') IS NOT NULL
68 DROP TRIGGER dbo.Manufacturer_DeleteTrigger
69GO
70
71CREATE TRIGGER dbo.Manufacturer_DeleteTrigger
72 ON dbo.Manufacturer
73 AFTER DELETE
74 AS
75 IF EXISTS (SELECT 1 FROM Lab13_2.dbo.Car WHERE ManufacturerId IN (SELECT ManufacturerId FROM deleted))
76 BEGIN
77 THROW 51000, 'Cannot delete ManufacturerId in Manufacturer. This Manufacturer has reference in Car', 1;
78 ROLLBACK TRANSACTION
79 END
80GO
81
82--создание триггера на обновление производителя
83IF OBJECT_ID('dbo.Manufacturer_UpdateTrigger', 'TR') IS NOT NULL
84 DROP TRIGGER dbo.Manufacturer_UpdateTrigger
85GO
86
87CREATE TRIGGER dbo.Manufacturer_UpdateTrigger
88 ON dbo.Manufacturer
89 AFTER UPDATE
90 AS
91 IF (UPDATE(ManufacturerId))
92 BEGIN
93 IF EXISTS (SELECT 1 FROM Lab13_2.dbo.Car WHERE ManufacturerId IN (SELECT ManufacturerId FROM deleted WHERE ManufacturerId NOT IN (SELECT ManufacturerId FROM inserted)))
94 BEGIN
95 THROW 51000, 'Cannot update ManufacturerId in Manufacturer. This Manufacturer has reference in Car', 1;
96 ROLLBACK TRANSACTION
97 END
98 END
99GO
100
101
102--создание триггера обновления машин
103USE Lab13_2
104GO
105IF OBJECT_ID('Car_UpdateTrigger', 'TR') IS NOT NULL
106 DROP TRIGGER dbo.Car_UpdateTrigger
107GO
108
109CREATE TRIGGER Car_UpdateTrigger
110 ON dbo.Car
111 AFTER UPDATE
112 AS
113 IF (UPDATE(ManufacturerId))
114 BEGIN
115 IF EXISTS (SELECT 1 FROM inserted WHERE ManufacturerId NOT IN (SELECT ManufacturerId FROM Lab13_1.dbo.Manufacturer))
116 BEGIN
117 THROW 51000, 'Cannot update ManufacturerId in Car. New ManufacturerId not exists in table Manufacturer', 1;
118 ROLLBACK TRANSACTION
119 END
120 END
121GO
122
123--создание триггера вставки машин
124IF OBJECT_ID('Car_InsertTrigger', 'TR') IS NOT NULL
125 DROP TRIGGER dbo.Car_InsertTrigger
126GO
127
128CREATE TRIGGER Car_InsertTrigger
129 ON dbo.Car
130 AFTER INSERT
131 AS
132 IF (UPDATE(ManufacturerId))
133 BEGIN
134 IF EXISTS (SELECT 1 FROM inserted WHERE ManufacturerId NOT IN (SELECT ManufacturerId FROM Lab13_1.dbo.Manufacturer))
135 BEGIN
136 THROW 51000, 'Cannot insert ManufacturerId in Car. New ManufacturerId not exists in table Manufacturer', 1;
137 ROLLBACK TRANSACTION
138 END
139 END
140GO
141
142
143
144--Тесты
145USE Lab13_1
146GO
147
148INSERT INTO Manufacturer (ManufacturerId, ManufacturerName, Country)
149VALUES (1, 'Audi', 'Germany'),
150 (2, 'Hyundai', 'South Korea'),
151 (3, 'BMW', 'Germany');
152GO
153
154SELECT * FROM Manufacturer
155GO
156
157USE Lab13_2
158GO
159
160INSERT INTO Car (CarId, CarName, Price, ProductionYear, ManufacturerId)
161VALUES (1, 'Q8', 4990000, 2017, 1),
162 (2, 'Q7', 3995000, 2015, 1),
163 (3, 'Creta', 957000, 2014, 2);
164GO
165
166SELECT * FROM Car
167GO
168
169----выдаст ошибку, т.к. нету Manufacturer с ManufacturerId = 4
170--UPDATE dbo.Car
171--SET ManufacturerId = 4
172--WHERE CarName = 'Q8'
173--GO
174--работает коректно
175UPDATE dbo.Car
176SET ManufacturerId = 3
177WHERE CarName = 'Q8'
178GO
179
180SELECT * FROM dbo.Car
181GO
182
183----выдаст ошибку, т.к. нету Manufacturer с ManufacturerId = 12
184--INSERT INTO Car (CarName, Price, ProductionYear, ManufacturerId)
185--VALUES ('carName', 1010, 2020, 12);
186--GO
187--работает коректно
188INSERT INTO Car (CarId, CarName, Price, ProductionYear, ManufacturerId)
189VALUES (4, 'x6', 5420000, 2006, 3);
190GO
191
192SELECT * FROM dbo.Car
193GO
194
195
196USE Lab13_1
197GO
198
199----выдаст ошибку, т.к. в таблице Car есть строчки, где используется ManufacturerId = 2
200--DELETE FROM dbo.Manufacturer
201--WHERE ManufacturerId = 2
202INSERT INTO Manufacturer (ManufacturerId, ManufacturerName, Country)
203VALUES (4, 'TestName', 'TestCountry');
204GO
205
206SELECT * FROM Manufacturer
207GO
208
209--работает коректно
210DELETE FROM dbo.Manufacturer
211WHERE ManufacturerId = 4
212
213SELECT * FROM Manufacturer
214GO
215
216----выдаст ошибку, т.к. в таблице Car есть строчки, где используется ManufacturerId = 1
217--UPDATE dbo.Manufacturer
218--SET ManufacturerId = 12
219--WHERE ManufacturerId = 1
220
221
222--Создание представления машины и производители
223USE Lab13_2
224GO
225
226IF OBJECT_ID (N'dbo.CarManufacturersLab15') IS NOT NULL
227DROP VIEW dbo.CarManufacturersLab15;
228GO
229
230CREATE VIEW dbo.CarManufacturersLab15
231AS
232SELECT c.CarId, c.CarName, c.Price, c.ProductionYear, c.ManufacturerId, m.ManufacturerName, m.Country
233FROM dbo.Car c,
234Lab13_1.dbo.Manufacturer m
235WHERE c.ManufacturerId = m.ManufacturerId
236GO
237
238SELECT * FROM dbo.CarManufacturersLab15
239GO
240
241--Триггер вставки в представление
242IF OBJECT_ID('dbo.CarManufacturersLab15_InsertTrigger', 'TR') IS NOT NULL
243 DROP TRIGGER dbo.CarManufacturersLab15_InsertTrigger
244GO
245
246CREATE TRIGGER dbo.CarManufacturersLab15_InsertTrigger
247 ON dbo.CarManufacturersLab15
248 INSTEAD OF INSERT
249 AS
250 BEGIN
251 INSERT INTO Lab13_1.dbo.Manufacturer(ManufacturerId, ManufacturerName, Country)
252 SELECT i.ManufacturerId, i.ManufacturerName, i.Country
253 FROM inserted i
254 WHERE i.ManufacturerId NOT IN (SELECT ManufacturerId FROM Lab13_1.dbo.Manufacturer)
255
256 INSERT INTO dbo.Car (CarId, CarName, Price, ProductionYear, ManufacturerId)
257 SELECT i.CarId, i.CarName, i.Price, i.ProductionYear, i.ManufacturerId
258 FROM inserted i
259 END
260GO
261
262--Триггер удаления из представления
263IF OBJECT_ID('dbo.CarManufacturersLab15_DeleteTrigger', 'TR') IS NOT NULL
264 DROP TRIGGER dbo.CarManufacturersLab15_DeleteTrigger
265GO
266
267CREATE TRIGGER dbo.CarManufacturersLab15_DeleteTrigger
268 ON dbo.CarManufacturersLab15
269 INSTEAD OF DELETE
270 AS
271 BEGIN
272 DELETE FROM dbo.Car WHERE CarId IN (SELECT CarId FROM deleted)
273 END
274GO
275
276--Триггер обновления представления
277IF OBJECT_ID('dbo.CarManufacturersLab15_UpdateTrigger', 'TR') IS NOT NULL
278 DROP TRIGGER dbo.CarManufacturersLab15_UpdateTrigger
279GO
280
281CREATE TRIGGER dbo.CarManufacturersLab15_UpdateTrigger
282 ON dbo.CarManufacturersLab15
283 INSTEAD OF UPDATE
284 AS
285 BEGIN
286 DELETE FROM dbo.Car WHERE CarId IN (SELECT CarId FROM deleted)
287 INSERT INTO dbo.Car (CarId, CarName, Price, ProductionYear, ManufacturerId)
288 SELECT i.CarId, i.CarName, i.Price, i.ProductionYear, i.ManufacturerId
289 FROM inserted i
290 END
291GO
292
293--Тесты
294INSERT INTO dbo.CarManufacturersLab15(CarId, CarName, Price, ProductionYear, ManufacturerId, ManufacturerName, Country)
295VALUES (10, 'insert car name', 1010, 2020, 2, 'Hyundai', 'South Korea')
296GO
297
298SELECT * FROM dbo.CarManufacturersLab15
299GO
300
301
302DELETE FROM dbo.CarManufacturersLab15
303WHERE CarName = 'insert car name'
304GO
305
306SELECT * FROM dbo.CarManufacturersLab15
307GO
308
309UPDATE dbo.CarManufacturersLab15
310SET ManufacturerId = 1
311WHERE CarName = 'Q8'
312GO
313
314SELECT * FROM dbo.CarManufacturersLab15
315GO
316
317INSERT INTO dbo.CarManufacturersLab15(CarId, CarName, Price, ProductionYear, ManufacturerId, ManufacturerName, Country)
318VALUES (11, 'insert car name', 1010, 2020, 20, 'new Manufacturer', 'new Country')
319GO
320
321SELECT * FROM dbo.CarManufacturersLab15
322GO
323
324SELECT * FROM dbo.Car
325GO
326
327SELECT * FROM Lab13_1.dbo.Manufacturer
328GO