If I Update a View, Will My Original Tables Get Updated

If I update a view, will my original tables get updated

see Using Views in Microsoft SQL Server

When modifying data through a view
(that is, using INSERT or UPDATE
statements) certain limitations exist
depending upon the type of view. Views
that access multiple tables can only
modify one of the tables in the view.
Views that use functions, specify
DISTINCT, or utilize the GROUP BY
clause may not be updated.
Additionally, inserting data is
prohibited for the following types of
views:
* views having columns with derived (i.e., computed) data in the SELECT-list  
* views that do not contain all columns defined as NOT NULL from the tables from which they were defined
It is also possible to insert or
update data through a view such that
the data is no longer accessible via
that view, unless the WITH CHECK
OPTION has been specified.

Are Views automatically updated

Short Answer

Yes, if you query a View it will reflect the changed data in the tables it is based on.

Long Answer

Preface

I read these answers and it made me question how Views work so I did some research and what I found support but also added to the answers listed so I want to add this to the pot.

I source my references with *# which definitions at the bottom.

Overview

There are different types of Views and they have different types of behaviors. Some are stored then updated frequently and others are not stored at all and computed on the fly.

Definition of a View

"A view is a virtual table whose contents are defined by a query...Unless indexed, a view does not exist as a stored set of data values in a database." *1

Nonindexed Views

"Unlike permanent tables, a view has no physical representation of its data unless you create an index on it. Whenever you issue a query on a nonindexed view, SQL Server in practice has to access the underlying tables. Unless specified otherwise..." *1

So Nonindexed Views are calculated when called.

Indexed Views

"An indexed view is a view that has been materialized. This means the view definition has been computed and the resulting data stored just like a table." *2

As Indexed Views are stored they are not well suited for tables that often update as they will need to constantly update the materialized data and their index.

Answer

In both cases, Indexed or Nonindexed Views reflect the changes in the tables they refer once the change is made either when you call the View or when the change is made based on if it is Indexed.

References

*2 https://learn.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver15

Do Changes Made on Table Created by Joining a View and an Another Table Affect Original Table View Based On?

In principle, yes, if the views are constructed in such a way that the underlying tables are updateable through the view. For example, let's say I have a table named TABLE_1 and create a view VIEW_1 as follows:

CREATE OR REPLACE VIEW VIEW_1 AS
  SELECT *
    FROM TABLE_1

If I issue the update statement

UPDATE VIEW_1
  SET FIELD_N = 'XYZ'
  WHERE KEY_1 = 123

Oracle is bright enough to pass the UPDATE through to the underlying table, and TABLE_1 will be updated.

A view of any complexity, however, will most likely contain operations that make the view non-updateable. So let's say I have the following VIEW_2:

CREATE OR REPLACE VIEW VIEW_2 AS
  SELECT KEY_1,
         FIELD_N,
         SUM(SOME_OTHER_FIELD) AS OTHER_SUM,
         MIN(YADDA_YADDA) AS MIN_YADDA
    FROM TABLE_1
    GROUP BY KEY_1,
             FIELD_N

an UPDATE of this view will fail with an ORA-01732: data manipulation operation not legal on this view error. So whether you can update through a view or not very much depends on what operations the view is performing.

db<>fiddle here

Why view is not updated if my table definition is updated?

When you create a view it stores the view definition as metadata in the system tables. Even if you use SELECT * FROM it will store the exact column names e.g. SELECT a, b FROM

If you then update the base table the metadata definition for the view still remains the same so any new columns won't be picked up.

You need to either drop and recreate the view or run sp_refreshview to update the definition

Can a view be updated/inserted/deleted? If Yes under what conditions?

In Oracle you can update a view under these conditions:

from: Oracle database SQL Reference

Notes on Updatable Views

An updatable view is one you can use to insert, update, or delete

base table rows. You can create a view to be inherently updatable, or
you can create an INSTEAD OF trigger on any view to make it

updatable.

To learn whether and in what ways the columns of an inherently

updatable view can be modified, query the USER_UPDATABLE_COLUMNS data
dictionary view. The information displayed by this view is meaningful
only for inherently updatable views. For a view to be inherently

updatable, the following conditions must be met:

Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an

unnested collection), then the view is not inherently updatable.

The view must not contain any of the following constructs:

A set operator

A DISTINCT operator

An aggregate or analytic function

A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause

A collection expression in a SELECT list

A subquery in a SELECT list

A subquery designated WITH READ ONLY

Joins, with some exceptions, as documented in Oracle Database
Administrator's Guide

In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE
statement that refers to any of these pseudocolumns or expressions.

If you want a join view to be updatable, then all of the following conditions must be true:

The DML statement must affect only one table underlying the join.

For an INSERT statement, the view must not be created WITH CHECK
OPTION, and all columns into which values are inserted must come from
a key-preserved table. A key-preserved table is one for which every
primary key or unique key value in the base table is also unique in
the join view.

For an UPDATE statement, all columns updated must be extracted from
a key-preserved table. If the view was created WITH CHECK OPTION, then
join columns and columns taken from tables that are referenced more
than once in the view must be shielded from UPDATE.

For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first

table named in the FROM clause, whether or not the view was created

WITH CHECK OPTION.

In SQL Server, you can insert, update, and delete rows in a view, subject to the following limitations, Source

If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.

You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.

All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base
table.

Text and image columns can't be modified through views.

There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to

either add or edit a row that does not have City = 'Paris', the data

will be modified in the base table but not shown in the view, unless

WITH CHECK OPTION is used when defining the view.

Using View to Update Values of Original Table

You can use a left join:

select c.id, c.name,
       nz(pi.isActive, c.isActive) as isActive
from customers as c left join
     vPersonalInfo as pi
     on c.id = pi.id;

Update data in original table from view

you can create the Tabular Form using the 2 tables without a view as long as only one of the tables is being updated.

Just create your Tabular Form choosing the table you need to update and then change the query to include the columns from the other table and change all the column from the included table to Plain Text.

If you prefer to use a view you should create an instead of trigger, like this:

CREATE OR REPLACE TRIGGER iof_MY_TRIGGER
INSTEAD OF UPDATE
ON v_tbls
FOR EACH ROW

BEGIN

  UPDATE table1 
     SET emp_status = :NEW.EMP_STATUS
   WHERE emp_id = :NEW.EMP_ID;

END iof_MY_TRIGGER;
/

To include a checkbox to your tabular form change your tabular form for something like this:

SELECT emp_id
,      emp_name
,      apex_item.checkbox2(5, emp_status, DECODE(emp_status, 1,'CHECKED',0))
  FROM table1

You can read more about this in here

Why would I update a value in a view?

Your question seems to be more concerned with "why" rather than "how." Why would DML be executed against a view instead of directly against a table? The answers are almost too numerable to list here, but here are just a couple of the bigger ones.

For starters, when I design a database, almost every table has at least one view defined for it. If more than one, one is normally the DML view and the others are read-only (trigger that does nothing). No outward-facing and very few inward-facing apps have direct access to the tables. Everything must go through the views.

Why? Because this builds a wall of abstraction between the apps and the underlying tables. I can then constantly tune and tweak the tables and rarely have to make any changes to the apps. I can add or drop columns, change the data type of columns, merge tables together or split a table into two or three separate tables, but the views bring everything back together to how the apps expect to see it. They don't even have to know any changes were made. The apps can write data to what they see as a single "table" and the view trigger assigns the data to the correct tables. The view triggers know how the actual data is stored, the apps don't have to know.

Here's one advantage that is unbeatable. There are many, many useful functions that require the use of a before trigger. Sometimes you just really want to manipulate the data in some way before it goes to the table. Some DBMSs, even major players like SQL Server, have only after triggers. Such a pain.

But front the table with a view, write a trigger on the view, et voila, instant before trigger. This is even handy with other DBMSs like Oracle in that the trigger can update other associated rows in the same table without running afoul of a mutating table condition. I am at this very moment writing a trigger on a view that must update values in several rows every time any Insert or Delete is executed and sometimes for an Update. Almost impossible task without views -- it would have to be done with stored procedures rather than "direct" DML. Handy for me, handy for the apps. Life is good!

Also, take the loop condition caused by trying to keep two tables synched with each other. Any changes made in either table should be sent to the other table. But a trigger on both tables that just mirrors the operation to the other table will create an infinite loop as that trigger turns around and sends it right back. But a trigger on a facing view can perform the DML on its underlying table and on the mirrored table. The view on the mirrored table does the same thing. But since both views operate directly on the tables, there is no infinite loop condition to fall into.

So my question would be more like: why would we ever want users to directly access tables rather than have them go through views?

If I Update a View, Will My Original Tables Get Updated