Mechanism of Views

Question

I know that View is nothing but the stored select query of a table. When ever we call the view, that select query will trigger in the back-end to display as the result of the view.

"I also know that we can update/insert/delete the data from the VIEW and all the modifications we did for view will be reflected in the underlying table"

But this is what confusing me.

Q1: If the view is a simple select query and when ever it called, it will execute the select query form back-end, How does the update/insert will work on the view. I don't understand how the update/insert will work on "Select query" alias VIEW?

Q2: I know that a views can be refreshed by executing the select on underlying table. But how the changes made to view will effect the underlying table's Data?

When we did the update/delete/insert what mechanism is going on in the back end to update the view as well as underlying table?

Answer 1

First of all understand view never refreshed (except Materialized view in Oracle). Assume you have created view like below create view myview as select * from t;

That view query will be stored in database. Whenever you call that view then database will load that stored query and execute eg select * from myview

in the above case database will change the query to like below and execute select * from (select * from t);

So database always fetch data from the base table not from view (or refresh view).

For your first question, see the below eg update myview set c1 = 123; In this case db will change this update to like below update (select * from t) set c1 = 123; This is feasible in any rdbms.

For your second question, whatever data change using view will affect the base table data only not change/update the view.