A view WITH CHECK OPTION clause can be useful when you want to provide users with an updatable view, but you want to prevent them from updating certain columns. Just include a search condition for each column you want to protect. The clause s usefulness is limited somewhat by the fact that a view cannot be defined with replaceable parameters.

Using Barcode creation for Software Control to generate, create EAN-13 Supplement 5 image in Software applications.

TarCode.com/European Article Number 13

Using Barcode encoder for Software Control to generate, create USS Code 39 image in Software applications.

TarCode.com/Code 3/9

Read-Only and Updatable Views

Using Barcode drawer for Software Control to generate, create UCC - 12 image in Software applications.

TarCode.com/EAN 128

Using Barcode drawer for Software Control to generate, create British Royal Mail 4-State Customer Barcode image in Software applications.

TarCode.com/British Royal Mail 4-State Customer Barcode

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.

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

Using Barcode encoder for Java Control to generate, create PDF417 image in Java applications.

TarCode.com/PDF 417

Using Barcode recognizer for VS .NET Control to read, scan read, scan image in .NET applications.

TarCode.com/Barcode

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.

Using Barcode encoder for ASP.NET Control to generate, create EAN13 image in ASP.NET applications.

TarCode.com/GS1 - 13

Using Barcode generator for Android Control to generate, create Barcode image in Android applications.

TarCode.com/Barcode

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. For more information about triggers and the new.* context variables, refer to 31.

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: