CREATE VIEW

<< CREATE TRIGGER | FB 2.0 Language Reference | CREATE OR ALTER EXCEPTION >>

CREATE VIEW

Full SELECT syntax supported
PLAN subclause disallowed in 1.5,
reallowed in 2.0
Triggers on updatable views
block auto-writethrough
View with non-participating
NOT NULL columns in base table
can be made insertable

`CREATE VIEW`

Available in: DSQL, ESQL

Full `SELECT` syntax supported

Changed in: 2.0

Description

From Firebird 2.0 onward view definitions are considered full-fledged SELECT statements. Consequently, the following elements are (re)allowed in view definitions: FIRST, SKIP, ROWS, ORDER BY, PLAN and UNION.

Note: The use of a UNION within a view is currently only supported if you supply a column list for the view (this list is normally optional):

 create view vplanes (make, model) as
   select make, model from jets
     union
   select make, model from props
     union
   select make, model from gliders

In Firebird 2.5, the column list will become optional also for views with UNIONs.

`PLAN` subclause disallowed in 1.5, reallowed in 2.0

Changed in: 1.5, 2.0

Description

Firebird versions 1.5.x forbid the use of a PLAN subclause in a view definition. From 2.0 onward a PLAN is allowed again.

Triggers on updatable views block auto-writethrough

Changed in: 2.0

Description

In versions prior to 2.0, Firebird often did not block the automatic writethrough to the underlying table if one or more triggers were defined on a naturally updatable view. This could cause mutations to be performed twice unintentionally, sometimes leading to data corruption and other mishaps. Starting at Firebird 2.0, this misbehaviour has been corrected: now if you define a trigger on a naturally updatable view, no mutations to the view will be automatically passed on to the table; either the trigger takes care of that, or nothing will. This is in accordance with the description in the InterBase 6^Ž Data Definition Guide under Updating views with triggers.

Warning: Some people have developed code that takes advantage of the previous behaviour. Such code should be corrected for Firebird 2.0 and higher, or mutations may not reach the table at all.

View with non-participating `NOT NULL` columns in base table can be made insertable

Changed in: 2.0

Description

Any view whose base table contains one or more non-participating NOT NULL columns is read-only by nature. It can be made updatable by the use of triggers, but even with those, all INSERT attempts into such views used to fail because the NOT NULL constraint on the base table was checked before the view trigger got a chance to put things right. In Firebird 2.0 and up this is no longer the case: provided the right trigger is in place, such views are now insertable.

Example

The view below would give validation errors for any insert attempts in Firebird 1.5 and earlier. In Firebird 2.0 and up it is insertable:

 create table base (x int not null, y int not null);

 create view vbase as select x from base;

 set term #;
 create trigger bi_base for vbase before insert
 as
 begin
   if (new.x is null) then new.x = 33;
   insert into base values (new.x, 0);
 end#
 set term ;#

Notes:

Please notice that the problem described above only occurred for NOT NULL columns that were left outside the view.
Oddly enough, the problem would be gone if the base table itself had a trigger converting NULL input to something valid. But then there was a risk that the insert would take place twice, due to the auto-writethrough bug that has also been fixed in Firebird 2.