Re: Date's updatable view rules
Date: 25 Jul 2004 05:14:13 -0700
Message-ID: <c0e3f26e.0407250414.65c609ab_at_posting.google.com>
"Marshall Spight" <mspight_at_dnai.com> wrote in message news:<l7vMc.20397$eM2.2889_at_attbi_s51>...
> What do people here think about Date's updatable view rules?
>
> http://www.dbdebunk.com/page/page/1270150.htm
As you can see from that link, he is rethinking this idea. It isn't as straight-forward as some of his previous writings seem to suggest. I wrote to Hugh Darwen a while ago with an issue I had with view updating as follows:
It seems to me that some of the view updating rules go against what the user would reasonably expect to happen. For example, suppose we have defined the view:
VAR LONDON_SP VIEW
(SP JOIN S) WHERE CITY = 'LONDON' {S#, P#, QTY}
This could be intended to provide a London user with a
restricted view of SP, like this:
S# P# QTY
== == ---
S1 P1 300
S1 P2 200
S1 P3 400
S1 P4 200
S1 P5 100
S1 P6 100
S4 P2 200
S4 P4 300
S4 P5 400
It could therefore be the case that such a user can only access LONDON_SP and not SP.
Now suppose that that user wishes to delete one row, specifically the row for S1 and P1. He would do this:
DELETE LONDON_SP WHERE S# = S#('S1') AND P# = P#('P1'); But according to the view updating rules, this would result in the attempted deletion of supplier S1 also. Either the attempt to delete the row must fail, or supplier S1 and all its related SP rows must be deleted (via ON DELETE CASCADE) resulting in the view containing just:
S# P# QTY
== == ---
S4 P2 200
S4 P4 300
S4 P5 400
Neither outcome is satisfactory!
I received interesting responses from both Hugh and Chris Date, saying that this subject was to be revisited due to such issues.
My feeling is that view updating is nowhere near as straight-forward as has been suggested, and that the SQL vendors like Oracle don't allow many types of view update for good reason! Received on Sun Jul 25 2004 - 14:14:13 CEST