· 6 years ago · Dec 29, 2019, 11:08 PM
1use master;
2go
3if DB_ID (N'lab_9') is not null
4drop database lab_9;
5go
6create database lab_9
7on (
8NAME = lab_9dat,
9FILENAME = 'C:\DB\lab_9\lab_9dat.mdf',
10SIZE = 10,
11MAXSIZE = UNLIMITED,
12FILEGROWTH = 5
13)
14log on (
15NAME = lab_9log,
16FILENAME = 'C:\DB\lab_9\lab_9log.ldf',
17SIZE = 5,
18MAXSIZE = 20,
19FILEGROWTH = 5
20);
21go
22
23--======================================================================================--
24use lab_9;
25go
26
27if OBJECT_ID(N'Store',N'U') is NOT NULL
28 DROP TABLE Store;
29go
30
31
32CREATE TABLE Store(
33 Store_Id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
34 Name char(50) NOT NULL,
35 Adress char(50) NULL,
36 PhoneNumber numeric(11) DEFAULT('12345')
37);
38go
39
40SET IDENTITY_INSERT Store ON;
41go
42
43INSERT INTO Store(Store_Id,Name)
44VALUES (1,N'Книжный лабиринт'),
45 (2,N'Книжный на Лубянке'),
46 (3,N'Мир книг'),
47 (4,N'Главный книжный')
48go
49
50INSERT INTO Store(Store_Id,Name)
51VALUES (0,N'Склад')
52
53
54if OBJECT_ID(N'Book',N'U') is NOT NULL
55 DROP TABLE Book;
56go
57
58if OBJECT_ID(N'FK_Store',N'F') IS NOT NULL
59 ALTER TABLE Book DROP CONSTRAINT FK_Store
60go
61
62CREATE TABLE Book (
63 book_id int IDENTITY(1,1) PRIMARY KEY,
64 author char(50) NOT NULL,
65 name char(50) NOT NULL,
66 genre char(20) NOT NULL,
67 External_Id int NULL,
68 CONSTRAINT FK_Store FOREIGN KEY (External_Id) REFERENCES Store (Store_Id)
69 );
70go
71
72INSERT INTO Book(author,name,genre, External_Id)
73VALUES (N'Александр Пушкин',N'Евгений Онегин', N'Роман',1),
74 (N'Жюль Верн',N'20 000 льё под водой',N'Научная фантастика',2),
75 (N'Агата Кристи',N'Убийство Роджера Экройда',N'Детектив',3),
76 (N'Стивен Кинг',N'1408', N'Мистика',4),
77 (N'Корней Чуковской',N'Добрый доктор', N'Сказка',4),
78 (N'Гастон Леру',N'Призрак оперы', N'Роман',3)
79go
80
81--SELECT * FROM Book
82--======================================================================================--
83
84IF OBJECT_ID(N'Delete_Store',N'TR') IS NOT NULL
85 DROP TRIGGER Delete_Store
86go
87
88CREATE TRIGGER Delete_Store
89 ON Store
90 INSTEAD OF DELETE
91AS
92 BEGIN
93 IF EXISTS (SELECT TOP 1 Store_ID FROM deleted WHERE Store_ID = 0)
94 BEGIN;
95 EXEC sp_addmessage 50001, 15,N'Удаление магазина невозможно',@lang = 'us_english', @replace='REPLACE';
96 RAISERROR(50001,15,-1)
97 END;
98 ELSE
99 BEGIN;
100 UPDATE Book SET External_Id = 0 WHERE External_Id IN (SELECT Store_Id FROM deleted)
101 DELETE FROM Book WHERE book_id IN (SELECT book_id FROM deleted)
102 IF (SELECT DISTINCT COUNT(*) FROM deleted) > 1
103 PRINT 'Магазины удалены из таблицы и все книги перемещены в склад!'
104 ELSE
105 PRINT 'Магазин удален из таблицы и все книги перемещены в склад!'
106 END;
107 END
108go
109
110--DELETE FROM Store WHERE Store_ID in (1,2,3)
111--SELECT * FROM Book
112--SELECT * FROM Store
113--go
114--======================================================================================--
115
116IF OBJECT_ID(N'Update_Store',N'TR') IS NOT NULL
117 DROP TRIGGER Update_Store
118go
119
120CREATE TRIGGER Update_Store
121 ON Store
122 AFTER UPDATE
123AS
124 BEGIN
125 IF ((UPDATE(Adress) AND EXISTS (SELECT TOP 1 Adress FROM deleted WHERE Adress is not NULL))
126 OR (UPDATE(Name) AND EXISTS (SELECT TOP 1 Name FROM deleted WHERE Name is not NULL)))
127 BEGIN;
128 EXEC sp_addmessage 50002, 15,N'Изменение адреса магазина невозможно!',@lang='us_english',@replace='REPLACE';
129 RAISERROR(50002,15,-1)
130 END;
131 ELSE
132 BEGIN;
133 DECLARE @temp_table TABLE (
134 store_id int PRIMARY KEY,
135 add_name char(50), add_adress char(70),add_phonenumber numeric(11),
136 delete_name char(50), delete_adress char(70),delete_phonenumber numeric(11)
137 );
138
139 INSERT INTO @temp_table(store_id,add_name,add_adress,add_phonenumber,
140 delete_name,delete_adress,delete_phonenumber)
141 SELECT A.store_id, A.name,A.adress,A.phonenumber,
142 B.name,B.adress,B.phonenumber
143 FROM inserted A
144 INNER JOIN deleted B ON A.store_id = B.store_id
145
146 IF UPDATE(adress)
147 PRINT N'Добавлен адрес'
148 IF UPDATE(name)
149 PRINT N'Был переназван магазин'
150 IF UPDATE(phonenumber) AND EXISTS (SELECT TOP 1 delete_phonenumber FROM @temp_table WHERE delete_phonenumber IS NULL)
151 PRINT N'Был добавлен телефон'
152 IF UPDATE(phonenumber)
153 PRINT N'Был изменен телефон'
154
155 DECLARE @number int;
156 SET @number = (SELECT DISTINCT COUNT(*) FROM @temp_table);
157 IF @number > 1
158 PRINT N'у ' + CAST(@number AS VARCHAR(1)) + ' магазина'
159 ELSE
160 PRINT N'у 1 магазина'
161 END;
162 END
163go
164
165--UPDATE Store SET Adress = 'Update: Store adress' WHERE Store_Id > 1
166--SELECT * FROM Store
167--go
168
169--UPDATE Store SET PhoneNumber = 88005553535 WHERE Store_id=1
170--SELECT * FROM Store
171--go
172
173
174IF OBJECT_ID(N'Add_Store',N'TR') IS NOT NULL
175 DROP TRIGGER Add_Store
176go
177
178CREATE TRIGGER Add_Store
179 ON Store
180 AFTER INSERT
181AS
182 BEGIN
183 IF (SELECT DISTINCT COUNT(*) FROM inserted) > 1
184 PRINT 'Добавлены новые магазины в таблицу'
185 ELSE
186 PRINT 'Добавлен новый магазин в таблицу'
187 END
188go
189
190/*INSERT INTO Store(Store_Id,Name)
191VALUES (5,N'Комус'),
192 (6,N'Книжный номер 1')
193SELECT * FROM Store
194go*/
195
196--=====================================================================================================--
197
198SET IDENTITY_INSERT Store OFF;
199go
200
201if OBJECT_ID(N'StoreView',N'V') is NOT NULL
202 DROP VIEW StoreView;
203go
204
205CREATE VIEW StoreView AS
206 SELECT b.book_id as book_id b.name as name,b.author as author, b.genre as genre,s.name as store_name
207 FROM Store as s INNER JOIN Book as b ON s.Store_Id = b.External_Id
208go
209
210SELECT * FROM StoreView
211go
212
213IF OBJECT_ID(N'Add_View_Store',N'TR') IS NOT NULL
214 DROP TRIGGER Add_View_Store
215go
216
217CREATE TRIGGER Add_View_Store
218 ON StoreView
219 INSTEAD OF INSERT
220AS
221 BEGIN
222 DECLARE @temp_table TABLE (
223 name char(50), author char(50),genre char(20),
224 store_id int, store_name char(70)
225 );
226
227
228 INSERT INTO @temp_table(name,author,genre,store_id,store_name)
229 SELECT A.name,A.author,A.genre,B.Store_Id,A.store_name
230 FROM inserted A
231 LEFT JOIN Store B
232 ON A.store_name = B.name
233
234
235 INSERT INTO Store(Name)
236 SELECT store_name
237 FROM @temp_table
238 WHERE store_id IS NULL
239
240 UPDATE @temp_table SET store_id = (SELECT Store_Id FROM Store WHERE name = store_name)
241
242 INSERT INTO Book(author,name,genre,External_Id)
243 SELECT author,name,genre,store_id
244 FROM @temp_table
245
246 PRINT 'Добавлены новые книги'
247 END
248go
249
250/*INSERT INTO StoreView(name,author,genre,store_name)
251VALUES (N'451 градус по Фаренгейту',N'Рэй Брэдбери',N'Роман',N'Старый книжный'),
252 (N'Мастер и Маргарита',N'Михаил Булгаков',N'Мистика',N'Новый книжный'),
253 (N'Маленький принц',N'Антуан де Сент-Экзюпери',N'Сказка',N'Лучший книжный')
254SELECT * FROM Store
255SELECT * FROM Book
256go*/
257
258
259IF OBJECT_ID(N'Delete_View_Store',N'TR') IS NOT NULL
260 DROP TRIGGER Delete_View_Store
261go
262
263CREATE TRIGGER Delete_View_Store
264 ON StoreView
265 INSTEAD OF DELETE
266AS
267 BEGIN
268 DELETE FROM Book WHERE name IN (SELECT name FROM deleted)
269 PRINT 'Удалены книги'
270 END
271go
272
273--DELETE FROM StoreView WHERE (name=N'1408' AND author=N'Стивен Кинг') OR (name=N'Евгений Онегин')
274--SELECT * FROM StoreView
275--go
276
277IF OBJECT_ID(N'Update_View_library',N'TR') IS NOT NULL
278 DROP TRIGGER Update_View_library
279go
280
281/*CREATE TRIGGER Update_View_library
282 ON StoreView
283 INSTEAD OF UPDATE
284AS
285 BEGIN
286 DECLARE @temp_table TABLE (
287 name nchar(50), author nchar(50),genre nchar(20),library_name nchar(70),
288 delete_name nchar(50), delete_author nchar(50),delete_genre nchar(20),delete_library_name nchar(70),
289 library_id int
290 );
291
292 IF UPDATE(name) OR UPDATE(author) OR UPDATE(genre)
293 BEGIN
294 EXEC sp_addmessage 50004, 15,N'Запрещено изменение данных о книге',@lang='us_english',@replace='REPLACE';
295 RAISERROR(50004,15,-1)
296 END
297
298 INSERT INTO @temp_table(name,author,genre,library_name,
299 delete_name,delete_author,delete_genre,delete_library_name,
300 library_id)
301 SELECT A.name, A.author,A.genre,A.store_name,
302 B.name,B.author,B.genre,B.store_name,
303 C.Store_Id
304 FROM inserted A
305 INNER JOIN deleted B ON A.name = B.name
306 LEFT JOIN Store C ON A.store_name = C.name
307
308 SELECT * FROM @temp_table
309
310
311 IF EXISTS (SELECT * FROM @temp_table WHERE library_id IS NULL)
312 BEGIN
313 EXEC sp_addmessage 50003, 15,N'Перемещение книги в несуществующую библиотеку невозможно!',@lang='us_english',@replace='REPLACE';
314 RAISERROR(50003,15,-1)
315 END
316
317 IF UPDATE(store_name)
318 BEGIN
319 UPDATE Book SET External_Id = (SELECT TOP 1 library_id FROM @temp_table) WHERE name IN (SELECT delete_name FROM @temp_table)
320 PRINT N'Книга(и) была(и) перемещена(ы) в другой магазин'
321 END
322
323 END
324go
325
326UPDATE StoreView SET store_name=N'LastUpdate store' WHERE (name=N'1408' AND author=N'Стивен Кинг') OR (name=N'Евгений Онегин')
327SELECT * FROM Store
328SELECT * FROM Book
329go*/
330
331CREATE TRIGGER Update_View_library
332 ON StoreView
333 INSTEAD OF UPDATE
334AS
335 BEGIN
336 IF UPDATE(store_name)
337 BEGIN
338 SET store_name = ins.store_name FROM (SELECT * FROM inserted) AS ins
339 JOIN (SELECT * FROM deleted) AS del
340 ON ins.book_id = del.book_id WHERE del.store_name = StoreView.store_name
341 END
342 END
343GO
344
345UPDATE StoreView SET store_name=N'LastUpdate store'
346SELECT * FROM StoreView
347go