I have a table
big_table with a large number of daily INSERTs, and this table has a column
interesting_val. For the first row, and first row only, which is inserted after 00:00 UTC on each date, I want to SELECT
interesting_val and INSERT to another table
daily_interesting_val. Is this behavior something I can achieve with a combination of procedures and triggers?
big_table has at least one row created after 00:00 UTC, but
daily_interesting_val does not have a corresponding row with the new
interesting_val, my application is in an illegal state, and I want to make this time window as small as possible.
One solution I can imagine, but don't if is possible in MySQL:
Create a scheduled procedure that creates a trigger at 00:00 each day. This trigger calls a procedure which (1) checks existence of row, (2) if not exists, insert row, (3) if exists, delete trigger.