· 6 years ago · Nov 25, 2019, 01:46 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
103select *
104from Application;
105go
106
107select *
108from VisaCenter;
109go
110
111
112
113/* 2 */
114create view ApplicationView /*with schemabinding*/
115as
116select ID, BankChequeEUR, Area
117from dbo.Application;
118go
119
120select *
121from ApplicationView;
122go
123
124
125
126create trigger Trigger_2_insert
127 on ApplicationView
128 instead of insert
129 as
130begin
131 insert into VisaCenter(VisaCenterName, Address) values ('VC_name_trigger_2', 'address_trigger_2')
132 insert into Application(InsuranceID, BankChequeEUR, Area) values (77, 2000, 'Vladimir')
133end;
134go
135
136
137
138create trigger Trigger_2_update
139 on ApplicationView
140 instead of update
141 as
142begin
143 if update(ID)
144 begin
145 raiserror ('ERROR: prohibited to change ID!', 16, 1)
146 end
147end;
148go
149
150
151
152create trigger Trigger_2_delete
153 on ApplicationView
154 instead of delete
155 as
156begin
157 delete
158 from VisaCenter
159 where Address = 'Moscow'
160end;
161go
162
163
164
165insert into ApplicationView(BankChequeEUR, Area)
166values (5, 'USA'),
167 (7, 'Brazil');
168go
169
170select *
171from Application;
172go
173
174select *
175from VisaCenter;
176go
177
178
179
180update ApplicationView
181set Area = 'Mexico'
182where Area = 'Brazil';
183go
184
185select *
186from ApplicationView;
187go
188
189
190
191delete from ApplicationView
192where BankChequeEUR = 1500;
193go
194
195select * from ApplicationView;
196go
197
198select *
199from VisaCenter;
200go