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

Question

I googled above question and got the following answers

YES--> If it is in the case of simple view (which consists only one base table).

NO---->If it is in the case of complex view( which consists multiple base tables, and joins).

However, there is an indirect way of performing DML operations on Complex views, by using "Instead Of trigger". In the body of Instead of trigger we will use co-relational identifiers, and DML statements to perform DML operations indirectly on the complex view. Then that changes will reflect in base tables of that particular complex view.

here my question is does changes applied to view will have any effect on original tables?

If a View on a single base table is manipulated will the changes be reflected on the base table?

can any one give me example for the above cases?

Answer 1

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.