FirebirdSQL logo
 INDEXTRIGGER 

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.

Who Can Create a View?

The CREATE VIEW statement can be executed by:

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.

Examples of Creating Views

  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;

ALTER VIEW

Alters a view

Available in

DSQL

Syntax
ALTER VIEW viewname [<full_column_list>]
    AS <select_statement>
    [WITH CHECK OPTION]

<full_column_list> ::= (colname [, colname ...])
Table 1. ALTER VIEW Statement Parameters
Parameter Description

viewname

Name of an existing view

select_statement

SELECT statement

full_column_list

The list of columns in the view

colname

View column name.Duplicate column names are not allowed.

Use the ALTER VIEW statement for changing the definition of an existing view.Privileges for views remain intact and dependencies are not affected.

The syntax of the ALTER VIEW statement corresponds with that of CREATE VIEW.

Caution

Be careful when you change the number of columns in a view.Existing application code and PSQL modules that access the view may become invalid.For information on how to detect this kind of problem in stored procedures and trigger, see The RDB$VALID_BLR Field in the Appendix.