Hello.

Updatable views are nice, eh? After a week doing dumb joins just to avoid
some VIEWS from being updatable, I think I will ask for some ideas to exert
a better control over them:

According to the manual (and it seems accurate in my experience) only simple
views that don't carry aggregates nor joins (and other restrictions) are
made updatable automatically. For the rest of them, you should write
triggers to make them R/W for the user and I'm not speaking about rights
over tables, but about VIEW's intrinsic rules. Asides, it seems that a view
that includes a constant as one of the fields (select 1 from) cannot be made
updatable even after defining triggers, not sure if it's expected behavior.

The power of a view (for me) comes when you define triggers that -unlike
triggers on a table- are able to change the semantics of operations: a
before-insert trig for a view can perform an update on the underlying table
or in the case of a join-based view, apply different operations to the
underlying tables. Very powerful and useful, so you give the user rights on
the view and deny him/her rights on the tables. And if you include
selectable procs and procedures that perform complex/conditional operations
on groups of views, we have a very interesting toolbox.

Until now, I've shown the bright side. Now it comes the problem: some of my
views operate on a single table. They don't need triggers on them to be
updatable. But since I write triggers for them to alter semantics or change
some fields before they are stored, both the trigger and the direct action
are executed. This is a headache. Now if you insert in such view, the direct
INSERT command applies to the underlying table (automagically) followed by
the INSERT carried by the defined trigger. If both go to the same table -and
assuming you use a PK- errors will arise (PK violation) or if the PK is
based on a generator you may have two insertions depending on when/how you
call gen_id. With updates you will have two or three updates and the engine
reports back that your single operation caused several writes.

I again found myself this time doing in these simple views my old trick
create view v as select ...
from tbl join rdb$database on 1=1;

just to trick the engine and force the view to be only updatable by my
triggers, not directly. There should be a better solution. Either a reserved
word to make a view explicitly non-automagically updatable or a mechanism in
place so when triggers are defined on a view that's already updatable, the
"natural" behavior won't fire and only the user triggers will execute.

The topic was proposed at Mers a year ago but I don't remember any solution.
Ideas? I'm not looking for workarounds, but for ideas to enhance the engine.
As the subject says, I'm sick of automatically updatable views and I'm
looking for a way to allow them only at will.

C.
---------
Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente
http://www.cvalde.com - http://firebird.sf.net