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.

asked 29 Sep '11, 10:22

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%

edited 29 Sep '11, 14:33

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665


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

permanent link

answered 29 Sep '11, 11:35

Martin's gravatar image

Martin
8.6k116151237
accept rate: 14%

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).

permanent link

answered 29 Sep '11, 10:35

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 29 Sep '11, 10:37

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×60
×6
×3

question asked: 29 Sep '11, 10:22

question was seen: 2,287 times

last updated: 30 Sep '11, 09:34