0

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.

  • Can you modify the constraint to be implemented with declarative constraints and not triggers? – ypercubeᵀᴹ Mar 25 '14 at 10:21
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.