MySQL Trigger updating second table based on History and computed values

Question

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:

[23000][1048] (conn=17034) Column 'Cycle' cannot be null

Other times got:

[21000][1241] (conn=17049) Operand should contain 1 column(s)

And other erros.

Someone can point me what I'm doing wrong?

Thanks in advice!!!

• nbk: you have two before insert trigger tat make not much sense, combine them, also debugging is simple define some user-defined variables like @a, @b and so on and check with a select which variables where filled to get the perpetrator
• Akina: What are PKs in your tables? Anycase - normalize your structure.
• Wisdom: Hey @nbk, thanks for your help, after I removed triggers one and two as you advised, I focused on the third trigger and finally managed to get the desired result, I will share it in a new answer. PS.: This is a hobby of cloning statistics websites and I only continue when I get home, sorry for the delay in responding.
• Wisdom: Hey @Akina, thanks for your help, I believe the PRIMARY keys relevant to the case are the Id field in both tables created with the command: ALTER TABLE Lottery_History ADD PRIMARY KEY (Id) USING BTREE, ALTER TABLE Lottery_Cycle ADD PRIMARY KEY (Id) USING BTREE, I managed to obtain the desired result and I will share it in a new answer below, as well as the link to the website from which I was inspired. What do you suggest for normalization?
• Wisdom: @Akina about the structure, I created this way with a focus on reducing data traffic, since NULL takes up less bandwidth (thinking about optimization for the website to load quickly and space on database) than using zero or another value, and with the inner join I keep distinct data without duplication as it should be in relational databases.

Answer 1

The final solution I got using @nbk adivice is:

1. Remove trigger trg_BI_Lottery_Cycle_Missing
2. Remove trigger trg_BI_Lottery_Cycle_Cycle
3. Refactor the trigger trg_AI_Lottery_Cycle_Update_Lottery_Cycle

Trigger trg_AI_Lottery_History_Update_Lottery_Cycle Final:

DROP TRIGGER IF EXISTS `trg_AI_Lottery_History_Update_Lottery_Cycle`;
CREATE TRIGGER trg_AI_Lottery_History_Update_Lottery_Cycle
AFTER INSERT ON Lottery_History
FOR EACH ROW
BEGIN
 DECLARE Contest_Id SMALLINT UNSIGNED DEFAULT 1;
 DECLARE Current_Cycle SMALLINT UNSIGNED DEFAULT 1;
 DECLARE Contests_Total TINYINT UNSIGNED DEFAULT 1;
 DECLARE Ball_Missing_Insert_01 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_02 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_03 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_04 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_05 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_06 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_07 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_08 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_09 TINYINT UNSIGNED DEFAULT NULL;
 DECLARE Ball_Missing_Insert_10 TINYINT UNSIGNED DEFAULT NULL;
 -- Get the current Contest
 IF (SELECT 1 AS Contest
 FROM
 Lottery_Cycle LIMIT 1
 ) IS NOT NULL THEN SET Contest_Id = (SELECT MAX(Id) FROM Lottery_History);
 ELSE
 SET Contest_Id = 1;
 END IF;
 -- Get currently Cycle
 IF (SELECT 1 AS Cycle
 FROM
 Lottery_Cycle LIMIT 1
 ) > 0 THEN SET Current_Cycle = (SELECT MAX(Cycle) FROM Lottery_Cycle);
 ELSE
 SET Current_Cycle = 1;
 END IF;
 -- Create an temporary table for simulate an array
 CREATE TEMPORARY TABLE IF NOT EXISTS Lottery_Cycle_Insert_Temp (
 Number TINYINT(1) UNSIGNED NOT NULL
 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
 -- 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);
 -- Keep only not draw numbers in current cycle
 IF Contest_Id > 1 AND
 (SELECT
 1
 FROM
 `Lottery_Cycle`
 WHERE
 Id = NEW.Id - 1
 AND
 Quantity = 0
 ) THEN
 SET Current_Cycle = Current_Cycle + 1;
 SET Contests_Total = 1;
 ELSEIF Contest_Id > 1 THEN
 DELETE FROM
 Lotofacil_Cycle_Array_Temp
 WHERE
 Number NOT IN (
 SELECT DISTINCT
 Ball_Missing_01 AS Number
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_01 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_02
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_02 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_03
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_03 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_04
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_04 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_05
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_05 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_06
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_06 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_07
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_07 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_08
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_08 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_09
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_09 IS NOT NULL
 UNION
 SELECT DISTINCT
 Ball_Missing_10
 FROM Lottery_Cycle
 WHERE Id = Contest_Id - 1 AND Ball_Missing_10 IS NOT NULL
 );
 SET Contests_Total = (SELECT MAX(Contest) + 1 FROM Lottery_Cycle WHERE Cycle = Current_Cycle);
 END IF;
 -- 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);
 -- Storing remaining numbers in variables for Insert
 SET Ball_Missing_Insert_01 = (SELECT Number FROM Lottery_Cycle_Insert_Temp ORDER BY Number 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 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 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 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 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 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 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 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 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 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);
 -- 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 ;

Indexes for this scenario:

ALTER TABLE `Lottery_History` ADD PRIMARY KEY (`Id`) USING BTREE,
ALTER TABLE `Lottery_Cycle` ADD PRIMARY KEY (`Id`) USING BTREE,

Website that inspired me:

https://www.mazusoft.com.br/lotofacil/tabela-ciclos.php

Select that I will use to create the Query View:

 SELECT
 *
 FROM
 Lottery_History LH
 INNER JOIN
 Lottery_Cycle LC
 ON
 LH.Id = LC.Id
 FORCE INDEX (LH.PRIMARY, LC.PRIMARY)

I'm open to suggestions on how to improve this code. Full insert is taking less than 1 minute for more than 3,000 records (There are several other triggers for several other statistics) and no more than one insert (single insert take milliseconds) will occur between Monday and Saturday.

Thanks for all help!!!