Back
Home
Up
Next

Updatable views.

    Views are like virtual tables: they host a SELECT statement made over one or more tables but they can be used where a physical table would be accepted. Among their uses we can find:

  • Restrict columns from a single table or several tables: show only the columns fields that are needed for a report or interactive usage.
  • Restrict rows from a single table or several tables: show only the records that are needed for the intended users.
  • Provide an uniform view of data for all users: instead of creating a complex SELECT in each application, the applications SELECT from the view.
  • Provide read-only or read/write access to data without giving users direct access to the underlying tables: this helps manage security and consistency.
  • Verify that the INSERT or UPDATE commands over a view are in sync with the restrictions clauses of the VIEW definition.
  • Allow changing the semantics of INSERT, DELETE and UPDATE operations when applied to the VIEW.

Since a VIEW holds only a SELECT statement, it doesn't use storage space. It will reflect the current data in the underlying tables as seen by the client's transaction. An ORDER BY clause is not allowed in the VIEW definition. The user that creates the VIEW should give appropriate rights to other users and also should ensure that the view has enough privileges on the underlying tables. The syntax is:

CREATE VIEW name [( view_col [, view_col ...])]
AS <select> [WITH CHECK OPTION];

The "view_col" part applies in two cases: to change the field names exposed by the view (otherwise they are taken from the SELECT clause) and to name columns that come from expressions (like two fields concatenated, UDF calls, aggregate functions and math operations) in whose case it's mandatory. The "with check option" part should only be used with updatable views only and it specifies that before accepting an INSERT or UPDATE commands, the engine will verify that the new values meet the same restrictions that define the underlying SELECT for the VIEW. In plain terms, it means that the changed or new record should be visible through the VIEW and not to be discarded by the ON, WHERE and HAVING clauses that may exist in the VIEW definition. Otherwise, the insertion or modification is rejected.

There are some restrictions that apply to VIEWS:

  • They cannot be based on a stored procedure. Even if you can't specify a stored procedure in the FROM clause of the VIEW definition, I found that you can put a selectable stored proc in the WHERE clause but in this case, you can't use the WITH CHECK OPTION clause. 
  • The ORDER BY clause is not accepted; use it when you select from the view.
  • Up to IB5, you cannot create a VIEW that has UNION clauses; embedded SQL is required.
  • There's no ALTER VIEW command; you should DROP the view and create it again if you want to change its definition.
  • An updatable VIEW that includes BEFORE triggers will have a weird behavior: a change in the data will execute the direct specified action (INSERT, UPDATE, DELETE) plus the action specified in the trigger, so you may get messages like PK violations, etc. So, if you want to control write operations to the VIEW through such triggers, please ensure the view is read-only.

There are two kinds of VIEWS: read-only and updatable. The former can be made updatable by defining triggers for the VIEW and not for the underlying tables in most of the cases (there are cases where a VIEW cannot be turned updatable). For a view to be naturally updatable (without the need to write triggers for it), it should be a subset of a single table or another updatable view, fields from the underlying tables that aren't exposed through the view should allow NULL values and the SELECT clause that defines the VIEW is not allowed to include DISTINCT, nested queries, HAVING, aggregate built-in functions (AVG, SUM, MAX, MIN, COUNT), UDFs, procedures, JOIN or UNION and expressions.

Unlike a table, where BEFORE triggers can only change field values before being stored, reject an operation by raising an exception or performing actions over other tables, the BEFORE triggers for a VIEW can effectively change the meaning of an operation. For example, the BEFORE UPDATE trigger can do an INSERT in the underlying tables so the original UPDATE over the VIEW is transformed silently into an insertion in one or more tables or a combination of operations over the underlying tables.

 

This page was last updated on 2001-02-15 20:23:00