FirebirdSQL logo
 INDEXTRIGGER 

Updatable Views

A view can be updatable or read-only.If a view is updatable, the data retrieved when this view is called can be changed by the DML statements INSERT, UPDATE, DELETE, UPDATE OR INSERT or MERGE.Changes made in an updatable view are applied to the underlying table(s).

A read-only view can be made updatable with the use of triggers.Once triggers have been defined on a view, changes posted to it will never be written automatically to the underlying table, even if the view was updatable to begin with.It is the responsibility of the programmer to ensure that the triggers update (or delete from, or insert into) the base tables as needed.

A view will be automatically updatable if all the following conditions are met:

  • the SELECT statement queries only one table or one updatable view

  • the SELECT statement does not call any stored procedures

  • each base table (or base view) column not present in the view definition meets one of the following conditions:

    • it is nullable

    • it has a non-NULL default value

    • it has a trigger that supplies a permitted value

  • the SELECT statement contains no fields derived from subqueries or other expressions

  • the SELECT statement does not contain fields defined through aggregate functions (MIN, MAX, AVG, SUM, COUNT, LIST, etc.), statistical functions (CORR, COVAR_POP, COVAR_SAMP, etc.), linear regression functions (REGR_AVGX, REGR_AVGY, etc.) or any type of window function

  • the SELECT statement contains no ORDER BY, GROUP BY or HAVING clause

  • the SELECT statement does not include the keyword DISTINCT or row-restrictive keywords such as ROWS, FIRST, SKIP, OFFSET or FETCH

Note
The RETURNING clause and updatable views

The RETURNING clause of a DML statement used on a view made updatable using triggers may not always report the correct values.For example, values of identity column, computed columns, default values, or other expressions performed by the trigger will not be automatically reflected in the RETURNING columns.

To report the right values in RETURNING, the trigger will need to explicitly assign those values to the columns of the NEW record.

WITH CHECK OPTION

The optional WITH CHECK OPTION clause requires an updatable view to check whether new or updated data meet the condition specified in the WHERE clause of the SELECT statement.Every attempt to insert a new record or to update an existing one is checked whether the new or updated record would meet the WHERE criteria.If they fail the check, the operation is not performed and an error is raised.

WITH CHECK OPTION can be specified only in a CREATE VIEW statement in which a WHERE clause is present to restrict the output of the main SELECT statement.An error message is returned otherwise.

Important
Please note:

If WITH CHECK OPTION is used, the engine checks the input against the WHERE clause before passing anything to the base relation.Therefore, if the check on the input fails, any default clauses or triggers on the base relation that might have been designed to correct the input will never come into action.

Furthermore, view fields omitted from the INSERT statement are passed as NULLs to the base relation, regardless of their presence or absence in the WHERE clause.As a result, base table defaults defined on such fields will not be applied.Triggers, on the other hand, will fire and work as expected.

For views that do not have WITH CHECK OPTION, fields omitted from the INSERT statement are not passed to the base relation at all, so any defaults will be applied.