Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you modify the constraint to be implemented with declarative constraints and not triggers? –  ypercube Mar 25 at 10:21

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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