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

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.

The CREATE VIEW statement can be executed by:

• Administrators

• Users with the CREATE VIEW privilege

The creator of a view becomes its owner.

To create a view, a non-admin user also needs at least SELECT access to the underlying table(s) and/or view(s), and the EXECUTE privilege on any selectable stored procedures involved.

To enable insertions, updates and deletions through the view, the creator/owner must also possess the corresponding INSERT, UPDATE and DELETE rights on the underlying object(s).

Granting other users privileges on the view is only possible if the view owner has these privileges on the underlying objects WITH GRANT OPTION. This will always be the case if the view owner is also the owner of the underlying objects.

1. Creating view returning the JOB_CODE and JOB_TITLE columns only for those jobs where MAX_SALARY is less than $15,000.

     |CREATE VIEW ENTRY_LEVEL_JOBS AS
     |SELECT JOB_CODE, JOB_TITLE
     |FROM JOB
     |WHERE MAX_SALARY < 15000;
   

2. Creating a view returning the JOB_CODE and JOB_TITLE columns only for those jobs where MAX_SALARY is less than $15,000. Whenever a new record is inserted or an existing record is updated, the MAX_SALARY < 15000 condition will be checked. If the condition is not true, the insert/update operation will be rejected.

     |CREATE VIEW ENTRY_LEVEL_JOBS AS
     |SELECT JOB_CODE, JOB_TITLE
     |FROM JOB
     |WHERE MAX_SALARY < 15000
     |WITH CHECK OPTION;
   

3. Creating a view with an explicit column list.

      |CREATE VIEW PRICE_WITH_MARKUP (
      |  CODE_PRICE,
      |  COST,
      |  COST_WITH_MARKUP
      |) AS
      |SELECT
      |  CODE_PRICE,
      |  COST,
      |  COST * 1.1
      |FROM PRICE;
   

4. Creating a view with the help of aliases for fields in the SELECT statement (the same result as in Example 3).

     |CREATE VIEW PRICE_WITH_MARKUP AS
     |SELECT
     |  CODE_PRICE,
     |  COST,
     |  COST * 1.1 AS COST_WITH_MARKUP
     |FROM PRICE;
   

5. Creating a read-only view based on two tables and a stored procedure.

     |CREATE VIEW GOODS_PRICE AS
     |SELECT
     |  goods.name AS goodsname,
     |  price.cost AS cost,
     |  b.quantity AS quantity
     |FROM
     |  goods
     |  JOIN price ON goods.code_goods = price.code_goods
     |  LEFT JOIN sp_get_balance(goods.code_goods) b ON 1 = 1;
   

See alsoSection 5.6.2, “ALTER VIEW”, Section 5.6.3, “CREATE OR ALTER VIEW”, Section 5.6.5, “RECREATE VIEW”, Section 5.6.4, “DROP VIEW”

The ALTER VIEW statement can be executed by:

• Administrators

• The owner of the view

• Users with the ALTER ANY VIEW privilege

Altering the view PRICE_WITH_MARKUP

   |ALTER VIEW PRICE_WITH_MARKUP (
   |  CODE_PRICE,
   |  COST,
   |  COST_WITH_MARKUP
   |) AS
   |SELECT
   |  CODE_PRICE,
   |  COST,
   |  COST * 1.15
   |FROM PRICE;

See alsoSection 5.6.1, “CREATE VIEW”, Section 5.6.3, “CREATE OR ALTER VIEW”, Section 5.6.5, “RECREATE VIEW”

Creating the new view PRICE_WITH_MARKUP view or altering it if it already exists

   |CREATE OR ALTER VIEW PRICE_WITH_MARKUP (
   |  CODE_PRICE,
   |  COST,
   |  COST_WITH_MARKUP
   |) AS
   |SELECT
   |  CODE_PRICE,
   |  COST,
   |  COST * 1.15
   |FROM PRICE;

See alsoSection 5.6.1, “CREATE VIEW”, Section 5.6.2, “ALTER VIEW”, Section 5.6.5, “RECREATE VIEW”

The DROP VIEW statement can be executed by:

• Administrators

• The owner of the view

• Users with the DROP ANY VIEW privilege

Deleting the PRICE_WITH_MARKUP view

  |DROP VIEW PRICE_WITH_MARKUP;

See alsoSection 5.6.1, “CREATE VIEW”, Section 5.6.5, “RECREATE VIEW”, Section 5.6.3, “CREATE OR ALTER VIEW”

Creating the new view PRICE_WITH_MARKUP view or recreating it, if it already exists

   |RECREATE VIEW PRICE_WITH_MARKUP (
   |  CODE_PRICE,
   |  COST,
   |  COST_WITH_MARKUP
   |) AS
   |SELECT
   |  CODE_PRICE,
   |  COST,
   |  COST * 1.15
   |FROM PRICE;

See alsoSection 5.6.1, “CREATE VIEW”, Section 5.6.4, “DROP VIEW”, Section 5.6.3, “CREATE OR ALTER VIEW”