· 6 years ago · Dec 10, 2019, 05:56 PM
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 ('VC_zebra', '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)
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 end
230 end
231
232 if update(VisaCenterName) and update(TariffName)
233 begin
234 if not exists(
235 select *
236 from VisaCenterNew as vc
237 left join inserted as i
238 on vc.ID = i.ID
239 where vc.VisaCenterName = i.VisaCenterName
240 )
241 begin
242 update nt
243 set nt.TariffName = i.TariffName,
244 nt.VisaCenterName = i.VisaCenterName
245 from NewTariff as nt
246 join inserted as i
247 on nt.ID = i.ID;
248 end
249 end
250end;
251go
252
253/*select * from VisaCenterTariffView*/
254update VisaCenterTariffView
255set TariffName = TariffName + 'LLL'
256where ID = 2;
257/*select *
258from VisaCenterTariffView
259go
260
261select *
262from VisaCenterNew;
263go
264
265
266select *
267from NewTariff;
268go*/
269
270update VisaCenterTariffView
271set VisaCenterName = 'VC_name_test',
272 Address = 'street_test'
273where ID = 1;
274go
275
276/*select *
277from VisaCenterTariffView
278go
279
280select *
281from VisaCenterNew;
282go
283
284
285select *
286from NewTariff;
287go*/
288
289update VisaCenterTariffView
290set VisaCenterName = 'VC_name_test1',
291 Address = 'street_hack'
292where ID = 2;
293go
294
295select *
296from VisaCenterTariffView
297go
298
299select *
300from VisaCenterNew;
301go
302
303
304select *
305from NewTariff;
306go
307
308
309create trigger Trigger_2_delete
310 on VisaCenterTariffView
311 instead of delete
312 as
313begin
314 delete
315 from NewTariff
316 where NewTariff.TariffName in
317 (select TariffName from deleted);
318end;
319go
320
321
322
323/*insert into VisaCenterTariffView(VisaCenterName, Address, TariffName)
324values ('VC_name_1', 'street_1', 'USA_Tariff'),
325 ('VC_name_2', 'street_2', 'Brazil_Tariff'); То и катанул
326go*/
327
328
329/*select *
330from VisaCenterNew;
331go
332
333select *
334from NewTariff;
335go
336
337*/
338
339
340/*select *
341from VisaCenterNew;
342go
343
344select *
345from NewTariff;
346go
347
348select *
349from VisaCenterTariffView;
350go
351*/
352
353
354/*update VisaCenterTariffView
355set TariffName = 'Mexico_Tariff'
356where TariffName = 'Brazil_Tariff';
357go*/
358
359/*select *
360from VisaCenterNew;
361go
362
363select *
364from NewTariff;
365go
366
367select *
368from VisaCenterTariffView;
369go*/
370
371
372/*delete
373from VisaCenterTariffView
374where TariffName = 'USA_Tariff';
375go*/
376
377/*select *
378from VisaCenterNew;
379go
380
381select *
382from NewTariff;
383go
384
385select *
386from VisaCenterTariffView;
387go*/
388
389
390/* raiserror */
391
392/*update ApplicationView
393set ID = 55
394where Area = 'Brazil';
395go*/