Subject Re: [IB-Architect] Updatable views
Author Jim Starkey
At 03:39 PM 12/7/01 +0100, Ivan Prenosil wrote:
>I prefer software that is doing what it is asked to do,
>rather than such that tries to do me a favor by "anticipating"
>my needs and trying to "help" me by doing some extra work automatically.

In general, you are correct. ESP is nice in a GUI, but for anything
else, predictable is better. In this case, however, I think you
are wrong.

Some views are obviously updatable (single table, view contains
primary key and fields for all unique indexes). These are the
official SQL updatable views. Lets call them class 1 views.
Other views are updatable if you were paying attention that
day in class. We'll call them class 2 views. Class 3 views
are those that are not updatable from their definitions, but
are updatable with a knowledge of the application semantics.
Class 4 views are just plain non-updatble.

By SQL rules classes 2 through 4 are required to produce errors.
By computer science rules classes 3 and 4 are required to product
errors. Pragmatically, only attempted update of a class 4 view
should be an error.

So what is the proper system behavior? The Borland guys obviously
think that if the SQL committee believes a view is updatable, the
system should do it for you (there is a very good reason why this
is wrong). OK, that says the class 1 views have an automatic update
but not classes 2 and 3. But what is the SQL committee gets smarter
and learns that class 2 views are properly updatable? For systems
that don't support view update by trigger, this means that an error
condition is replaced by a valid operation, which is upwards
compatible and consistent. In the case of Interbase that supports
arbitrary view update by trigger it means that the semantics of
a class 2 view update must change in an incompatible manner. It
also means that a developer understand the latest thinking (sic)
of the SQL committee with regard to automatic view update.

Diane Brown can be stretched only so far.

The alternative to this mess is a simple and consistent rule:
Any before update trigger overrides automatic view update. It
is simple to understand, simple to use, simple to implement, and
is compatible over SQL committee waffling.

The second advantage to the behavior is a trigger can set fields
in the target physical table not included in the view. This was
a very useful feature that Borland broke when they changed the
water in V4, and one that is absolutely critical to implementing
triggers for classes 2 and 3.

So, bottom line, Firebird is going to do something different
for views with triggers than views without triggers. The
question is whether it will be consistent across all views,
and if not, what views are handled which ways. I believe handling
all view consistently is the best solution. The fact that this
approach has more utility is frosting on the cake.

Jim Starkey