I’ve got a table that stores events.
However, the way the system has been designed, events are usually recorded in batches. What I mean by this is that a set of events (10 or so) are usually recorded together, rather than just single events.
We can assume that: there is a column called “batch_no” in the events table, so we know which events belong to which batch no.
The question: What I am trying to do is execute a trigger function, every time a batch of events have finished loading to the table. However, the problem is I can’t think of how the trigger will know this, and not just call the function for every row.
The solutions I’ve been thinking about involves something like: (a) define a trigger for each row; (b) on condition that calculate count(select * from events, where NEW.batchNO = events.batchNO); delay some time; calculate again the same count, and if they are equal, we know the batch has finished loading, and we call the trigger.
Although, clearly the solution above sounds complicated? Is there a more better or simpler solution? (Or if not, any help for how I could implement what I described?)