I have 2 tables (parent and child). On parent, I have a computed column that is sum of ColA in Child table by parent id.
My problem is: how to refresh computed column value when I have a insert/delete on child table? I don't need to process updates since my program doesn't allows it.
Use after insert, update and delete triggers like:
create TRIGGER "AInsert" after insert on child referencing new as new_row
create TRIGGER "AUpdate" after update of ColA, order 1 on child referencing new as new_row
create TRIGGER "ADelete" after delete order 1 on child referencing old as old_row
answered 29 Sep '11, 11:35
Is this really a computed column, i.e. declared with COMPUTE(...)?
AFAIK, those should only be used with constant values and deterministic functions.
I would generally recommend to omit (or "delay") the computation completely by using a view instead of a computed column, say by something like
create view V_ParentWithSum as select parent.*, sum(child.colA) as SummedChild from parent key join child
If this does not need your requirements, I would use a trigger on child to update the parent. But then I would not declare the column as a computed column, as changing computed columns via triggers of other tables seems dangerous (and looks suspicious as well IMHO).