Considerations regarding inserting into, and updating, computed columns include the following:
Direct inserts and updates An INSERT or UPDATE statement can specify a value for a computed column; however, the value is ignored. The server computes the value for computed columns based on the COMPUTE specification, and uses the computed value in place of the value specified in the INSERT or UPDATE statement.
Column dependencies It is strongly recommended that you not use triggers to set the value of a column referenced in the definition of a computed column (for example, to change a NULL value to a not-NULL value), as this can result in the value of the computed column not reflecting its intended computation.
Listing column names You must always explicitly specify column names in INSERT statements on tables with computed columns.
Triggers If you define triggers on a computed column, any INSERT or UPDATE statement that affects the column fires the triggers.
The LOAD TABLE statement permits the optional computation of computed columns. Suppressing computation during a load operation may make performing complex unload/reload sequences faster. It can also be useful when the value of a computed column must stay constant, even though the COMPUTE expression refers a non-deterministic value, such as CURRENT TIMESTAMP.
Avoid changing the values of dependent columns in triggers as it may cause the value of the computed column to be inconsistent with the column definition.
If a computed column x depends on a column y that is declared not-NULL, then an attempt to set y to NULL will be rejected with an error before triggers fire.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|