· 6 years ago · Nov 18, 2019, 05:22 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 (2, 'Dead Souls', 1841),
56 (2, 'Overcoat', 1842),
57 (1, '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 INSTEAD OF 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 (NameBook, YearOfPublication) VALUES ('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 /*IF EXISTS (SELECT * FROM inserted WHERE NameBook NOT IN (SELECT NameBook FROM LibraryBooks))
159 BEGIN
160 RAISERROR('ERROR!!!', 16, 1) ROLLBACK TRANSACTION
161 END*/
162 BEGIN
163 INSERT INTO LibraryBooks (NameBook)
164 SELECT inserted.NameBook
165 FROM inserted
166 INSERT INTO BookAuthors (FirstName, LastName)
167 SELECT inserted.AuthorFirstName, inserted.AuthorLastName
168 FROM inserted
169 END
170GO
171
172INSERT INTO Books (NameBook, AuthorFirstName, AuthorLastName)
173 VALUES ('tesr', 'T', 'tt')
174SELECT * FROM Books
175GO
176
177SELECT * FROM LibraryBooks
178GO