validation using the same table

Question

My tables:

parent (id number)
 child (id number, parent_id number, allocation number)

So for every parent, there is set of three fixed records in child table. And, I need to put a validation block to restrict users from updating the allocation for any of the three children greater than 100% in child table (for any given parent_id).

I have added a trigger to check the allocation sum for a given parent_id, if it is greater than 100% then raise exception. However this leads to a mutating issue. Any design improvements/suggestions to overcome this issue or any alternatives to achieve this.

Sample data

PS: The app intercepts the oracle exception, and translates it into red bar error message in the application; hence RAISE_APPLICATION_ERROR is must for error message display.

Edit 1:
The issue here is app layer, which allows edit on list screen. Multiple records could be updated in a single go hence the trigger on child table would get fired multiple times. In every trigger instance we also need to check the other children's allocation, which leads to mutating issue.

Answer 1

You cannot perform DML in triggers on the table for which the trigger is defined, you can see the result as mutating trigger error.

One alternative is to introduce another table, say sum_parent where you can maintain sum of allocation numbers for each parent id and inside the child table's trigger you can fetch data from this table(sum_parent), put the check in place and then update this table(sum_parent) with the new sum inside the trigger itself.

Answer 2

A solution using only DDL. No triggers:

CREATE TABLE child 
( id number NOT NULL, 
  parent_id  number NOT NULL,
  allocation number NOT NULL,

We add a few columns we'll need:

  child_no      number NOT NULL,        -- a number from 1 to 3 
  prev_child_no number NOT NULL,        -- previous child number

  running_total      number NOT NULL,   -- running total of allocations
  prev_running_total number NOT NULL,   -- previous running total

Besides the constraints you have

          -- I guess you already have these two constraints        
  PRIMARY KEY (id), 
  FOREIGN KEY (parent_id)
    REFERENCES parent (id), 
  CHECK ( allocation >= 0 ),

We add some more, for the child-to-previous-child relationships:

          -- this links a child to the previous one
  FOREIGN KEY (parent_id, prev_child_no, prev_running_total)
    REFERENCES child (parent_id, child_no, running_total),

          -- these 2 constraints enforce that there are
  UNIQUE (parent_id, child_no),         -- maximum 3 children
  CHECK (child_no IN (1,2,3)),          -- per parent

          -- and this exactly 3 children per parent
  CHECK ( child_no = 1 AND prev_child_no = 3  
       OR child_no > 1 AND prev_child_no + 1 = child_no ), 

and for the running totals:

          -- this enforces that the running total is correct
  CHECK ( child_no = 1 AND running_total = allocation
       OR child_no > 1 AND running_total = allocation + prev_running_total ),

        -- enforce that it never exceeds 100
  CHECK ( running_total <= 100 )
) ;