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?
Whenever 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.
big_table
gets the first INSERT for the day, and I can't tolerate more than a few seconds where my application is in the illegal state.VIEW
?