YXtv4KLN

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