· 6 years ago · Nov 27, 2019, 09:20 AM
1use master;
2go
3
4drop database if exists lab9;
5go
6
7create database lab9;
8go
9
10use lab9;
11go
12
13
14/* 1 */
15create table Application
16(
17 ID int identity (1,1) primary key not null,
18 InsuranceID int unique not null,
19 BankChequeEUR int not null,
20 Area char(30) not null default 'Schengen'
21);
22go
23
24create table VisaCenter
25(
26 ID int identity (1,1) primary key not null,
27 VisaCenterName char(30) not null,
28 Address char(30) not null
29);
30go
31
32
33create trigger Trigger_1_insert
34 on Application
35 after insert
36 as
37begin
38 insert into VisaCenter(VisaCenterName, Address) values ('VC_name_trigger_1', 'address_trigger_1')
39end;
40go
41
42
43create trigger Trigger_1_update
44 on Application
45 after update
46 as
47begin
48 if exists(select * from VisaCenter where Address = 'qwerty')
49 begin
50 raiserror ('something error 123456789', 16, 0)
51 end;
52
53 update VisaCenter
54 set Address = 'Moscow'
55 where VisaCenterName != 'NoName'
56end;
57go
58
59
60create trigger Trigger_1_delete
61 on Application
62 after delete
63 as
64begin
65 delete
66 from VisaCenter
67 where Address = 'Rostov'
68end;
69go
70
71
72insert into Application(InsuranceID, BankChequeEUR, Area)
73values (11, 250, 'Saratov'),
74 (22, 500, 'Krasnodar'),
75 (33, 1500, 'Kiev');
76go
77
78insert into Application(InsuranceID, BankChequeEUR, Area)
79values (44, 250, 'Saratov'),
80 (55, 500, 'Krasnodar'),
81 (66, 1500, 'Kiev');
82go
83
84insert into VisaCenter(VisaCenterName, Address)
85values ('NoName', 'Saratov'),
86 ('VC_cat', 'Krasnodar'),
87 ('VC_dog', 'Kiev'),
88 ('NoName', 'smth'),
89 ('pppp', 'Rostov'),
90 ('VC_camel', 'Rostov');
91go
92
93update Application
94set BankChequeEUR = 5000
95where Area = 'Kiev';
96go
97
98delete
99from Application
100where BankChequeEUR = 250;
101go
102
103
104/*select *
105from Application;
106go
107
108select *
109from VisaCenter;
110go*/
111
112
113/* raiserror */
114
115/*insert into VisaCenter(VisaCenterName, Address)
116values ('123', 'qwerty')
117
118update Application
119set BankChequeEUR = 777
120where Area = 'Kiev';
121go*/
122
123
124/* 2 */
125create table VisaCenterNew
126(
127 ID int identity (1,1) primary key not null,
128 VisaCenterName varchar(255) unique not null,
129 Address char(30) not null
130);
131go
132
133
134create table NewTariff
135(
136 ID int identity (1,1) primary key not null,
137 TariffName varchar(30),
138 VisaCenterName varchar(255)
139 constraint FK_VisaCenterNew_NewTariff
140 foreign key
141 references VisaCenterNew (VisaCenterName)
142 on update cascade
143);
144go
145
146
147
148create view VisaCenterTariffView with schemabinding
149as
150select nt.ID, vc.VisaCenterName, vc.Address, nt.TariffName
151from dbo.VisaCenterNew as vc
152 left join dbo.NewTariff as nt
153 on vc.ID = nt.ID;
154go
155
156/*select *
157from VisaCenterTariffView;
158go*/
159
160
161create trigger Trigger_2_insert
162 on VisaCenterTariffView
163 instead of insert
164 as
165begin
166 insert into VisaCenterNew(VisaCenterName, Address)
167 select VisaCenterName, Address
168 from (
169 select VisaCenterName, Address
170 from inserted
171 group by VisaCenterName, Address
172 )
173 as VC_A
174 where not exists(
175 select *
176 from VisaCenterNew
177 as VCN
178 where VCN.VisaCenterName = VC_A.VisaCenterName
179 )
180
181 insert into NewTariff(TariffName, VisaCenterName)
182 select i.TariffName, i.VisaCenterName
183 from inserted as i
184 join VisaCenterNew as VCN
185 on i.VisaCenterName = VCN.VisaCenterName
186
187 /*declare @VCName varchar(255) = convert(varchar(255), newid())
188 insert into VisaCenterNew(VisaCenterName, Address) values (@VCName, 'address_trigger_2')
189 insert into NewTariff(TariffName, VisaCenterName) values ('new_tariff_1', @VCName)*/
190end;
191go
192
193/*select *
194from VisaCenterTariffView*/
195insert into VisaCenterTariffView(VisaCenterName, Address, TariffName)
196values ('VC_name_xx', 'street_1', 'USA_Tariff_2xx'),
197 ('VC_name_xxx', 'street_1', 'Brazil_Tariff'),
198 ('VC_name_xxxxxx', 'street_1', 'Brazil_Tariff');
199select *
200from VisaCenterTariffView
201go
202
203create trigger Trigger_2_update
204 on VisaCenterTariffView
205 instead of update
206 as
207begin
208 if update(ID)
209 begin
210 raiserror ('ERROR: prohibited to change ID!', 16, 1)
211 end
212
213 if update(VisaCenterName) and update(Address) and update(TariffName)
214 begin
215 if not exists(
216 select *
217 from VisaCenterNew as vc
218 left join inserted as i
219 on vc.ID = i.ID
220 where vc.VisaCenterName = i.VisaCenterName
221 )
222 begin
223 update vc
224 set vc.VisaCenterName = i.VisaCenterName,
225 vc.Address = i.Address
226 from VisaCenterNew as vc
227 join inserted as i
228 on vc.ID = i.ID;
229
230 update nt
231 set nt.TariffName = i.TariffName,
232 nt.VisaCenterName = i.VisaCenterName
233 from NewTariff as nt
234 join inserted as i
235 on nt.ID = i.ID;
236 end
237 end
238
239 if update(VisaCenterName) and update(Address)
240 begin
241 if not exists(
242 select *
243 from VisaCenterNew as vc
244 left join inserted as i
245 on vc.ID = i.ID
246 where vc.VisaCenterName = i.VisaCenterName
247 )
248 begin
249 update vc
250 set vc.VisaCenterName = i.VisaCenterName,
251 vc.Address = i.Address
252 from VisaCenterNew as vc
253 join inserted as i
254 on vc.ID = i.ID;
255
256 update nt
257 set nt.VisaCenterName = i.VisaCenterName
258 from NewTariff as nt
259 join inserted as i
260 on nt.ID = i.ID;
261 end
262 end
263
264 if update(VisaCenterName) and update(TariffName)
265 begin
266 if not exists(
267 select *
268 from VisaCenterNew as vc
269 left join inserted as i
270 on vc.ID = i.ID
271 where vc.VisaCenterName = i.VisaCenterName
272 )
273 begin
274 update vc
275 set vc.VisaCenterName = i.VisaCenterName
276 from VisaCenterNew as vc
277 join inserted as i
278 on vc.ID = i.ID;
279
280 update nt
281 set nt.TariffName = i.TariffName,
282 nt.VisaCenterName = i.VisaCenterName
283 from NewTariff as nt
284 join inserted as i
285 on nt.ID = i.ID;
286 end
287 end
288
289 if update(Address) and update(TariffName)
290 begin
291 update vc
292 set vc.Address = i.Address
293 from VisaCenterNew as vc
294 join inserted as i
295 on vc.ID = i.ID;
296
297 update nt
298 set nt.TariffName = i.TariffName
299 from NewTariff as nt
300 join inserted as i
301 on nt.ID = i.ID;
302 end
303
304 if update(VisaCenterName)
305 begin
306 if not exists(
307 select *
308 from VisaCenterNew as vc
309 left join inserted as i
310 on vc.ID = i.ID
311 where vc.VisaCenterName = i.VisaCenterName
312 )
313 begin
314 update vc
315 set vc.VisaCenterName = i.VisaCenterName
316 from VisaCenterNew as vc
317 join inserted as i
318 on vc.ID = i.ID;
319
320 update nt
321 set nt.VisaCenterName = i.VisaCenterName
322 from NewTariff as nt
323 join inserted as i
324 on nt.ID = i.ID;
325 end
326 end
327
328 if update(Address)
329 begin
330 update vc
331 set vc.Address = i.Address
332 from VisaCenterNew as vc
333 join inserted as i
334 on vc.ID = i.ID;
335 end
336
337 if update(TariffName)
338 begin
339 update nt
340 set nt.TariffName = i.TariffName
341 from NewTariff as nt
342 join inserted as i
343 on nt.ID = i.ID;
344 end
345end;
346go
347
348/*select * from VisaCenterTariffView*/
349update VisaCenterTariffView
350set TariffName = TariffName + 'LLL'
351where ID = 2;
352/*select *
353from VisaCenterTariffView
354go
355
356select *
357from VisaCenterNew;
358go
359
360
361select *
362from NewTariff;
363go*/
364
365update VisaCenterTariffView
366set VisaCenterName = 'VC_name_test',
367 Address = 'street_test'
368where ID = 1;
369go
370
371/*select *
372from VisaCenterTariffView
373go
374
375select *
376from VisaCenterNew;
377go
378
379
380select *
381from NewTariff;
382go*/
383
384update VisaCenterTariffView
385set VisaCenterName = 'VC_name_test1',
386 Address = 'street_hack'
387where ID = 2;
388go
389
390select *
391from VisaCenterTariffView
392go
393
394select *
395from VisaCenterNew;
396go
397
398
399select *
400from NewTariff;
401go
402
403
404create trigger Trigger_2_delete
405 on VisaCenterTariffView
406 instead of delete
407 as
408begin
409 delete
410 from NewTariff
411 where NewTariff.TariffName in
412 (select TariffName from deleted);
413end;
414go
415
416
417
418/*insert into VisaCenterTariffView(VisaCenterName, Address, TariffName)
419values ('VC_name_1', 'street_1', 'USA_Tariff'),
420 ('VC_name_2', 'street_2', 'Brazil_Tariff');
421go*/
422
423
424/*select *
425from VisaCenterNew;
426go
427
428select *
429from NewTariff;
430go
431
432*/
433
434
435/*select *
436from VisaCenterNew;
437go
438
439select *
440from NewTariff;
441go
442
443select *
444from VisaCenterTariffView;
445go
446*/
447
448
449/*update VisaCenterTariffView
450set TariffName = 'Mexico_Tariff'
451where TariffName = 'Brazil_Tariff';
452go*/
453
454/*select *
455from VisaCenterNew;
456go
457
458select *
459from NewTariff;
460go
461
462select *
463from VisaCenterTariffView;
464go*/
465
466
467/*delete
468from VisaCenterTariffView
469where TariffName = 'USA_Tariff';
470go*/
471
472/*select *
473from VisaCenterNew;
474go
475
476select *
477from NewTariff;
478go
479
480select *
481from VisaCenterTariffView;
482go*/
483
484
485/* raiserror */
486
487/*update ApplicationView
488set ID = 55
489where Area = 'Brazil';
490go*/