Creating a View for update
An updatable view is one that enables privileged users to insert, update, and delete information in the view’s base table. To be updatable, a view must meet the following conditions:
- It derives its columns from a single table or updatable view.
- It does not define a self-join of the base table.
- It does not reference columns derived from arithmetic expressions.
- The view’s
SELECTstatement does not contain:
WHEREclause that uses the
- Nested queries
- Stored procedures
In the following view,
HIGH_CITIES is an updatable view. It selects all cities in the
CITIES table with altitudes greater than or equal to a half mile.
EXEC SQL CREATE VIEW HIGH_CITIES AS SELECT CITY, COUNTRY_NAME, ALTITUDE FROM CITIES WHERE ALTITUDE >= 2640; EXEC SQL COMMIT;
Users who have
UPDATE privileges for this view can change rows in or add new rows to the view’s underlying table,
CITIES. They can even insert or update rows that cannot be displayed by the
HIGH_CITIES view. The following
INSERT adds a record for Santa Cruz, California, altitude 23 feet, to the
EXEC SQL INSERT INTO HIGH_CITIES (CITY, COUNTRY_NAME, ALTITUDE) VALUES ('Santa Cruz', 'United States', '23');
To restrict inserts and updates through a view to only those rows that can be selected by the view, use the
WITH CHECK OPTION in the view definition. For example, the following statement defines the view,
HIGH_CITIES, to use the
WITH CHECK OPTION. Users with
UPDATE privileges will be able to enter rows only for cities with altitudes greater than or equal to a half mile.
EXEC SQL CREATE VIEW HIGH_CITIES AS SELECT CITY, COUNTRY_NAME, ALTITUDE FROM CITIES WHERE ALTITUDE > 2640 WITH CHECK OPTION;