Read-Only and Updatable Views - Firebird

When a DML operation is performed on a view, the changes can be 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 updatable. If it does not meet these conditions, it is read-only, and writes to the view cannot be passed through to the underlying tables.

Values can only be inserted through a view for those columns named in the view. Firebird stores NULL into any unreferenced columns. A view will not be updatable if there are any columns not in the view that are non-nullable.

A read-only view can be made updatable by means of triggers.

Read-Only Views

A view will be read-only if its SELECT statement has any of the following characteristics:

  • Specifies a row quantifier other than ALL (i.e., DISTINCT, FIRST, SKIP)
  • Contains fields defined by subqueries or other expressions
  • Contains fields defined by aggregating functions and/or a GROUP BY clause
  • Includes UNION specifications
  • Joins multiple tables
  • Does not include all NOT NULL columns from the base table
  • Selects from an existing view that is not updatable

Making Read-Only Views Updatable

You can write triggers that will perform the correct writes to the base tables when a DELETE, UPDATE, or INSERT operation is requested for a view. This Firebird feature can turn many non-updatable views into updatable views.

The following script creates two tables, creates a view that is a join of the two tables, and then creates three triggers (one each for DELETE, UPDATE, and INSERT) that will pass all updates on the view through to the base tables:

When defining triggers for views, take care to ensure that any triggers on the view do not create a conflict or an unexpected condition with regard to triggers defined for the base tables. The trigger event for the view precedes that for the table, respective of phase (BEFORE/AFTER).

For example, suppose you have a Before Insert trigger on the base table that fetches a fresh generator value for the primary key if its new.value is null. If the view trigger includes an INSERT statement for the base table, omit the primary key column from the view’s statement. This causes NULL to be passed to the new.value of the primary key column, allowing the table trigger to do its work.

Hopeless Cases

Not all views can be made updatable by defining triggers for them. For example, this handy little read-only view reads a context variable from the server, but regardless of the triggers you define for it, all operations except SELECT will fail:

CREATE VIEW SYSTRANS (CURR_TRANSACTION) AS SELECT CURRENT_TRANSACTION FROM RDB$DATABASE;

Naturally Updatable Views

A view is naturally updatable if both of the following conditions are met:

  • The view specification is a subset of a single table or another updatable view.
  • All base table columns excluded from the view definition are nullable.

The following statement creates a naturally updatable view:

CREATE VIEW EMP_MNGRS (FIRST, LAST, SALARY) AS SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEE WHERE JOB_CODE = 'Mngr' WITH CHECK OPTION;

Because the WITH CHECK OPTION clause was included in this specification, applications will be prevented from changing the JOB_CODE, even if there is no violation of the foreign key constraint on this column in the base table.

Changing the Behavior of Updatable Views

Alternative behavior for naturally updatable views can be specified using triggers. For a particular phase (BEFORE/AFTER) of an operation, the view triggers fire before the base table’s triggers. With care and skill, it is thus possible to use views to pre-empt the normal trigger behavior of the base table under planned circumstances.

It is also possible to create havoc with badly planned view triggers. Test, test, test!


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics