Postgresql – How to ensure a trigger is fired after variable number of inserts

postgresqltransactiontrigger

I have a 1:N relationship between two tables – let's call them "foo" (the parent table) and "bar". I have some triggers on both "foo" and "bar" tables. I insert data in a single transaction, first to "foo" and then to "bar". I need to check whether percentages saved in "bar" sum up to 100% ("check_percentage" trigger) after execution of all the "check_bar" triggers (they check whether "bar" meets some criteria and discards it, including the percentage, if not) – there is one per "bar" table.

First I though of "check_percentage" as a trigger on "foo", but it is not a constraint trigger, so DEFERRABLE INITIALLY DEFERRED shouldn't work according to the manual, and I didn't find any other way how to postpone its execution. The only solution I already found was to trigger "check_percentage" on "bar" instead, after "check_bar". If I check the percentage after each insert and "check_bar" execution, I can easily find cases where there are more than 100%, but for less than 100% (this is more likely to cause the problem), I would need to ensure that this instance of "bar" is not the last. I can theoretically get this information, but it would require a lot of extra logic. Triggering "check_percentage" in the end of the transaction would be ideal, but I don't know how to achieve it.

Best Answer

You could implement all your inserts on those tables via stored procedures. That would allow you to execute your check_percentage process at the end of said stored procedure. It would also allow you to impose additional conditions on said execution.