· 6 years ago · Dec 16, 2019, 08:28 PM
1// file 1
2
3use master;
4go
5
6drop database if exists lab15_db_1;
7go
8
9create database lab15_db_1;
10go
11
12use lab15_db_1;
13go
14
15create table VisaCenter
16(
17 VisaCenterName varchar(30) primary key not null,
18 Address varchar(50) not null,
19 WorkTime time not null default '10:00:00'
20);
21go
22
23
24create trigger Trigger_VisaCenter_insert
25 on lab15_db_1.dbo.VisaCenter
26 instead of insert
27 as
28begin
29 if exists(
30 select *
31 from lab15_db_1.dbo.VisaCenter as vc
32 join inserted as i
33 on vc.VisaCenterName = i.VisaCenterName
34 )
35 begin
36 raiserror ('ERROR: This VisaCenter already exists', 16, 0);
37 end
38 else
39 begin
40 insert into lab15_db_1.dbo.VisaCenter
41 select *
42 from inserted
43
44 insert into lab15_db_2.dbo.Tariff (VisaCenterName)
45 select VisaCenterName
46 from inserted
47 end
48end;
49go
50
51
52create trigger Trigger_VisaCenter_update
53 on lab15_db_1.dbo.VisaCenter
54 instead of update
55 as
56begin
57 if update(VisaCenterName)
58 begin
59 if exists(
60 select *
61 from lab15_db_1.dbo.VisaCenter as vc
62 join inserted as i
63 on vc.VisaCenterName = i.VisaCenterName
64 )
65 begin
66 raiserror ('ERROR: This VisaCenterName already exists!', 16, 0);
67 end
68 else
69 begin
70 update vc
71 set vc.VisaCenterName = i.VisaCenterName
72 from lab15_db_1.dbo.VisaCenter as vc
73 join inserted as i
74 on vc.VisaCenterName
75 in (
76 select VisaCenterName
77 from deleted
78 );
79
80 update t
81 set t.VisaCenterName = i.VisaCenterName
82 from lab15_db_2.dbo.Tariff as t
83 join inserted as i
84 on t.VisaCenterName
85 in (
86 select VisaCenterName
87 from deleted
88 );
89 end
90 end
91
92 if update(Address)
93 begin
94 update vc
95 set vc.Address = i.Address
96 from lab15_db_1.dbo.VisaCenter as vc
97 join inserted as i
98 on vc.VisaCenterName = i.VisaCenterName;
99 end
100
101 if update(WorkTime)
102 begin
103 update vc
104 set vc.WorkTime = i.WorkTime
105 from lab15_db_1.dbo.VisaCenter as vc
106 join inserted as i
107 on vc.VisaCenterName = i.VisaCenterName;
108 end
109end;
110go
111
112
113create trigger Trigger_VisaCenter_delete
114 on lab15_db_1.dbo.VisaCenter
115 instead of delete
116 as
117begin
118 delete
119 from lab15_db_1.dbo.VisaCenter
120 where VisaCenterName in (
121 select VisaCenterName
122 from deleted
123 )
124
125 if exists(
126 select *
127 from lab15_db_2.dbo.Tariff as t
128 join deleted as d
129 on t.VisaCenterName = d.VisaCenterName
130 )
131 begin
132 delete
133 from lab15_db_2.dbo.Tariff
134 where VisaCenterName in (
135 select VisaCenterName
136 from deleted
137 );
138 end
139end
140go
141
142
143//file 2
144
145use master;
146go
147
148drop database if exists lab15_db_2;
149go
150
151create database lab15_db_2;
152go
153
154use lab15_db_2;
155go
156
157create table Tariff
158(
159 VisaCenterName varchar(30) not null,
160 TariffName varchar(30) default 'edit tariff name',
161 primary key (VisaCenterName, TariffName),
162
163 Conditions varchar(1000) default 'edit conditions',
164 Price money default 3000
165);
166go
167
168
169create trigger Trigger_Tariff_insert
170 on lab15_db_2.dbo.Tariff
171 instead of insert
172 as
173begin
174 if not exists(
175 select *
176 from lab15_db_1.dbo.VisaCenter as vc
177 join inserted as i
178 on vc.VisaCenterName = i.VisaCenterName
179 )
180 begin
181 raiserror ('This VisaCenter does not exist', 16, 1);
182 end
183 else
184 begin
185 insert into lab15_db_2.dbo.Tariff
186 select *
187 from inserted;
188 end
189end;
190go
191
192
193create trigger Trigger_Tariff_update
194 on lab15_db_2.dbo.Tariff
195 instead of update
196 as
197begin
198 if update(VisaCenterName)
199 begin
200 raiserror ('Prohibit to change a VisaCenterName', 16, 2);
201 end
202
203 if update(TariffName)
204 begin
205 update t
206 set TariffName = i.TariffName
207 from lab15_db_2.dbo.Tariff as t,
208 inserted as i
209 where t.VisaCenterName = i.VisaCenterName
210 end
211
212 if update(Conditions)
213 begin
214 update t
215 set Conditions = i.Conditions
216 from lab15_db_2.dbo.Tariff as t,
217 inserted as i
218 where t.VisaCenterName = i.VisaCenterName
219 end
220
221 if update(Price)
222 begin
223 update t
224 set Price = i.Price
225 from lab15_db_2.dbo.Tariff as t,
226 inserted as i
227 where t.VisaCenterName = i.VisaCenterName
228 end
229end;
230go
231
232
233//file 3
234
235use master;
236go
237
238drop database if exists lab15_db_union;
239go
240
241create database lab15_db_union;
242go
243
244use lab15_db_union;
245go
246
247/*
248create view VC_T_View
249as
250select vc.VisaCenterName, vc.Address, t.TariffName, t.Price
251from lab15_db_1.dbo.VisaCenter as vc
252 right join lab15_db_2.dbo.Tariff as t
253 on vc.VisaCenterName = t.VisaCenterName;*/
254
255
256insert into lab15_db_1.dbo.VisaCenter(VisaCenterName, Address)
257values ('VisaCenter_1', 'Address_1'),
258 ('VisaCenter_2', 'Address_2');
259
260select *
261from lab15_db_1.dbo.VisaCenter;
262go
263
264select *
265from lab15_db_2.dbo.Tariff;
266go
267
268
269update lab15_db_2.dbo.Tariff
270set TariffName = 'Tariff_updated'
271where TariffName = 'edit tariff name';
272go
273
274
275insert into lab15_db_2.dbo.Tariff(VisaCenterName)
276values ('VisaCenter_1'),
277 ('VisaCenter_2');
278
279
280select *
281from lab15_db_2.dbo.Tariff;
282go