Re: On view updating

Laconic2 wrote:
> Maybe views need more definition... In addition to providing a
"SELECT"
> statement, maybe it should provide "UPDATE", "INSERT", and "DELETE"
> statements. These statements could work somewhat like triggers, in
the
> sense that they would be activated by circumstances. In some cases,
the
> "UPDATE", "INSERT" and "DELETE" are redundant, because they can be
inferred
> from the SELECT. In other cases, they can't be inferred, and have to
be
> explicit.
>
> I'm just playing around with this idea. Maybe I'll reject it after
further
> consideration.

Again, this is what some DBMSs (e.g. Oracle) do: for views that are not inherently updateable you can define an "INSTEAD OF" trigger that defines what should happen. There is nothing to stop you writing something totally wacky like turning an insert on a view into a delete from some quite unrelated table(s).

Another issue I forgot to mention with Date/McGoveran view updating, as I understand it, is that logically equivalent views can have different update characteristics. For example these (SQL syntax, and assume emp has a foreign key referencing dept):

create view v1 as
select emp.*
from emp, dept
where emp.deptno = dept.deptno
and dept.location = 'LONDON';

create view v2 as
select emp.*
from emp
where emp.deptno in
( select dept.deptno
from dept
where dept.location = 'LONDON'
);

Both views return those employees who belong to a department based in London. But the first is a join view, and so a DELETE from this corresponds to a DELETE from emp AND dept; the second corresponds only to a DELETE from emp. Yet the optimiser might well transform one form into the other (unless it takes account of this - in which case optimisation options have been reduced). Received on Mon Sep 20 2004 - 17:43:26 CEST