C4JgE6ww

1-- this trigger checks number of transfers done by each hub officer and assigns a rank in the ranks table to ho(for bragging rights)
2DROP TRIGGER IF EXISTS update_ranks;
3DELIMITER //
4CREATE TRIGGER update_ranks AFTER UPDATE ON players
5  FOR EACH ROW
6
7  BEGIN
8
9    DECLARE currentHoId INT;
10    DECLARE numberOfTransfers INT;
11    DECLARE rows INT;
12    DECLARE i INT;
13    SET i = 0;
14
15
16    -- temp ho's
17    DROP VIEW IF EXISTS temp;
18    CREATE VIEW temp AS
19    select users_id, user_name from users where role_id = 3;
20
21    -- assign amount of rows i need to loop through and check
22    SELECT COUNT(*) INTO rows FROM temp;
23
24    -- loop
25    WHILE i < rows DO
26      select users_id into currentHoId from temp ORDER BY users_id LIMIT i, 1;
27    -- check if ho isn't in ranks table add
28      IF (NOT (SELECT EXISTS(SELECT * from ranks WHERE ranks.user_fk = temp.users_id))) THEN
29        -- if user doesn't exit in ranks add
30        INSERT INTO ranks(user_fk, rank) VALUES (currentHoId, null);
31      END IF;
32
33    -- get number of transfers for current HO
34    SELECT count(*) into numberOfTransfers FROM players WHERE players.school_id = (
35         SELECT school_id from schools where users_id = currentHoId AND date_transferred IS NOT NULL
36        );
37
38    -- update and set ranks based on number of transfers for ho
39    UPDATE ranks set rank = getHoRank(numberOfTransfers) where user_fk = currentHoId;
40
41    SET i = i + 1; -- increment
42
43   END WHILE;
44
45  END //
46DELIMITER ;