ASA SQL User's Guide
  Working with Database Objects
    Working with tables

Working with computed columns

A computed column is a column whose values are obtained from other columns. You cannot INSERT or UPDATE values in computed columns. However, any update that attempts to modify the computed column fires any triggers associated with the column.

Computed columns are declared in the CREATE TABLE or ALTER TABLE statement. Computed columns are created automatically when you create an index on a function.

Creating tables with computed columns 

The following CREATE TABLE statement is used to create the product table in the Java sample tables:

CREATE TABLE product
(
  id    INTEGER NOT NULL,
  JProd asademo.Product NOT NULL,
  name CHAR(15) COMPUTE ( JProd>>name ),
  PRIMARY KEY ("id")
)

Adding computed columns to tables 

The following statement adds a computed column named inventory_value to the product table:

ALTER TABLE product
ADD inventory_value INTEGER
  COMPUTE ( JProd.quantity * JProd.unit_price )

Modifying computed column expressions 

You can change the expression used in a computed column with the ALTER TABLE statement. The following statement changes the expression that a computed column is based on.

ALTER TABLE table_name
ALTER column-name SET COMPUTE ( expression )

The column is recalculated when this statement is executed. If the new expression is invalid, the ALTER TABLE statement fails.

The following statement stops a column from being a computed column.

ALTER TABLE table_name
ALTER column-name DROP COMPUTE

Existing values in the column are not changed when this statement is executed, but they are no longer updated automatically.

Inserting and updating computed columns
When computed columns are recalculated