Hello, all.

It seems that I will be remembered as the south american guy that used to
rant against SQL VIEW's.
:-)

I'm totally sick of those automatically updatable views (aka UV). For what I
see in ib-support, I'm not alone and others do my trick, too, namely,
creating a join with a dummy table to force it to be non-updatable.

The problem with UVs is that they don't allow you to take control of the
actions. You create a trigger for before insert, then that trigger fires,
but (and here lies the problem), the default action is executed anyway.
Hence, you are screwed if you want to change the semantics: let's say your
intention is to write to another table, not to the base table. Unless you
resort to the dummy join hack, your insertion will go anyway to the base
table. Quick example:

create table T (a int);
create view V(b) as select a from t;

insert into v values(0);
puts the record in T as expected.

Now, isql's "set term" put aside,

create trigger tr for v
before insert
as begin
insert into another_table(only_time, v_item)
values (current_time, new.b);
end

Now,
insert into v values(0);
writes to both T and ANOTHER_TABLE. How do I stop the default insertion into
T from taking place? I can't. I should redefine the view as having a dummy
join with a dummy table or with rdb$database. Then the UV becomes
non-updatable and only trigger fire.

It seems that the goal of changing the behavior to cancel any default action
as soon as you create a trigger is too aggressive and may defeate current
applications. So, the solution is in the syntax. Diane once posted about an
extended syntax in Oracle.

I took Ann's suggestion to propose

create view vname(fields)
[for trigger update]
as select ...;

instead of "triggered" as she thought, to not introduce new reserved words
to the name space. Or maybe
[for update of trigger]

The problem is that it suggest that we are dealing with UPDATE operations
only. I revised the list of reserved words and ACTION is reserved, so we
could have instead

create view vname(fields)
[for trigger action]
as select ...;

The advantage would be (without JOIN cheap tricks) to give the triggers the
full control of the view. At the same time, when reading a script, it's more
evident something is being attempted with a view than finding where some
dummy joins have in its body definition.

After that, the UV becomes non-updatable until developer writes before or
after insert trigger. Then insertions are enabled. If you want deletions,
write a before or after delete trigger, etc. This is the current behavior
with non-updatable views.

I'm not inventing new facilities. I'm only formalizing a hack (dummy join)
that's been used to work around a behavior that doesn't give the developer
enough freedom. Views can change the semantics of any operations through
triggers.

Here's a silly example that's a bit confusing thanks to the current double
behavior:

SQL> create table tproblem(a int);
SQL> create view vproblem(b) as select a from tproblem;
SQL> set term ^; ===> Jim's most loved command
SQL> create trigger tr_problem
for vproblem before insert
as begin
update tproblem set a=0 where a = new.b;
end^
SQL> set term ;^

SQL> set count;
SQL> insert into vproblem values(9);
Records affected: 1
SQL> select * from vproblem;
B
============
9
Records affected: 1
SQL> insert into vproblem values(9);
Records affected: 1
SQL> select * from vproblem;
B
============
0
9

Records affected: 2
SQL> ^Z

It's clear that first, the trigger fires over the view, then the default
action is executed over the underlying table. Now, if a before insert
trigger were to do instead

insert into tproblem
select t.c from t
where t.a = new.b

and t.c happens to be the same value than new.b, since the trigger inserts
t.c and then the default insert goes with new.b, it takes a PK or UNIQUE
constraint in the table to get a key violation.

Those issues should be stopped with a syntax extension that self-documents
the view, not with the current hacks we need.

create view vname(fields)
[for trigger action]
as select ...;

Comments welcome.

C.
---------
Claudio Valderrama C.
Ingeniero en Informática - Consultor independiente
http://www.cvalde.com - http://www.firebirdSQL.org