Marcin Bury wrote:
Can someone explain how updatable view works?
Ann W. Harrison answers:
In version 1.x, they work differently depending on whether the view is "naturally updateable". A "naturally updateable" view is one without joins, aggregates, projection (distinct), or grouping - like the one you describe. Update, insert, and delete statements on naturally updateable views work exactly as they would on base tables - both the operation and triggers are executed.
Views with joins, aggregates, projection, or grouping behave differently - the only actions taken on a insert, update, or delete statement are the actions defined in triggers.
In Firebird 1.x, the simplest way to disable natural updates on simple view is to change the view to include a join to a table with one row - RDB$DATABASE is a good choice.
For example to disable natural updates on a simple view like this:
create view naturallyUpdateable (a, b, c) as
select a, b, c from table1
change the view definition to this:
create view triggerUpdateOnly (a, b, c) as
select t1.a, t1.b, t1.c
from table1 t1
joint rdb$database t2
According to the "incompatibilities" document, the rules will change in V2.0, but the "join hack" will continue to work.
Views updatable via triggers no longer perform direct table operations. In previous versions, a naturally updatable view with triggers passed the DML operation to the underlying table as well as executed the triggers.
So if you followed the official documentation and used triggers to perform a table update, this update was actually done twice, causing either performance issues or errors (if blobs were affected). Now availability of triggers turn direct table updates off and everything depends on your triggers. If you rely on the legacy undocumented behavior (e.g. use triggers for logging purposes only), you'll need to adjust your triggers to perform actual updates as well.
On the plus side, this change allows triggers to override the natural operations on views - something that now requires unnatural acts like joining to RDB$DATABASE. After a minor bump, it was decided that triggers that support constraints will not disable natural updates, so adding a declarative SQL constraint to a view does not require that you also write triggers for all update operations.
However, other apparently innocent triggers - like those that perform logging or data validation - will stop natural updates. For hand written systems, that's probably not bad, but a tool that automatically generates triggers for logging is going to have to be very smart about simple views.