"Marco Lauria" <mslauria@...> wrote in message
news:5.0.2.1.2.20011126104857.00b19518@......

>
> I trust you, knowing that you are a very good programmer, when you say
> that from the engine - point of view - updatable views are a nightmare...
> but for me are really useful.

Updatable views are those that don't need that you write a trigger to allow
updates, insertions and deletions. Most views aren't updatable and should be
made modifiable by writing such triggers. The problem is with the former
group... fine because you don't write any trigger and can modify data
through them... however when you need to take control, you'll find that the
default action and your trigger both fire. Just imagine if you have

create table t(a int not null primary key, b int);
create view v as select a,b from t;
set term ^;
create trigger tr for v before insert as
begin insert into t values(new.a, new.b); end^
set term ;^

If "a" is the PK of T, you'll get a duplicate value exception: the default
action took place, then the trigger tried to insert, too. Since both are
using the value of "a" unmodified, they clash on the PK's index:

SQL> insert into v values(0);
Statement failed, SQLCODE = -803
violation of PRIMARY or UNIQUE KEY constraint "INTEG_2" on table "T"

There's no solution, you should do a fake join to force your view to become
non-updatable, so only the trigger makes it updatable again and only the
trigger fires when you insert into V. As long as you wrote TR, the default,
automatic insertion should be inhibited, because you took control of the
insertion event by means of the trigger TR.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing