JHE7AR4M

1CREATE TABLE house(
2	id INTEGER AUTO_INCREMENT PRIMARY KEY NOT NULL,
3    name VARCHAR(30) NOT NULL,
4    number_of_wins INTEGER DEFAULT 0
5);
6
7CREATE TABLE person(
8	id INTEGER AUTO_INCREMENT PRIMARY KEY NOT NULL,
9    name VARCHAR(30) NOT NULL,
10    house_id INTEGER DEFAULT NULL,
11  
12
13  FOREIGN KEY (house_id) REFERENCES house(id)
14    ON DELETE CASCADE
15    ON UPDATE CASCADE
16);
17
18CREATE TABLE battle(
19	id INTEGER AUTO_INCREMENT PRIMARY KEY NOT NULL,
20	winner_id INTEGER DEFAULT NULL,
21    loser_id INTEGER DEFAULT NULL,
22  
23	FOREIGN KEY (winner_id) REFERENCES person(id)
24    ON DELETE CASCADE
25    ON UPDATE CASCADE,
26    FOREIGN KEY (loser_id) REFERENCES person(id)
27    ON DELETE CASCADE
28    ON UPDATE CASCADE
29);
30
31INSERT INTO house (name)
32VALUES
33	('Lanister'),
34    ('Tosho'),
35    ('Pisho');
36    
37INSERT INTO person (name, house_id)
38VALUES
39	('TA', 1),
40	('a', 1),
41	('b', 2),
42	('c', 2),
43	('d', 3),
44	('e', 3);
45INSERT INTO battle (winner_id, loser_id)
46VALUES
47	(1, 3),
48	(2, 4),
49	(5, 3),
50	(6, 4);
51    
52DROP TRIGGER IF EXISTS insert_battle;
53DROP TRIGGER IF EXISTS update_battle;
54DROP TRIGGER IF EXISTS delete_battle;
55
56DELIMITER $$
57
58CREATE TRIGGER insert_battle AFTER INSERT ON battle FOR EACH ROW
59BEGIN
60	UPDATE house h
61    LEFT JOIN person p ON p.id = NEW.winner_id
62    SET h.number_of_wins = h.number_of_wins + 1
63    WHERE h.id = p.house_id;
64END$$
65
66CREATE TRIGGER update_battle AFTER UPDATE ON battle FOR EACH ROW
67BEGIN
68UPDATE house h
69    LEFT JOIN person p ON p.id = OLD.winner_id
70    SET h.number_of_wins = h.number_of_wins - 1
71    WHERE h.id = p.house_id;
72
73	UPDATE house h
74    LEFT JOIN person p ON p.id = NEW.winner_id
75    SET h.number_of_wins = h.number_of_wins + 1
76    WHERE h.id = p.house_id;
77END$$
78
79CREATE TRIGGER delete_battle AFTER DELETE ON battle FOR EACH ROW
80BEGIN
81	UPDATE house h
82    LEFT JOIN person p ON p.id = OLD.winner_id
83    SET h.number_of_wins = h.number_of_wins - 1
84    WHERE h.id = p.house_id;
85END$$
86
87DELIMITER ;
88
89SELECT p.name
90FROM battle b
91LEFT JOIN person p ON p.id = b.winner_id;
92
93SELECT h.name
94FROM battle b
95LEFT JOIN person p ON p.id = b.loser_id
96LEFT JOIN house h ON h.id = p.house_id;
97
98SELECT p.name, p1.name
99FROM battle b
100LEFT JOIN person p ON p.id = b.winner_id
101LEFT JOIN person p1 ON p1.id = b.loser_id;