· 6 years ago · Jun 01, 2019, 10:34 PM
1#DODAWANIE
2#GOAL
3DELIMITER $$
4DROP TRIGGER IF EXISTS add_Goal $$
5CREATE TRIGGER add_Goal
6 BEFORE INSERT ON Goalss
7 FOR EACH ROW
8BEGIN
9 IF (SELECT Finished From Matche WHERE Match_id = NEW.Match_id) = 0 THEN
10 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot assign goals to match that was not played';
11 END IF;
12
13END$$
14DELIMITER ;
15
16#ban # tutAJ 2
17DELIMITER $$
18DROP TRIGGER IF EXISTS before_create_ban $$ # tutAJ JEST BŁAD
19CREATE TRIGGER before_create_ban
20 before INSERT ON Ban
21 FOR EACH ROW
22BEGIN
23 IF EXISTS (SELECT * From Ban where Match_id = NEW.Match_id AND Player_id=NEW.Player_id) then
24 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot ban twice the same player';
25 END IF;
26
27END$$
28DELIMITER ;
29
30#MODYFIKACJE TUTAJ
31#COACH
32DELIMITER $$
33DROP TRIGGER IF EXISTS after_contract_change $$
34CREATE TRIGGER after_contract_change
35 AFTER UPDATE ON Contract
36 FOR EACH ROW
37BEGIN
38 IF OLD.Contract_id <> NEW.Contract_id THEN
39 DELETE FROM Contract WHERE Contract_id = OLD.Contract_id;
40 UPDATE Contract SET Contract_id = OLD.Contract_id WHERE Contact_id = NEW.Contract_id;
41 END IF;
42
43END$$
44DELIMITER ;
45#Stadium
46DELIMITER $$
47DROP TRIGGER IF EXISTS after_Stadium_change $$
48CREATE TRIGGER after_Stadium_change
49 BEFORE UPDATE ON Stadium
50 FOR EACH ROW
51BEGIN
52 IF OLD.Stadium_id <> NEW.Stadium_id THEN
53 DELETE FROM Stadium WHERE Stadium_id = OLD.Stadium_id;
54 UPDATE Stadium SET NEW.Stadium_id = OLD.Stadium_id WHERE Stadium_id = NEW.Stadium_id;
55 END IF;
56
57END$$
58DELIMITER ;
59# Football_club
60DELIMITER $$
61DROP TRIGGER IF EXISTS after_Football_club_change $$
62CREATE TRIGGER after_Football_club_change
63 AFTER UPDATE ON Football_club
64 FOR EACH ROW
65BEGIN
66 IF OLD.Football_club_id <> NEW.Football_club_id THEN
67 DELETE FROM Football_club WHERE Football_club_id = OLD.Football_club_id;
68 UPDATE Football_club SET Football_club_id = OLD.Football_club_id WHERE Football_club_id = NEW.Football_club_id;
69 END IF;
70
71END$$
72DELIMITER ;
73# Player
74DELIMITER $$
75DROP TRIGGER IF EXISTS after_Player_change_club $$
76CREATE TRIGGER after_Player_change_club
77 AFTER UPDATE ON Player
78 FOR EACH ROW
79BEGIN
80 IF OLD.Player_id <> NEW.Player_id THEN
81 DELETE FROM Player WHERE Player_id = OLD.Player_id;
82 UPDATE Player SET Player_id = OLD.Player_id WHERE Player_id = NEW.Player_id;
83 END IF;
84
85END$$
86DELIMITER ;
87#BAN
88DELIMITER $$
89DROP TRIGGER IF EXISTS after_Ban_change $$
90CREATE TRIGGER after_Ban_change
91 AFTER UPDATE ON Ban
92 FOR EACH ROW
93BEGIN
94 IF OLD.Ban_id <> NEW.Ban_id THEN
95 DELETE FROM Ban WHERE Ban_id = OLD.Ban_id;
96 UPDATE Ban SET Ban_id = OLD.Ban_id WHERE Ban_id = NEW.Ban_id;
97 END IF;
98 IF OLD.Player_id <> NEW.Player_id THEN
99 DELETE FROM Ban WHERE Player_id = OLD.Player_id;
100 UPDATE Ban SET Player_id = OLD.Player_id WHERE Player_id = NEW.Player_id;
101 END IF;
102 IF OLD.Match_id <> NEW.Match_id THEN
103 DELETE FROM Ban WHERE Match_id = OLD.Match_id;
104 UPDATE Ban SET Match_id = OLD.Match_id WHERE Match_id = NEW.Match_id;
105 END IF;
106
107END$$
108DELIMITER ;
109#GOAL
110DELIMITER $$
111DROP TRIGGER IF EXISTS after_Goal_change $$
112CREATE TRIGGER after_Goal_change
113 AFTER UPDATE ON Goal
114 FOR EACH ROW
115BEGIN
116 IF OLD.Goal_id <> NEW.Goal_id THEN
117 DELETE FROM Goal WHERE Goal_id = OLD.Goal_id;
118 UPDATE Ban SET Goal_id = OLD.Goal_id WHERE Goal_id = NEW.Goal_id;
119 END IF;
120 IF OLD.Match_id <> NEW.Match_id THEN
121 DELETE FROM Goal WHERE Match_id = OLD.Match_id;
122 UPDATE Goal SET Match_id = OLD.Match_id WHERE Match_id = NEW.Match_id;
123 END IF;
124
125END$$
126DELIMITER ;
127#match
128DELIMITER $$ # TO nie ten
129DROP TRIGGER IF EXISTS After_match $$
130CREATE TRIGGER After_match
131 AFTER UPDATE ON Matche
132 FOR EACH ROW
133BEGIN
134 IF OLD.Finished <> NEW.Finished AND OLD.Finished = 1 THEN
135 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'you cannot channge finished once you changed it before';
136 END IF;
137
138END$$
139DELIMITER ;
140#USUWANIE
141#BAN
142DELIMITER $$
143DROP TRIGGER IF EXISTS delete_Ban $$
144CREATE TRIGGER delete_Ban
145 BEFORE DELETE ON Ban
146 FOR EACH ROW
147BEGIN
148 IF OLD.Length = 0 THEN
149 DELETE FROM Ban
150 where OLD.Ban_id = Ban_id;
151 ELSE
152 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'you cannot delete ban';
153 end if;
154END$$
155DELIMITER ;
156#Goal
157DELIMITER $$
158DROP TRIGGER IF EXISTS delete_Goal $$
159CREATE TRIGGER delete_Goal
160 BEFORE DELETE ON Goal
161 FOR EACH ROW
162BEGIN
163 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'you cannot delete Goal';
164END$$
165DELIMITER ;
166#Match
167DELIMITER $$
168DROP TRIGGER IF EXISTS before_delete_match $$
169CREATE TRIGGER before_delete_match
170 before Delete on Matche
171 FOR EACH ROW
172BEGIN
173 IF OLD.Finished = 0 THEN
174 DELETE FROM Referee_Match where Match_id = OLD.Match_id;
175 DELETE FROM Player_Match where Match_id = OLD.Match_id;
176 DELETE FROM Football_club_Match where Match_id = OLD.Match_id;
177 else
178 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'table t1 does not support deletion';
179 end if;
180END$$
181DELIMITER ;
182#Player
183DELIMITER $$
184DROP TRIGGER IF EXISTS after_player_delete $$
185CREATE TRIGGER after_player_delete
186 BEFORE delete ON Player
187 FOR EACH ROW
188BEGIN
189 DELETE FROM Ban
190 where Player_id = OLD.Player_id;
191
192 DELETE FROM Player_Match
193 where Player_id = OLD.Player_id;
194
195 DELETE FROM Contract
196 where OLD.Contract_id = Contract_id;
197
198 update Gol
199 set Scorer_id = NULL
200 where Scorer_id = OLD.Player_id;
201
202END$$
203DELIMITER ;
204#Referee
205DELIMITER $$
206DROP TRIGGER IF EXISTS delete_Referee $$
207CREATE TRIGGER delete_Referee
208 BEFORE DELETE ON Referee
209 FOR EACH ROW
210BEGIN
211 UPDATE Referee_Match SET Licence_number = NULL WHERE Licence_number=OLD.Licence_number;
212END$$
213DELIMITER ;
214#Football_club
215DELIMITER $$
216DROP TRIGGER IF EXISTS before_delete_club $$
217CREATE TRIGGER before_delete_club
218 BEFORE Delete on Football_club
219 FOR EACH ROW
220BEGIN
221 IF exists (Select Finished From Matches Natural join Football_club_Match where Finished = 1) THEN
222 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'table does not support deletion';
223 else
224 Delete From Matche where Match_id in
225 (Select Match_id FROM Football_club_Match where Team_id = OLD.Football_club_id);
226 Update Player set Football_club_id = NULL where Football_club_id = OLD.Football_club_id;
227 end if;
228END$$
229DELIMITER ;
230
231
232
233#Reszta
234
235DELIMITER $$
236DROP TRIGGER IF EXISTS after_Referee_add $$
237CREATE TRIGGER after_Referee_add
238 BEFORE INSERT on Football_club_Match
239 FOR EACH ROW
240BEGIN
241 IF 3 < (Select COUNT(*) From Referee_Match where NEW.Match_id = Match_id) THEN
242 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Already max 3 referees';
243 END if;
244END$$
245DELIMITER ;
246
247
248DELIMITER $$
249DROP TRIGGER IF EXISTS after_Football_club_Match $$
250CREATE TRIGGER after_Football_club_Match
251 BEFORE INSERT on Football_club_Match
252 FOR EACH ROW
253BEGIN
254 IF NEW.Home = 1 AND 0 < (Select count(*) From Football_club_Match where Home = 1 AND NEW.Match_id = Match_id) THEN
255 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Home team alreadu exists';
256 END if;
257 IF NEW.Home = 0 AND 0 < (Select count(*) From Football_club_Match where Home = 0 AND NEW.Match_id = Match_id) THEN
258 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Away team alreadu exists';
259 END if;
260END$$
261DELIMITER ;
262
263DELIMITER $$
264DROP TRIGGER IF EXISTS before_Football_club_Match $$
265CREATE TRIGGER before_Football_club_Match
266 BEFORE INSERT on Player_Match
267 FOR EACH ROW
268BEGIN
269 DECLARE v int(10);
270 SELECt Football_club_id INTO v FROM Player WHERE Player_id=NEW.Player_id;
271 IF v is NULL THEN
272 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot sign this player to match because he is free agent';
273 END IF;
274 IF v NOT IN (SELECT Team_id FROM Football_club_Match WHERE Match_id=NEW.Match_id) THEN
275 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot sign this player to match';
276 end if;
277 IF EXIStS(SELECT Ban_id FROM Ban WHERE Player_id=NEW.Player_id) THEN
278 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You cannot sign this player to match becaouse of ban';
279 END IF;
280END$$
281DELIMITER ;