Triggers in DB to be executed after a “set” of insert statements

Question

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?)

Answer 1

You can pass parameters to a trigger function but only in the CREATE TRIGGER statement, which helps to use the same trigger function for multiple triggers, but does not help with your situation.

You need the trigger to fire on a condition that is not known at the time of trigger creation. I see basically three possibilities:

1) Statement-level trigger

Using the FOR EACH STATEMENT clause. The manual:

A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. For example, a DELETE that affects 10 rows will cause any ON DELETE triggers on the target relation to be called 10 separate times, once for each deleted row. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies (in particular, an operation that modifies zero rows will still result in the execution of any applicable FOR EACH STATEMENT triggers).

Only applicable if you insert all your batches with a single INSERT command (multiple rows), but not more than one batch at a time.

2) WHEN condition for a row-level trigger.

Using the FOR EACH ROW clause plus a WHEN condition. You need version 9.0+ for that.
If you can tell from a single inserted row, which is the last one of a batch then your trigger definition could look like this:

CREATE TRIGGER insert_after_batch
AFTER INSERT ON tbl
FOR EACH ROW
WHEN (NEW.batch_last)  -- any expression identifying the last
EXECUTE PROCEDURE trg_tbl_insert_after_batch();

This assumes a column batch_last boolean in your table, where you flag the last row of a batch. Any expression based on column values is possible.

This way the trigger only fires for the last row of each batch. Make it an AFTER trigger, so all rows are already visible in the table and you can query them together for whatever you want to do in your trigger. Probably the way to go.

3) Conditional code inside your trigger

That's basically the fallback for versions before 9.0 without the WHEN clause. Do the same check inside the trigger before executing the payload. More expensive than 2).