MySQL procedure skips latest records

Question

I have the following three tables;

hm_deliverylog
+------------+----------------------------------+------------------------------------------------------------------------------------+---------------------+-----------------+--------------------------+-------------------+----------+---------+
| deliveryid | deliveryfrom                     | deliveryfilename                                                                   | deliverytime        | deliverysubject | deliverybody             | deliveryipaddress | subjnotr | islendi |
+------------+----------------------------------+------------------------------------------------------------------------------------+---------------------+-----------------+--------------------------+-------------------+----------+---------+
|          1 | [email protected]              | C:\Program Files (x86)\hMailServer\Data\{BACBEB1F-3852-4CD0-A963-337CA956879E}.eml | 2022-04-11 12:47:13 | Hmail Test      | TEST MESAJI PLAIN-TEXT
 | NULL              | NULL     |       0 |
+------------+----------------------------------+------------------------------------------------------------------------------------+---------------------+-----------------+--------------------------+-------------------+----------+---------+
1 row in set (0.002 sec)

m_deliverylog_recipients
+---------------------+------------+--------------------------+
| deliveryrecipientid | deliveryid | deliveryrecipientaddress |
+---------------------+------------+--------------------------+
|                   1 |          1 | [email protected]         |
+---------------------+------------+--------------------------+
mlog
+-------+-------+---------------------+-----------+------------------+------------+---------+---------------------+-----------+------------+
| pr_id | delid | gonderen            | gonip     | alici            | konu       | mesaj   | gontar              | olusturma | guncelleme |
+-------+-------+---------------------+-----------+------------------+------------+---------+---------------------+-----------+------------+
|     1 |     1 | [email protected] | 10.0.0.83 | [email protected] | Hmail Test | Testing | 2022-05-13 09:37:16 | NULL      | NULL       |
+-------+-------+---------------------+-----------+------------------+------------+---------+---------------------+-----------+------------+
1 row in set (0.000 sec)

I am trying to merge data from two tables into another table using the following procedure;

CREATE DEFINER=`root`@`localhost` PROCEDURE `mlog_birlestir`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
INSERT INTO mlog (delid,gonderen,gonip,alici,konu,mesaj,gontar)
SELECT hm_deliverylog.deliveryid, hm_deliverylog.deliveryfrom, hm_deliverylog.deliveryipaddress, hm_deliverylog_recipients.deliveryrecipientaddress, hm_deliverylog.deliverysubject, hm_deliverylog.deliverybody, hm_deliverylog.deliverytime
from hm_deliverylog,hm_deliverylog_recipients WHERE hm_deliverylog.deliveryid=hm_deliverylog_recipients.deliveryid;
END

Create a trigger before the insertion event of hm_deliverylog and call the process as follows;

call mlog_birlestir()

With the above steps, when records are created on hm_deliverylog and hmdeliverylog_recipients, it should merge the data and create new records on mlog. But it always skips the first record and follows with a delay of 1 record (sorry my English is not good enough to explain this situation), let me explain as follows;

1-Deleted all records on 3 tables

2- Insert the first record into hm_deliverylog and hm_deliverylog_recipients

3- Expected record on mlog: not created

4- Create a second record on hm_deliverylog and hm_deliverylog_recipients

5- Two records expected on mlog: First record created on mlog, but no second record

6- Created third record on hm_deliverylog and hm_deliverylog_recipients

7-Expected 3 records on mlog: The second record was created on the mlog, but there was no third record

Here's the thing, it always creates one before the latest record on the mlog and skips the latest record.

I tried changing the trigger's event to AFTER INSERT but that didn't solve the problem.

Records are created on the first two tables processed by the program but I have no control over it and I'm looking for a workaround to resolve this issue.

What am I missing here?

Answer 1

Thanks to @Akina, I solved this problem.

My code is ok but I made a logic error, I am creating a trigger that calls a procedure on hm_deliverylog (first table), what I should be doing is creating on hm_deliverylog_recipients (second table) Trigger because the application insert main first inserts the data into the first table and then inserts the recipient data into the second table, so when I set the trigger to the first table it runs but checks the second table, but there are no records in the second table yet, that's why it doesn't create the records table on the third table.

When I create the trigger on the second table it works fine.

Thanks again @Akina for the reminder