Types of Views: Read-only and Update-able
When you update a view, the changes are passed through to the underlying tables from which the view was created only if certain conditions are met. If a view meets these conditions, it is update-able. If it does not meet these conditions, it is read-only, meaning that writes to the view are not passed through to the underlying tables.
- Note: The terms update-able and read-only refer to how you access the data in the underlying tables, not to whether the view definition can be modified. To modify the view definition, you must drop the view and then recreate it.
A view is update-able if all of the following conditions are met:
- It is a subset of a single table or another update-able view.
- All base table columns excluded from the view definition allow
SELECTstatement of the view does not contain sub-queries, a
HAVINGclause, aggregate functions, joined tables, user-defined functions, or stored procedures.
If the view definition does not meet all of these conditions, it is considered read-only.
- Note: Read-only views can be updated by using a combination of user-defined referential constraints, triggers, and unique indexes. For information on how to update read-only views using triggers, see Working with Triggers.
The creator of the view must have the following privileges:
- To create a read-only view, the creator needs
SELECTprivileges for any underlying tables.
- To create an update-able view, the creator needs
ALLprivileges to the underlying tables.
For more information on SQL privileges, see Planning Security.
Examples of Views
The following statement creates an update-able view:
CREATE VIEW EMP_MNGRS (FIRST, LAST, SALARY) AS SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEE WHERE JOB_CODE = 'Mngr';
The next statement uses a nested query to create a view, so the view is read-only:
CREATE VIEW ALL_MNGRS AS SELECT FIRST_NAME, LAST_NAME, JOB_COUNTRY FROM EMPLOYEE WHERE JOB_COUNTRY IN (SELECT JOB_COUNTRY FROM JOB WHERE JOB_TITLE = 'manager');
The next statement creates a view that joins two tables, and so it is also read-only:
CREATE VIEW PHONE_LIST AS SELECT EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, LOCATION, PHONE_NO FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO.