Re: View updating in practice?

Serge Rielau <srielau_at_ca.ibm.com> writes:

> Hallo Jens,
>
> I think a problem in your paper is that it does not consider other
> features of SQL. For example it would be feasable to disallow the
> trainer to delete members from the team view. Instead he could be
> allowed to UPDATE the "member-of team" column (which would need to
> be provided in TEAM) and thus hire and fire as he pleases.

Actually, I think that there should be two tables: Emp and Team. Then member-of team should be an attribute in Emp, and the trainer must have update privileges for the attribute, not only for Team.

> As indicated by others INSTEAD OF triggers are a way to enforce
> the semantics you wish (although in my knwledge thay are NOT part
> of the SQL standard (yet)). E.g. the DELETE can be interpreted as
> an update to said underlying member-of-team column. And an INSERT
> could do the same. Coincidentally I recently published a (sample
> oriented) article on this topic. It's aimed at DB2, but applies to
> Oracle and MS SQL Server just the same:
> http://www7b.boulder.ibm.com/dmdd/library/techarticle/0210rielau/0210rielau.html

Thank you for that link. I've got two questions:

In example 1, you write: "Note, however, that if one performed a positioned delete and deleted one row where V1.c1 equals 5, there is no telling which of the two rows where T1.c1 equals 5 will be deleted."
Aren't all rows where V1.c1 equals 5 deleted?

Similarly in Example 4:
"Every row in V4 clearly originated from one row in a specific table."
But the user does not know that as s/he does not see Row Identifiers. (Assuming duplicates again.) Aren't all duplicates deleted?

Concerning the trigger examples, I'll need some time before I can say anything.

> The reasoning you use can equaly be applied to base tables. In
> specific it would suggest that triggers as well as cascading RI
> constraints would be harmful since the user might not be able to
> even find his row again (the before trigger might change, or even
> generate, the primary key). The RI constraint or after trigger may
> have impact throughout the database making it impossible to
> recover with the limited knowledge. Does this mean that database
> side logic is bad?

My position is the following: Whatever database side logic you use, be aware of the consequences. Sometimes you can safely hide that logic from users sometimes you cannot or do not want to. That fact that you can translate any view update into some kind of database update does not mean that this translation is what users expect. (You exclude view updates with side effects, e.g., insert into join in example 3. Why? Are you sure that there is no application scenario?)
I wrote the article out of the feeling that papers/presentations on view updating are strongly biased by not warning enough against the consequences.

> Gruss nach Muenster Serge

Herzliche Grüße nach Toronto,

Jens Received on Mon Nov 11 2002 - 10:41:25 CET