mPtnBMtE

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--удаление таблицы из БД1
25IF OBJECT_ID (N'dbo.ManufacturersCar') IS NOT NULL
26DROP TABLE dbo.ManufacturersCar;
27GO
28--Создаем таблицу в БД1 c машинами 
29GO
30CREATE TABLE dbo.ManufacturersCar
31(
32	 CarID INT PRIMARY KEY NOT NULL,
33	 CarName VARCHAR (50) NOT NULL
34);
35GO
36
37use Lab13_2
38GO
39--удаление таблицы из БД2
40IF OBJECT_ID (N'dbo.ManufacturersCar') IS NOT NULL
41DROP TABLE dbo.ManufacturersCar;
42GO
43--Создаем таблицу в БД2 c производителями машин
44GO
45CREATE TABLE dbo.ManufacturersCar
46(
47	 CarID INT PRIMARY KEY NOT NULL,
48	 ManufacturerName VARCHAR (50) NOT NULL
49);
50GO
51
52--Пункт 2
53--Создать необходимые элементы базы данных
54--(представления, триггеры), обеспечивающие работу
55--с данными вертикально фрагментированных таблиц
56--(выборку, вставку, изменение, удаление).
57
58--Создание представления и тригеров в БД1
59
60--Создаем представление в БД1
61USE Lab13_1
62GO
63
64IF OBJECT_ID (N'dbo.CarManufacturersLab14') IS NOT NULL
65DROP VIEW dbo.CarManufacturersLab14;
66GO
67
68CREATE VIEW dbo.CarManufacturersLab14
69AS
70	SELECT c.CarID, c.CarName, m.ManufacturerName
71	FROM dbo.ManufacturersCar c,
72	Lab13_2.dbo.ManufacturersCar m
73	WHERE c.CarID = m.CarID
74GO
75
76--Тригер на вставку в БД1
77IF OBJECT_ID('CarManufacturersLab14InsertTrigger', 'TR') IS NOT NULL
78	DROP TRIGGER CarManufacturersLab14InsertTrigger
79GO
80
81CREATE TRIGGER CarManufacturersLab14InsertTrigger
82	ON dbo.CarManufacturersLab14
83	INSTEAD OF INSERT
84	AS
85	BEGIN
86		INSERT INTO dbo.ManufacturersCar (CarID, CarName)
87		SELECT i.CarID, i.CarName
88		FROM inserted i
89
90		INSERT INTO Lab13_2.dbo.ManufacturersCar(CarID, ManufacturerName)
91		SELECT i.CarID, i.ManufacturerName
92		FROM inserted i
93	END
94GO
95
96--Тригер на удаление в БД1
97IF OBJECT_ID('CarManufacturersLab14DeleteTrigger', 'TR') IS NOT NULL
98	DROP TRIGGER CarManufacturersLab14DeleteTrigger
99GO
100
101CREATE TRIGGER CarManufacturersLab14DeleteTrigger
102	ON dbo.CarManufacturersLab14
103	INSTEAD OF DELETE
104	AS
105	BEGIN
106		DELETE FROM dbo.ManufacturersCar WHERE CarID in (SELECT CarID FROM deleted)
107		DELETE FROM Lab13_2.dbo.ManufacturersCar WHERE CarID in (SELECT CarID FROM deleted)
108	END
109GO
110
111--Тригер на обновление в БД1
112IF OBJECT_ID('CarManufacturersLab14UpdateTrigger', 'TR') IS NOT NULL
113	DROP TRIGGER CarManufacturersLab14UpdateTrigger
114GO
115
116CREATE TRIGGER CarManufacturersLab14UpdateTrigger
117	ON dbo.CarManufacturersLab14
118	INSTEAD OF UPDATE
119	AS
120	BEGIN
121		IF (UPDATE(CarID))
122			BEGIN
123			
124				RAISERROR (N'Cannot update CarId', 18, 20);
125				ROLLBACK 
126			
127			END
128
129		IF (UPDATE(CarName))
130			BEGIN
131			UPDATE dbo.ManufacturersCar
132			SET CarName = (SELECT CarName FROM inserted i WHERE (i.CarID = dbo.ManufacturersCar.CarID))
133			WHERE (exists (SELECT * FROM inserted i WHERE (i.CarID = dbo.ManufacturersCar.CarID)))
134			END
135
136
137		IF (UPDATE(ManufacturerName))
138			BEGIN
139			UPDATE Lab13_2.dbo.ManufacturersCar
140			SET ManufacturerName = (SELECT ManufacturerName FROM inserted i WHERE (i.CarID = Lab13_2.dbo.ManufacturersCar.CarID))
141			WHERE (exists (SELECT * FROM inserted i WHERE (i.CarID = Lab13_2.dbo.ManufacturersCar.CarID)))
142			END
143	END
144GO
145
146select * from CarManufacturersLab14
147update CarManufacturersLab14 
148set CarID = CarID + 1, CarName = 'x_' + Carname
149select * from CarManufacturersLab14
150
151--Создаем представление в БД2
152USE Lab13_2
153GO
154
155IF OBJECT_ID (N'dbo.CarManufacturersLab14') IS NOT NULL
156DROP VIEW dbo.CarManufacturersLab14;
157GO
158
159CREATE VIEW dbo.CarManufacturersLab14
160AS
161	SELECT c.CarID, c.CarName, m.ManufacturerName
162	FROM Lab13_1.dbo.ManufacturersCar c,
163	dbo.ManufacturersCar m
164	WHERE c.CarID = m.CarID
165GO
166
167--Тригер на вставку в БД2
168IF OBJECT_ID('CarManufacturersLab14InsertTrigger', 'TR') IS NOT NULL
169	DROP TRIGGER CarManufacturersLab14InsertTrigger
170GO
171
172CREATE TRIGGER CarManufacturersLab14InsertTrigger
173	ON dbo.CarManufacturersLab14
174	INSTEAD OF INSERT
175	AS
176	BEGIN
177		INSERT INTO Lab13_1.dbo.ManufacturersCar (CarID, CarName)
178		SELECT i.CarID, i.CarName
179		FROM inserted i
180
181		INSERT INTO dbo.ManufacturersCar(CarID, ManufacturerName)
182		SELECT i.CarID, i.ManufacturerName
183		FROM inserted i
184	END
185GO
186
187--Тригер на удаление в БД2
188IF OBJECT_ID('CarManufacturersLab14DeleteTrigger', 'TR') IS NOT NULL
189	DROP TRIGGER CarManufacturersLab14DeleteTrigger
190GO
191
192CREATE TRIGGER CarManufacturersLab14DeleteTrigger
193	ON dbo.CarManufacturersLab14
194	INSTEAD OF DELETE
195	AS
196	BEGIN
197		DELETE FROM Lab13_1.dbo.ManufacturersCar WHERE CarID in (SELECT CarID FROM deleted)
198		DELETE FROM dbo.ManufacturersCar WHERE CarID in (SELECT CarID FROM deleted)
199	END
200GO
201
202--Тригер на обновление в БД2
203IF OBJECT_ID('CarManufacturersLab14UpdateTrigger', 'TR') IS NOT NULL
204	DROP TRIGGER CarManufacturersLab14UpdateTrigger
205GO
206
207CREATE TRIGGER CarManufacturersLab14UpdateTrigger
208	ON dbo.CarManufacturersLab14
209	INSTEAD OF UPDATE
210	AS
211	BEGIN
212		IF (UPDATE(CarID))
213			BEGIN
214				RAISERROR (N'Cannot update CarId', 18, 20);
215				ROLLBACK 
216			END
217
218		IF (UPDATE(CarName))
219			BEGIN
220			UPDATE Lab13_1.dbo.ManufacturersCar
221			SET CarName = (SELECT CarName FROM inserted i WHERE (i.CarID = Lab13_1.dbo.ManufacturersCar.CarID))
222			WHERE (exists (SELECT * FROM inserted i WHERE (i.CarID = Lab13_1.dbo.ManufacturersCar.CarID)))
223			END
224
225		IF (UPDATE(ManufacturerName))
226			BEGIN
227			UPDATE dbo.ManufacturersCar
228			SET ManufacturerName = (SELECT ManufacturerName FROM inserted i WHERE (i.CarID = dbo.ManufacturersCar.CarID))
229			WHERE (exists (SELECT * FROM inserted i WHERE (i.CarID = dbo.ManufacturersCar.CarID)))
230			END
231	END
232GO
233
234
235
236
237--Тесты
238use Lab13_1
239GO
240INSERT INTO dbo.CarManufacturersLab14(CarID, CarName, ManufacturerName)
241VALUES  (1, 'Audi Q8', 'Audi'),
242		(2, 'Audi Q7', 'Audi')
243GO
244
245use Lab13_2
246GO
247INSERT INTO dbo.CarManufacturersLab14(CarID, CarName, ManufacturerName)
248VALUES  (1001, 'Hyundai Creta', 'Hyundai'),
249		(1020, 'BMW x6', 'BMW')
250GO
251
252use Lab13_1
253GO
254UPDATE dbo.CarManufacturersLab14
255SET CarName = CarName + ' Remastered'
256WHERE CarName = 'Hyundai Creta'
257GO
258
259SELECT * FROM dbo.CarManufacturersLab14
260GO
261
262
263use Lab13_2
264GO
265
266DELETE FROM dbo.CarManufacturersLab14
267WHERE CarName = 'Audi Q8'
268GO
269
270Use Lab13_1
271GO
272
273SELECT * FROM dbo.ManufacturersCar
274GO
275
276use Lab13_2
277GO
278
279SELECT * FROM dbo.ManufacturersCar
280GO
281
282use Lab13_1
283GO
284
285SELECT * FROM dbo.CarManufacturersLab14
286GO
287
288use Lab13_2
289GO
290
291SELECT * FROM dbo.CarManufacturersLab14
292GO