· 6 years ago · Nov 18, 2019, 11:04 PM
1use master
2GO
3/*
4IF DB_ID(N'DataBaseLab9') IS NOT NULL
5DROP DATABASE DataBaseLab9;
6GO
7
8CREATE DATABASE DataBaseLab9
9GO
10*/
11
12USE DataBaseLab9;
13GO
14
15
16IF OBJECT_ID (N'BookAuthors') IS NOT NULL
17BEGIN
18 IF OBJECT_ID (N'LibraryBooks') IS NOT NULL
19 DROP TABLE LibraryBooks;
20 DROP TABLE BookAuthors;
21END
22GO
23
24CREATE TABLE BookAuthors
25 (AuthorID int PRIMARY KEY IDENTITY(1,1),
26 FirstName varchar(50),
27 LastName varchar(50),
28 YearOfBirth int,
29 City varchar(50),
30 );
31GO
32
33
34IF OBJECT_ID (N'LibraryBooks') IS NOT NULL
35 DROP TABLE LibraryBooks;
36GO
37
38 CREATE TABLE LibraryBooks
39 (BookID int PRIMARY KEY IDENTITY(1,1),
40 NameBook varchar(100),
41 YearOfPublication int,
42 AuthorID int
43 CONSTRAINT FK_Autors_Books FOREIGN KEY (AuthorID) REFERENCES BookAuthors (AuthorID)
44 );
45 GO
46
47 INSERT BookAuthors (FirstName, LastName, YearOfBirth, City)
48 VALUES ('Nikolay', 'Gogol', 1809, 'Big Sorochintsy'),
49 ('Mikhail', 'Lermontov', 1814, 'Moscow'),
50 ('Alexander', 'Pushkin', 1799, 'Moscow'),
51 ('Taras', 'Shevchenko', 1814, 'Morintsy');
52
53 INSERT LibraryBooks (AuthorID, NameBook, YearOfPublication)
54 VALUES (1,'Dead Souls', 1841),
55 (1,'Dead Souls', 1841),
56 (1, 'Overcoat', 1842),
57 (2, 'Hero of our time', 1840),
58 (3,'Ruslan and Ludmila', 1820),
59 (4, 'Kobzar', 1860);
60
61
62 Select *from LibraryBooks;
63 GO
64
65 Select *from BookAuthors
66 GO
67
68USE DataBaseLab9;
69GO
70
71
72IF OBJECT_ID ('Books', 'V') IS NOT NULL
73 DROP VIEW Books
74GO
75
76CREATE VIEW Books AS
77 SELECT LibraryBooks.NameBook AS NameBook,
78 BookAuthors.FirstName AS AuthorFirstName,
79 BookAuthors.LastName AS AuthorLastName
80 FROM LibraryBooks INNER JOIN BookAuthors ON LibraryBooks.AuthorID = BookAuthors.AuthorID
81GO
82
83SELECT *FROM Books
84GO
85
86
87------------1-----------------
88USE DataBaseLab9;
89GO
90
91IF OBJECT_ID ('InsertTrigger', 'TR') IS NOT NULL
92 DROP TRIGGER InsertTrigger;
93GO
94
95CREATE TRIGGER InsertTrigger ON LibraryBooks
96 AFTER INSERT
97 AS
98 PRINT 'add books'
99GO
100
101
102IF OBJECT_ID ('DeleteTrigger', 'TR') IS NOT NULL
103 DROP TRIGGER DeleteTrigger;
104GO
105
106CREATE TRIGGER DeleteTrigger ON LibraryBooks AFTER DELETE
107 AS
108 BEGIN
109 DELETE LibraryBooks WHERE EXISTS (SELECT AuthorID FROM deleted
110 WHERE LibraryBooks.NameBook = deleted.NameBook);
111 END
112GO
113
114
115INSERT LibraryBooks (AuthorID, NameBook, YearOfPublication) VALUES (1, 'Evenings on a Farm Near Dikanka', 1831);
116Select *from LibraryBooks
117GO
118
119delete from LibraryBooks where (NameBook = 'Dead Souls')
120Select *from LibraryBooks
121GO
122
123
124IF OBJECT_ID ('UpdateTrigger', 'TR') IS NOT NULL
125 DROP TRIGGER UpdateTrigger;
126GO
127
128CREATE TRIGGER UpdateTrigger ON LibraryBooks AFTER UPDATE
129 AS
130 BEGIN
131 DECLARE @name varchar(50) = (SELECT NameBook from deleted);
132 DECLARE @name_ varchar(50) = (SELECT NameBook from inserted);
133 IF (UPDATE (NameBook))
134 BEGIN
135 RAISERROR('ERROR!!!', 16, 1) ROLLBACK TRANSACTION
136 END
137 END
138GO
139
140UPDATE LibraryBooks SET YearOfPublication = 1834 WHERE (AuthorID = 476)
141Select *from LibraryBooks
142GO
143
144/*UPDATE LibraryBooks SET NameBook = 'new Name' WHERE (AuthorID = 476)
145Select *from LibraryBooks
146GO*/
147
148
149
150-------------2-----------------
151IF OBJECT_ID ('InsertTriggerView', 'TR') IS NOT NULL
152 DROP TRIGGER InsertTriggerView;
153GO
154
155CREATE TRIGGER InsertTriggerView ON Books
156 INSTEAD OF INSERT
157 AS
158 --ERROR
159 /*IF EXISTS (SELECT * FROM inserted WHERE NameBook NOT IN (SELECT NameBook FROM LibraryBooks))
160 BEGIN
161 RAISERROR('ERROR!!!', 16, 1) ROLLBACK TRANSACTION
162 END*/
163 BEGIN
164 INSERT INTO LibraryBooks (NameBook)
165 SELECT inserted.NameBook
166 FROM inserted
167 INSERT INTO BookAuthors (FirstName, LastName)
168 SELECT inserted.AuthorFirstName, inserted.AuthorLastName
169 FROM inserted
170 END
171GO
172
173INSERT INTO Books (NameBook, AuthorFirstName, AuthorLastName)
174 VALUES ('tesr', 'T', 'tt')
175SELECT * FROM Books
176GO
177
178SELECT * FROM LibraryBooks
179SELECT * FROM BookAuthors
180GO
181
182
183IF OBJECT_ID ('DeleteTriggerView', 'TR') IS NOT NULL
184 DROP TRIGGER DeleteTriggerView;
185GO
186
187CREATE TRIGGER DeleteTriggerView ON Books INSTEAD OF DELETE
188 AS
189 BEGIN
190 DELETE LibraryBooks WHERE NameBook IN (SELECT NameBook FROM deleted)
191 END
192GO
193
194delete from Books where (NameBook = 'Dead Souls')
195SELECT *FROM Books
196GO
197
198SELECT * FROM LibraryBooks
199SELECT * FROM BookAuthors
200GO
201
202
203IF OBJECT_ID ('UpdateTriggerView', 'TR') IS NOT NULL
204 DROP TRIGGER UpdateTriggerView
205GO
206
207CREATE TRIGGER UpdateTriggerView ON Books
208INSTEAD OF UPDATE
209AS
210BEGIN
211 IF UPDATE (AuthorFirstName) OR UPDATE (AuthorLastName)
212 BEGIN
213 print 'update_'
214 UPDATE BookAuthors
215 SET FirstName = inserted.AuthorFirstName, LastName = inserted.AuthorLastName
216 FROM inserted
217 JOIN BookAuthors
218 ON BookAuthors.AuthorID = AuthorId
219 END
220 IF UPDATE (NameBook)
221 BEGIN
222 print 'update'
223 UPDATE LibraryBooks
224 SET NameBook = inserted.NameBook
225 FROM inserted
226 JOIN LibraryBooks
227 ON LibraryBooks.BookID = BookID
228 END
229END
230
231UPDATE Books
232SET NameBook = 'Viy' WHERE (AuthorLastName = 'Gogol')
233SELECT * FROM Books
234GO
235
236SELECT * FROM LibraryBooks
237SELECT * FROM BookAuthors
238GO