MySQL Trigger updating second table based on History and computed values

**The Goal:** Update an secondary table called **Lottery_Cycle** when an INSERT occours on table **Lottery_History** with some computed values and checking previous data. Let's say a lottery with 25 numbers of which 15 are drawn. **What is an Cycle on this scenario?** On this scenario a cycle consists of recording when all lottery numbers are drawn. When this occurs, one cycle closes and another begins. **Lottery_History structure**

CREATE TABLE IF NOT EXISTS Lottery_History (
	Id SMALLINT(5) UNSIGNED NOT NULL,
	Ball_01 TINYINT(3) UNSIGNED NOT NULL,
	Ball_02 TINYINT(3) UNSIGNED NOT NULL,
	Ball_03 TINYINT(3) UNSIGNED NOT NULL,
	Ball_04 TINYINT(3) UNSIGNED NOT NULL,
	Ball_05 TINYINT(3) UNSIGNED NOT NULL,
	Ball_06 TINYINT(3) UNSIGNED NOT NULL,
	Ball_07 TINYINT(3) UNSIGNED NOT NULL,
	Ball_08 TINYINT(3) UNSIGNED NOT NULL,
	Ball_09 TINYINT(3) UNSIGNED NOT NULL,
	Ball_10 TINYINT(3) UNSIGNED NOT NULL,
	Ball_11 TINYINT(3) UNSIGNED NOT NULL,
	Ball_12 TINYINT(3) UNSIGNED NOT NULL,
	Ball_13 TINYINT(3) UNSIGNED NOT NULL,
	Ball_14 TINYINT(3) UNSIGNED NOT NULL,
	Ball_15 TINYINT(3) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

*Id* = The contest number *Ball_01* ~ Ball_15 = Each of the balls drawn in the contest **Lottery_Cycle**

CREATE TABLE IF NOT EXISTS Lottery_Cycle (
	Id SMALLINT(5) UNSIGNED NOT NULL,
	Ball_Missing_01 TINYINT(1) UNSIGNED DEFAULT NULL,
    Ball_Missing_02 TINYINT(1) UNSIGNED DEFAULT NULL,
    Ball_Missing_03 TINYINT(1) UNSIGNED DEFAULT NULL,
	Ball_Missing_04 TINYINT(1) UNSIGNED DEFAULT NULL,
	Ball_Missing_05 TINYINT(1) UNSIGNED DEFAULT NULL,
	Ball_Missing_06 TINYINT(1) UNSIGNED DEFAULT NULL,
	Ball_Missing_07 TINYINT(1) UNSIGNED DEFAULT NULL,
	Ball_Missing_08 TINYINT(1) UNSIGNED DEFAULT NULL,
	Ball_Missing_09 TINYINT(1) UNSIGNED DEFAULT NULL,
	Ball_Missing_10 TINYINT(1) UNSIGNED DEFAULT NULL,
	Missing TINYINT(1) UNSIGNED AS (
			(Ball_Missing_01 IS NOT NULL) +
			(Ball_Missing_02 IS NOT NULL) +
			(Ball_Missing_03 IS NOT NULL) +
			(Ball_Missing_04 IS NOT NULL) +
			(Ball_Missing_05 IS NOT NULL) +
			(Ball_Missing_06 IS NOT NULL) +
			(Ball_Missing_07 IS NOT NULL) +
			(Ball_Missing_08 IS NOT NULL) +
			(Ball_Missing_09 IS NOT NULL) +
			(Ball_Missing_10 IS NOT NULL)
		),
	Contests TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
	Cycle SMALLINT(1) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

*Id* = The contest number *Ball_Missing_01* ~ *Ball_Missing_10* = Displays balls not yet drawn in the cycle *Missing* = It is a computed column that reports the total number of balls not drawn *Contests* = Informs the number of contests in the current cycle *Cycle* = Displays the cycle number **Example use case and desired behavior** Lottery Competition 1, the following numbers were drawn and entered in the table **Lottery_History**:

01 05 06 08 09 10 13 15 16 17 18 19 20 22 25

Therefore, the Cycle Table must have the following values:

Id = 1
Ball_Missing_01 = 02
Ball_Missing_02 = 03
Ball_Missing_03 = 04
Ball_Missing_04 = 07
Ball_Missing_05 = 11
Ball_Missing_06 = 12
Ball_Missing_07 = 14
Ball_Missing_08 = 21
Ball_Missing_09 = 23
Ball_Missing_10 = 24
Quantity = Automatically computed (will be 10)
Contest = 1
Cycle = 1

Lottery Competition 2, the following numbers were drawn and entered in the table **Lottery_History**:

03 04 05 09 10 11 13 15 16 17 19 20 21 24 25

Therefore, the Cycle Table must have the following values:

Id = 2
Ball_Missing_01 = 02
Ball_Missing_02 = 07
Ball_Missing_03 = 12
Ball_Missing_04 = 14
Ball_Missing_05 = 23
Ball_Missing_06 = NULL
Ball_Missing_07 = NULL
Ball_Missing_08 = NULL
Ball_Missing_09 = NULL
Ball_Missing_10 = NULL
Quantity = Automatically computed (will be 5)
Contest = 2
Cycle = 1

Note that the Ball_Missing_01 ~ Ball_Missing_05 field refers to the 5 numbers out of the 25 possible that have not yet been drawn in either contest 1 and 2. Full drawn numbers from contest 1 and 2:

01 03 04 05 06 08 09 10 11 13 15 16 17 18 19 20 21 22 24 25

The "Contest" field informs that it is the second competition in Cycle 1 with missing numbers (not yet drawn). Lottery Competition 3, the following numbers were drawn and entered in the table **Lottery_History**:

02 03 04 05 06 08 09 10 11 12 14 19 20 23 24

Therefore, the Cycle Table must have the following values:

Id = 3
Ball_Missing_01 = 07
Ball_Missing_02 = NULL
Ball_Missing_03 = NULL
Ball_Missing_04 = NULL
Ball_Missing_05 = NULL
Ball_Missing_06 = NULL
Ball_Missing_07 = NULL
Ball_Missing_08 = NULL
Ball_Missing_09 = NULL
Ball_Missing_10 = NULL
Quantity = Automatically computed (will be 1)
Contest = 3
Cycle = 1

Lottery Competition 4, the following numbers were drawn and entered in the table **Lottery_History**:

02 06 07 08 09 10 11 12 16 19 20 22 23 24 25

Therefore, the Cycle Table must have the following values:

Id = 4
Ball_Missing_01 = NULL
Ball_Missing_02 = NULL
Ball_Missing_03 = NULL
Ball_Missing_04 = NULL
Ball_Missing_05 = NULL
Ball_Missing_06 = NULL
Ball_Missing_07 = NULL
Ball_Missing_08 = NULL
Ball_Missing_09 = NULL
Ball_Missing_10 = NULL
Quantity = Automatically computed (will be zero)
Contest = 4
Cycle = 1

At this point, a Cycle is completed because in the 4 contests informed, all numbers from 1 to 25 were drawn, and a new cycle begins. Lottery Competition 5, the following numbers were drawn and entered in the table **Lottery_History**:

01 02 04 05 07 08 09 10 11 12 14 16 17 24 25

Therefore, the Cycle Table must have the following values:

Id = 5
Ball_Missing_01 = 03
Ball_Missing_02 = 06
Ball_Missing_03 = 13
Ball_Missing_04 = 15
Ball_Missing_05 = 18
Ball_Missing_06 = 19
Ball_Missing_07 = 20
Ball_Missing_08 = 21
Ball_Missing_09 = 22
Ball_Missing_10 = 23
Quantity = Automatically computed (will be ten)
Contest = 1
Cycle = 2

Note that now the **Cycle** field receives the value of 2 because it is a new cycle and **Contest** field is 1 because its the first Contest from this new cycle. And this process must continue whenever a new insertion occurs in the table **Lottery_History**. Triggers I have made 1 - For determine the value to **Missing** field:

DROP TRIGGER IF EXISTS trg_BI_Lottery_Cycle_Missing;
DELIMITER $$
CREATE TRIGGER trg_BI_Lottery_Cycle
BEFORE INSERT ON `Lottery_Cycle
FOR EACH ROW
BEGIN

	IF NEW.Missing > 0 THEN
		SET NEW.Missing = (SELECT MAX(Contests) + 1 FROM Lottery_Cycle);
	ELSE
		SET NEW.Missing = 1;
	END IF;

END
$$
DELIMITER ;

2 - For determine the value to **Cycle** field:

DROP TRIGGER IF EXISTS trg_BI_Lottery_Cycle_Cycle;
DELIMITER $$
CREATE TRIGGER trg_BI_Lottery_Cycle_Cycle
BEFORE INSERT ON Lottery_Cycle
FOR EACH ROW
BEGIN

	SET NEW.Cicle = (SELECT MAX(Cycle IS NOT NULL) + 1 FROM Lottery_Cycle);

END
$$
DELIMITER ;

3 - Where I'm stuck!

DROP TRIGGER IF EXISTS trg_AI_Lottery_Cycle_Update_Lottery_Cycle;
CREATE TRIGGER trg_AI_Lottery_Cycle_Update_Lottery_Cycle
AFTER INSERT ON Lottery_History
FOR EACH ROW
BEGIN
	DECLARE Contest_Id TINYINT DEFAULT 1;
	DECLARE Current_Cycle TINYINT DEFAULT 1;
	DECLARE Contests_Total TINYINT DEFAULT 1; ????
	DECLARE Ball_Missing_Insert_01 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_02 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_03 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_04 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_05 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_06 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_07 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_08 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_09 TINYINT DEFAULT NULL;
	DECLARE Ball_Missing_Insert_10 TINYINT DEFAULT NULL;

	-- Check if table is empty
	IF (SELECT COUNT(*) AS Contests
			FROM
				Lottery_Cycle
			WHERE
				Contests IS NULL
		) = 0 THEN SET Contest_Id = 1;
	ELSE
		SET Contest_Id = (SELECT MAX(Id) FROM Lottery_History);
    END IF;

	-- Get currently Cycle
	SET Current_Cycle = (SELECT MAX(Cycle) FROM Lottery_Cycle);

    -- Create an temporary table for simulate an array
    CREATE TEMPORARY TABLE IF NOT EXISTS Lottery_Cycle_Insert_Temp (
        Number TINYINT(1) UNSIGNED NOT NULL
    );

    -- Insert all numbers (1 to 25) into the temporary table
    INSERT INTO Lottery_Cycle_Insert_Temp (Number)
    VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
           (11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
           (21), (22), (23), (24), (25);

    -- Removing the 15 drawn balls from the temporary table
    DELETE FROM Lottery_Cycle_Insert_Temp
    WHERE Number IN (NEW.Ball_01, NEW.Ball_02, NEW.Ball_03, NEW.Ball_04, NEW.Ball_05,
                     NEW.Ball_06, NEW.Ball_07, NEW.Ball_08, NEW.Ball_09, NEW.Ball_10,
                     NEW.Ball_11, NEW.Ball_12, NEW.Ball_13, NEW.Ball_14, NEW.Ball_15);

	-- Removing numbers already drawn in previous contests of the same
	-- Cycle of the Lottery_Cycle table
	DELETE FROM Lottery_Cycle_Insert_Temp
	WHERE Number IN (
	SELECT DISTINCT
		IF(LC.Ball_Missing_01 IS NULL, NULL, LC.Ball_Missing_01),
		IF(LC.Ball_Missing_02 IS NULL, NULL, LC.Ball_Missing_02),
		IF(LC.Ball_Missing_03 IS NULL, NULL, LC.Ball_Missing_03),
		IF(LC.Ball_Missing_04 IS NULL, NULL, LC.Ball_Missing_04),
		IF(LC.Ball_Missing_05 IS NULL, NULL, LC.Ball_Missing_05),
		IF(LC.Ball_Missing_06 IS NULL, NULL, LC.Ball_Missing_06),
		IF(LC.Ball_Missing_07 IS NULL, NULL, LC.Ball_Missing_07),
		IF(LC.Ball_Missing_08 IS NULL, NULL, LC.Ball_Missing_08),
		IF(LC.Ball_Missing_09 IS NULL, NULL, LC.Ball_Missing_09),
		IF(LC.Ball_Missing_10 IS NULL, NULL, LC.Ball_Missing_10)
	FROM Lottery_Cycle LC
	WHERE LC.Cycle = Current_Cycle
	);

	-- Storing remaining numbers in variables for Insert
	SET Ball_Missing_Insert_01 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_01;

	SET Ball_Missing_Insert_02 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_02;

	SET Ball_Missing_Insert_03 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_03;

	SET Ball_Missing_Insert_04 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_04;

	SET Ball_Missing_Insert_05 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_05;

	SET Ball_Missing_Insert_06 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_06;

	SET Ball_Missing_Insert_07 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_07;

	SET Ball_Missing_Insert_08 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_08;

	SET Ball_Missing_Insert_09 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_09;

	SET Ball_Missing_Insert_10 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number DESC LIMIT 1);
	DELETE FROM Lottery_Cycle_Insert_Temp WHERE Number = Ball_Missing_Insert_10;

	-- Configuring the value of the Current Cycle Competitions field
    SET Contests_Total = (SELECT MAX(Contests IS NOT NULL) +1 FROM Lottery_Cycle);

	-- If new Cycle Begins
	IF Ball_Missing_Insert_01 IS NULL AND
		Ball_Missing_Insert_02 IS NULL AND
		Ball_Missing_Insert_03 IS NULL AND
		Ball_Missing_Insert_04 IS NULL AND
		Ball_Missing_Insert_05 IS NULL AND
		Ball_Missing_Insert_06 IS NULL AND
		Ball_Missing_Insert_07 IS NULL AND
		Ball_Missing_Insert_08 IS NULL AND
		Ball_Missing_Insert_09 IS NULL AND
		Ball_Missing_Insert_10 IS NULL THEN
			SET Current_Cycle = Current_Cycle + 1;
	END IF;

	-- Insert remaining missing numbers into Lottery_Cycle
	INSERT INTO Lottery_Cycle (
		Id,
		Ball_Missing_01,
		Ball_Missing_02,
		Ball_Missing_03,
		Ball_Missing_04,
		Ball_Missing_05,
		Ball_Missing_06,
		Ball_Missing_07,
		Ball_Missing_08,
		Ball_Missing_09,
		Ball_Missing_10,
		Contests,
		Cycle
	) VALUES (
		Contest_Id,
		Ball_Missing_Insert_01,
		Ball_Missing_Insert_02,
		Ball_Missing_Insert_03,
		Ball_Missing_Insert_04,
		Ball_Missing_Insert_05,
		Ball_Missing_Insert_06,
		Ball_Missing_Insert_07,
		Ball_Missing_Insert_08,
		Ball_Missing_Insert_09,
		Ball_Missing_Insert_10,
		Contests_Total,
		Current_Cycle
	);

	-- Drop the temporary table
	DROP TEMPORARY TABLE IF EXISTS Lottery_Cycle_Insert_Temp;
END
$$
DELIMITER ;

I'm stucking on trial and error, sometimes got: (conn=17034) Column 'Cycle' cannot be null Other times got: (conn=17049) Operand should contain 1 column(s) And other erros. Someone can point me what I'm doing wrong? Thanks in advice!!!