· 9 years ago · Jan 19, 2017, 03:22 PM
1USE Library
2GO
3IF OBJECT_ID ('dbo.Copy', 'U') IS NOT NULL
4 DROP TABLE Copy;
5GO
6IF OBJECT_ID ('dbo.Book', 'U') IS NOT NULL
7 DROP TABLE Book;
8GO
9
10CREATE TABLE dbo.Book(
11
12 BookID int,
13 Title char(50),
14 NumberOfPages smallint,
15 ISBN smallint,
16 Author char(35)
17 CONSTRAINT DF_Author DEFAULT ('unknown'),
18 --PublicationHouse char(40),
19 --PublicationDate numeric(4),
20 constraint PK_1 primary key (BookID),
21)
22GO
23
24
25CREATE TABLE dbo.Copy
26(
27
28 BookFID int,
29 price smallmoney default (0),
30 --yearOfPurchase numeric(4),
31 --WriteOffYear numeric (4),
32 Place char(25) default ('unknown'),
33 CONSTRAINT PK_T_Copy primary key (CopyID),
34 --constraint FK_T_Copy foreign key (BookFID)
35 --references dbo.Book(BookID)
36);
37GO
38
39if OBJECT_ID(N'dbo.v1', N'V') is not null
40 drop view dbo.v1;
41go
42create view dbo.v1 as
43 select
44 book.BookID as BookID,
45 book.title as title,
46 book.numberOfPages as numberOfPages,
47 book.ISBN as isbn,
48 book.author as author,
49 --Copy.CopyID as CopyID,
50 copy.price as price,
51 copy.place as place
52 from dbo.book
53 inner join dbo.copy
54 on Copy.BookFID = book.BookID;
55go
56/* insert dbo.book values (1, 'title1', 123, 3123, 'gogol'),
57 (1, 'title2', 13, 3125, 'lermontov'),
58 (3, 'title3', 133, 4243, 'akhmatova'),
59 (4, 'title4', 23, 6923, 'blok'),
60 (5, 'title2', 63, 5463, 'esenin');
61 go
62
63 insert dbo.copy values (1, 143, 'first'),
64 (1, 147, 'first'),
65 (1, 119, 'first'),
66 (2, 1708, 'second'),
67 (2, 1678, 'second'),
68 (3, 1998, 'third'),
69 (4, 1708, 'forth'),
70 (5, 1890, 'fifth');
71 go*/
72
73
74 if OBJECT_ID(N'dbo.bookInsert', N'TR') is not null
75 drop trigger dbo.bookInsert
76go
77create trigger dbo.bookInsert
78 on dbo.book
79 after insert as
80 begin
81 SET NOCOUNT ON
82 insert into dbo.Copy
83 select BookID, 0, 'unknown'
84 from inserted;
85 print 'Dot forget to update copies!'
86 end
87GO
88insert dbo.book values (1, 'title1', 123, 3123, 'gogol'),
89 (2, 'title2', 13, 3125, 'lermontov'),
90 (3, 'title3', 133, 4243, 'akhmatova')
91
92if OBJECT_ID(N'dbo.bookUpdate', N'TR') is not null
93 drop trigger dbo.bookUpdate
94go
95create trigger dbo.bookUpdate
96 on dbo.book
97 INSTEAD OF UPDATE as
98 begin
99 SET NOCOUNT ON
100 if update (bookid) Raiserror ('cant be modified!', 10, 1)
101 else
102 begin
103 Update book
104 set book.title = inserted.title,
105 book.NumberOfPages = inserted.NumberOfPages,
106 book.isbn = inserted.isbn,
107 book.author = inserted.author
108 FROM book, inserted
109 WHERE book.BookID = inserted.BookID
110 end
111 end
112go
113update dbo.book set title = 'title'
114select * from dbo.book
115if OBJECT_ID(N'dbo.bookDelete', N'TR') is not null
116 drop trigger dbo.bookDelete
117go
118create trigger dbo.bookDelete
119 on dbo.book
120 after delete as begin
121 begin
122 set nocount on
123 delete from dbo.Copy
124 where dbo.Copy.BookFID in (select deleted.BookID
125 from deleted);
126 end
127 end
128go
129delete dbo.book where BookID = 2
130go
131select * from copy
132disable trigger dbo.BookDelete on dbo.Book;
133go
134disable trigger dbo.BookUpdate on dbo.book;
135go
136disable trigger dbo.bookInsert on dbo.Book;
137go
138
139if OBJECT_ID(N'dbo.ViewInsert', N'TR') is not null
140 drop trigger dbo.ViewInsert
141go
142create trigger dbo.ViewInsert
143 on dbo.v1
144 instead of insert
145 as
146 begin
147 set nocount on
148 if (NOT EXISTS (SELECT book.bookID, book.ISBN
149 FROM book, inserted
150 WHERE (book.ISBN = inserted.isbn) and (book.BookID = inserted.BookID)))
151 begin
152 insert into dbo.book
153 select
154 inserted.BookID,
155 inserted.title,
156 inserted.numberOfPages,
157 inserted.isbn,
158 inserted.author
159 from inserted
160
161 insert into dbo.copy
162 select
163 inserted.BookID,
164 inserted.price,
165 inserted.place
166 from inserted
167 end
168 ELSE
169 RAISERROR('This book exists!', 10, 1);
170 end
171go
172
173
174insert into dbo.v1 values
175 (3, 'tit1', 234, 23243, 'ff', 11, 'first'),
176 (2, 'tit1', 234, 23243, 'ff', 11, 'first'),
177 (2, 'tit2', 354, 3456, 'esenin', 22, 'second')
178insert into dbo.v1 values
179 (2, 'tit2', 354, 3456, 'esenin', 22, 'second')
180insert into dbo.v1 values
181 (4, 'tit3', 33, 9835, 'ahmatova', 33, 'third')
182select * from dbo.v1
183select * from dbo.book
184select * from dbo.Copy
185
186
187go
188
189if OBJECT_ID(N'dbo.ViewDelete', N'TR') is not null
190 drop trigger dbo.ViewDelete
191go
192create trigger dbo.ViewDelete
193 on dbo.v1
194 for delete
195 as
196 begin
197 delete
198 declare @a int;
199 select @a = count(*) from dbo.copy where dbo.copy.bookfid in (select deleted.bookid from deleted);
200 if (@a = 0)
201 delete from dbo.book where dbo.Book.BookID in (select deleted.bookid from deleted)
202 end
203go
204
205
206delete from dbo.V1
207 where V1.price = 33
208delete from dbo.V1
209 where v1.place = 'second'
210select * from dbo.v1
211select * from dbo.book
212select * from dbo.Copy
213
214if OBJECT_ID(N'dbo.ViewUpdate', N'TR') is not null
215 drop trigger dbo.ViewUpdate
216go
217create trigger dbo.ViewUpdate
218 on dbo.v1
219 instead of update
220 as
221 begin
222
223 if UPDATE(bookID)
224 RAISERROR('cant be modified', 16, 1)
225
226 if UPDATE(price) or UPDATE(place)
227 update dbo.copy
228 set
229 copy.price = inserted.price,
230 copy.place = inserted.place from dbo.copy, inserted
231 where copy.bookfid = inserted.bookID
232
233 end
234go
235
236update dbo.v1
237 set price = 451,
238 place = 'unknown'
239 where v1.bookid = 2;
240
241select * from dbo.v1
242select * from dbo.copy