Pardon my ignorance, but I wonder if you could answer a question.

Why would you ever need to insert into a view?

Also, suppose the view has a restrictive where clause, if the inserted row
doesn't match the where clause, would the insert fail?

What if the view is a multi table join?

What about updates to a view?

Thanks in anticipation

"Strangiato" <nochance (AT) youmustbejoking (DOT) com> wrote

Multiple applications or multiple application versions.


It should fail because the inserted row fails the predicate of the view.


What do you want to know?


Again, what do you want to know?

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

I guess the mechanics of how the data would get inserted. I can't get my
head around what would happen if you inserted data into a multi table
select. I'm more used to looking at the output of such a select.


First of all, is it a done thing and if so I guess the answer to my above
insert question would answer the update question.
Thanks for replying, by the way.

"Strangiato" <nochance (AT) youmustbejoking (DOT) com> wrote

An insert would insert to both tables and an update would update both
tables.

Join doesn't present much of a problem--project presents the problems.

After takin a swig o' Arrakan spice grog, "Strangiato" <nochance (AT) youmustbejoking (DOT) com> belched out:
Consider a payroll application where views are being used to make sure
that users only have access to the employee data that they are
_allowed_ to access.

In that case, there may be a need to update/add data, and a particular
user is only allowed access to part of the data.

Yes, it might, or perhaps the "view update scheme" may force the
values to conform to the "where clause" so that this is a non-issue.

That's less of a problem than a view that is a non-affine/non-linear
projection.

Again, no worse than inserts or joins...
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://cbbrowne.com/info/advocacy.html
Artificial intelligence, like fusion power, has been ten years away
for the last 30 years. -- Conrad Stack

Security

<<

Read about the WITH CHECK OPTION in any SQL book

The SQL-92 standard is actually very conservative about what VIEWs are
updatable. They have to be based on:

(1) a SELECT statement on one and only one table; this can go thru
several layers of VIEWs on top of VIEWs, however.

(2) The VIEW must include all the columns of a key (i.e. a UNIQUE or
PRIMARY KEY constraint) in the base table

(3) All columns not shown in the VIEW must have default values or be
NULL-able (if you think about it for a minute, this lets you construct
and insert a complete row into the base table).

The whole idea is that an updatable VIEW looks and behaves pretty much
like a base table, but slightly restricted as to what you can see.
One row in the VIEW maps to exactly one row in the base table from
which it is drawn.

However, other views are updatable and some vendors support more than
the basic version given in the SQL-92 Standard. The VIEW must have an
INSERT, UPDATE and DELETE rule under the covers which maps its rows
back to a single row in the base table(s).

for practical information with the fastest reading time is Nathan
Goodman in InfoDB. He discusses:

1) Projection from a single table
2) Selection/Projection from a single table
3) UNION VIEWs
4) Set Difference VIEWs
5) One-to-one joins
6) One-to-one outer joins
7) One-to-many outer joins
8) One-to-one outer joins
8) Many-to-many joins
9) Translated and coded fields

Codd, E. F.; THE RELATIONAL MODEL FOR DATABASE MANAGEMENT: VERSION
TWO; "RV-6 VIEW Updating"; (ISBN 0-201-14192-2).

Date, C. J. & Hugh Darwen; RELATIONAL DATABASE WRITINGS -1989-1991;
"Role of Functional Dependencies in Query Decomposition"; (ISBN
0-201-54303-6).

Date, C. J.; RELATIONAL DATABASE: SELECTED WRITINGS; "Updating VIEWs";
(ISBN 0-201-14196-5).

Goodman, Nathan; INFODB Vol 5, No 2 (Summer 1990); "VIEW Update is
Practical".

Umeshar, Dayal & P. A. Bernstein; ACM TRANSACTIONS ON DATABASE SYSTEMS
Vol 7, No 3 (Dec 1982); "On the Correct Translation of Update
Operations on Relational VIEWs".

Another feature, which is not used enough, is the WITH CHECK OPTION
clause on a VIEW. It is a bit tricky, but if you would like I can
post a section from SQL FOR SMARTIES on this feature.