Re: View updating in practice?

Alexander.Kuznetsov_at_marshmc.com (Alexander Kuznetsov) writes:

> Hi Jens,
>
> <I think>
> once upon a time, I had 1 yr experience in T-SQL and Sybase and
> considered myself quite an expert. Oops! I issued and UPDATE without
> WHERE clause againsht a beeeeeeg table in a production database.
>
> Updatable views you are speaking about are no more dangerous than
> standard ANSI compliant DML, as long as UPDATE/DELETE statements can
> run without WHERE clause.
>
> Since then I have dealt with more than 20 similar mishtakes made by
> other developers, so I've quite atoned for that error. I'm sick, sick,
> sick of it. Currently our middelware wont let thru a statement without
> WHERE clause.
>
> I'd say here is a very useful updateable view (anybody may deal with
> there own data only)
>
> CREATE VIEW MY_DATA
> AS
> SELECT * FROM ALL_DATA WHERE ENTERED_BY = CURRENT USER
>
> Funnelling all the OLTP threw this view would have prevened the
> disashter I was speakin about. Such views are very frequently used out
> here in the field.
> </I think>
>
> What do you think?

I think that this view is OK as long as users cannot modify ENTERED_BY. Maybe I should point out that my opinion is not as radical as I might seem in my paper. I still believe that view updates that are translatable under constant complement are reasonable ones. If I write a next version of that paper (depending on input I get here ;) it will contain a list of views and updates that satisfy this requirement.

Jens Received on Mon Nov 11 2002 - 09:38:05 CET