AST1hTEa

1-- 1. Да се направи така, че при добавяне
2-- на нов клас автоматично да се добавя и
3-- нов кораб със същото име и с година на
4-- пускане на вода = null.
5use ships;
6go
7create trigger t1
8on classes
9after insert
10as
11	insert into ships(name, class)
12	select class, class
13	from inserted;
14
15-- тестване:
16insert into classes
17values ('Test 1', 'bb', 'Bulgaria', 20, 20, 50000),
18       ('Test 2', 'bc', 'Bulgaria', 18, 21, 45000);
19select * from ships where name like 'Test %';
20drop trigger t1;
21
22-- 2. При изтриване на кораб автоматично да се изтрива и неговият клас, ако
23-- няма повече кораби от този клас.
24create trigger t2_1
25on ships
26after delete
27as
28	delete from classes
29	where class not in (select class from ships);
30go
31-- Забележка: ако преди това е имало класове без кораби - да не се пипат!
32create trigger t2
33on ships
34after delete
35as
36	delete from classes
37	where class not in (select class from ships)
38		and class in (select class from deleted);
39go
40-- Забележка: клас без кораби може да се получи и при update
41-- Забележка: ако не използвахме deleted,
42-- а направо ships, щяхме да изтрием и 
43-- класове, които са били празни и преди
44-- съответния delete
45
46-- тестване:
47-- в предишната задача вече сме добавили нов клас и нов кораб, ще използваме тях
48delete from ships
49where name like 'Test %';
50select * 
51from classes
52where class like  'Test %';
53drop trigger t2;
54go
55
56-- леко - искаме при изтриване на клас да се изтриват и 
57-- всички кораби - ами може с on delete cascade
58
59
60-- 3. Да се направи така, че ако при 
61-- добавяне или обновяване на кораб
62-- годината му на пускане е по-голяма от 
63-- текущата година, то годината му да бъде
64-- променена на null. (това е задача 3-А от 12.exerciseTriggers.pdf)
65
66-- в MSSQL няма BEFORE тригери, затова ще търсим друг начин
67create trigger t3
68on ships
69after insert, update
70as
71	update ships
72	set launched = null
73	where name in (select name
74					from inserted
75					where launched > year(getdate()));
76-- OR:	where launched > year(getdate()) and name in (select name from inserted);
77
78-- тестване:
79insert into ships values('Test','Iowa',2250);
80select * from ships where name='Test';
81delete from ships where name='Test';
82drop trigger t3;
83go
84-- горното решение ще гръмне, ако има check(launched <= year(getdate()) в ships!
85
86-- ако трябва в тялото на тригер да разграничим дали е бил извикан при insert или при update,
87-- можем да проверим if exists (select * from deleted)
88
89-- ако беше само за insert, можеше да направим instead of insert и всичко щеше да е наред
90create trigger t
91on ships
92instead of insert
93as
94insert into ships(name, class, launched)
95select name, class, case 
96			when launched > year(getdate()) then null 
97			else launched 
98			end
99from inserted;
100
101-- вариант без case: 
102-- insert into ships 
103-- select * from inserted where launched <= year(getdate())
104-- union all
105-- select name, class, null from inserted where launched > year(getdate());
106drop trigger t;
107go
108
109-- 4. При промяна на черно-бял филм на цветен съответният продуцент да получава $100000.
110-- Ако в една UPDATE заявка са били променени няколко филма на един продуцент, той да получи 
111-- само веднъж 100000.
112use movies;
113go
114
115create trigger t4
116on movie
117after update
118as
119	update movieexec
120	set networth = networth + 100000
121	where cert# in (select i.producerc#
122					from deleted d
123					join inserted i on d.title = i.title and d.year = i.year
124					where d.inColor = 'N' and i.inColor = 'Y');
125
126select * from movie;
127
128update movie
129set incolor = 'Y';
130
131select * from movieexec;
132
133update movie set incolor = 'N' where year = 2001;
134
135drop trigger t4;
136go
137
138-- Следващият пример е за валидация на данни. Всички задачи от такъв тип може да се решат аналогично.
139
140-- 5. Да не се допуска добавянето на ред
141-- в OUTCOMES, който да указва, че даден
142-- кораб е участвал в битка, преди да бъде
143-- пуснат на вода.
144
145-- Aко се добавят няколко реда и поне един от тях нарушава условието за коректност, 
146-- цялата операция ще бъде отменена.
147use ships;
148go
149
150create trigger t5
151on outcomes
152after insert
153as
154	if exists (select *
155				from inserted
156				join ships on ship = ships.name
157				join battles on battle = battles.name
158				where launched > year(battles.date))
159	begin
160		raiserror('Error: ship is launched after the battle', 16, 10); -- има само едно "е"
161		rollback;
162	end;
163
164-- проверка:
165insert into outcomes(ship, battle, result) 
166values('Iowa', 'North Atlantic', 'sunk');
167
168select * from outcomes
169where ship='Iowa';
170
171drop trigger t5;
172go
173
174
175-- С помощта на INSTEAD OF тригерите може да се изпълняват INSERT, UPDATE и DELETE заявки върху всеки изглед.
176
177-- 6. Да се създаде изглед за всички
178-- потънали кораби (име на кораб и битка), т.е. за всеки 
179-- потънал кораб да казва в коя битка е потънал,
180-- който да позволява insert, update, delete.
181
182create view SunkShips
183as
184select ship, battle
185from outcomes
186where result = 'sunk';
187go
188
189-- UPDATE и DELETE могат да се изпълнят безпроблемно,
190-- но при INSERT новият ред би имал result = null, което не ни върши работа.
191
192create trigger t6
193on SunkShips
194instead of insert
195as
196	insert into outcomes(ship, battle, result)
197	select ship, battle, 'sunk'
198	from inserted;
199
200drop trigger t6;
201
202-- 7. -- Симулиране на ON DELETE SET NULLS
203-- MS SQL не поддържа ON DELETE SET NULLS. Да се реализира с 
204-- тригери за външния ключ movie.producerc#
205go
206use movies;
207go
208
209create trigger t
210on movieexec
211instead of delete --не може after trigger заради FK; 
212-- в други СУБД има before тригери
213as
214begin
215	update movie
216	set producerc# = null
217	where producerc# in (select cert# from deleted);
218
219	-- следващата операция е тази, която по принцип щеше да се изпълни и без тригер
220	delete from movieexec
221	where cert# in (select cert# from deleted);
222end;
223
224-- ако имаме INSTEAD OF INSERT и искаме да изпълним INSERT заявката, която е била предвидена:
225-- INSERT INTO <table>
226-- SELECT * FROM INSERTED;
227
228go
229-- 12.exerciseTriggers.pdf
230
231-- Зад. 1. Да се напише тригер за таблицата MovieExec, който не позволява 
232-- средната стойност на Networth да е по-малка от 500 000 (ако при промени в 
233-- таблицата тази стойност стане по-малка от 500 000, промените да бъдат 
234-- отхвърлени).
235create trigger t
236on movieexec
237after insert, update, delete
238as
239	if (select AVG(networth)
240		from movieexec) < 500000
241	begin
242		raiserror('Error: Average networth cannot be < 500000', 16, 10);
243		rollback;
244	end;
245
246-- 3 Д) - модифицирана версия:
247-- При добавяне на нов запис в StarsIn, ако новият кортеж указва несъществуващ
248-- филм или актьор, да се добавят липсващите данни в съответната таблица
249-- (неизвестните данни да бъдат NULL):
250create trigger t
251on starsin
252instead of insert
253as
254begin
255	insert into moviestar(name)
256	select distinct starname
257	from inserted
258	where starname not in (select name from moviestar);
259	
260	insert into movie(title, year)
261	select distinct movietitle, movieyear
262	from inserted
263	where not exists (select * from movie
264					  where title = movietitle and YEAR = movieyear);
265	
266	insert into starsin
267	select * 
268	from inserted;
269end;
270
271-- 2 Б) Никой производител на компютри не може да произвежда и принтери;
272use pc;
273create trigger t
274on product
275after insert, update
276as
277if exists  (select * 
278			from Product p1
279			join Product p2 on p1.maker = p2.maker
280			where p1.type = 'PC' and p2.type = 'Printer')
281begin
282	raiserror('...', 16, 10);
283	rollback;
284end;
285
286-- 2 Г) При променяне на данните в таблицата Laptop се уверете, че средната 
287-- цена на лаптопите за всеки производител е поне 2000;
288create trigger t
289on laptop
290after update
291as
292if exists  (select maker
293			from laptop
294			join Product on laptop.model = Product.model
295			group by maker
296			having AVG(price) < 2000)
297begin
298	raiserror('...', 16, 10);
299	rollback;
300end;
301
302-- 2 Д) - по-добре с CHECK
303
304-- 3 В) Никой клас не може да има повече от два кораба;
305create trigger t
306on ships
307after insert, update
308as
309if exists  (select class
310			from ships
311			group by class
312			having COUNT(*) > 2)
313begin
314	raiserror('...', 16, 10);
315	rollback;
316end;
317
318-- 3 E) прилича на 2 Г) и 3 В)
319
320-- 3 Ж) подобно нещо вече направихме за outcomes, но трябва тригер и за battles
321
322create trigger t
323on outcomes
324after insert, update
325as
326if exists  (select *
327			from outcomes o1
328			join battles b1 on o1.battle = b1.name
329			join outcomes o2 on o1.ship = o2.ship
330			join battles b2 on o2.battle = b2.name
331			where o1.result = 'sunk'
332			and b1.date < b2.date)
333begin
334	raiserror('...', 16, 10);
335	rollback;
336end;
337-- същият тригер и за battles, но само за update
338
339-- примерни теоретични въпроси...