4mxPS2Jb

1DELIMITER //
2CREATE PROCEDURE MoveTeamsProc ()
3	BEGIN
4		DECLARE OldRowsCount001, OldRowsCount002, NewRowsCount001, NewRowsCount002 INT DEFAULT 0;
5        DROP TEMPORARY TABLE IF EXISTS Temp002;
6        DROP TEMPORARY TABLE IF EXISTS Temp001;
7        CREATE TEMPORARY TABLE IF NOT EXISTS Temp002 AS (SELECT * FROM ClubStatus WHERE DivisionID = "002" ORDER BY TotalPoints DESC LIMIT 1);
8        CREATE TEMPORARY TABLE IF NOT EXISTS Temp001 AS (SELECT * FROM ClubStatus WHERE DivisionID = "001" ORDER BY TotalPoints LIMIT 1);
9		START TRANSACTION;
10		SET OldRowsCount001 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "001");
11        SET OldRowsCount002 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "002");
12		UPDATE Club
13			SET DivisionID = "001" 
14            WHERE Club.ClubID IN (SELECT ClubID FROM Temp002);
15		SET NewRowsCount001 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "001");
16		UPDATE Club
17			SET DivisionID = "002"
18            WHERE ClubID IN (SELECT ClubID FROM Temp001);
19		SET NewRowsCount002 = (SELECT Count(ClubID) FROM ClubStatus WHERE DivisionID = "002");
20        DELETE FROM Game;
21        IF (OldRowsCount001 = NewRowsCount001-1) AND (OldRowsCount002 = NewRowsCount002) AND ((SELECT Count(*) FROM Game)=0)
22			THEN COMMIT;
23		ELSE ROLLBACK;
24		END IF;
25	END; //
26DELIMITER ;
27
28DROP EVENT IF EXISTS NewSeason;
29
30DELIMITER //
31CREATE EVENT NewSeason
32ON SCHEDULE EVERY 2 MINUTE
33STARTS '2018-06-01 00:00:00'
34DO 
35	BEGIN 
36	CALL MoveTeamsProc ();
37    END; //
38DELIMITER ;