|
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 what?! I don't understand! I know how to work with triggers, but I need a way to refresh computed column values when rows are inserted/deleted on another table!
(29 Sep '11, 20:52)
Zote
Replies hidden
I just wanted to give you a starter on how to work with a trigger to solve your problem. I didn't thought, that you were using the term computed column in its technical way, in that case Volkers answer is more appropriate.
(30 Sep '11, 03:02)
Martin
As stated in my answer, I would not recommend to do so if this column is really "computed". How is the column declared - do you use an UDF to sum up the childs?
(30 Sep '11, 06:59)
Volker Barth
1
In the help, in section SQL Anywhere Server - SQL Usage » Database objects » Computed columns » Recalculating computed columns, it states: Computed columns are not recalculated under the following circumstances: The computed column is queried. The computed column depends on the values of other rows (using a subquery or user-defined function), and these rows are changed.
(30 Sep '11, 09:34)
Glenn Paulley
|
|
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). |