sql – Is it possible to make changes to a DML record by statements – INSERT, UPDATE, DELETE – through a view?

Question:

Is it possible to perform changes to a DML record with statements – INSERT , UPDATE , DELETE through a view ( view )?

Answer:

Views in the database can be changeable ( updateable views ) under certain conditions.

Excerpt from Oracle SQL Reference :

Remarks for mutable views

A view that is mutable means that it can be used to change records in its underlying tables. It is possible to create a view that is in principle mutable (ie itself mutable), or it is also possible to create an INSTEAD OF trigger on any view to make it mutable.

You can find out which and how columns of a view that can be changed in principle (that is, changeable without a trigger) can be viewed through the USER_UPDATABLE_COLUMNS .

In order for a view to be modified, all of the following conditions must be met:

• Each view column must represent a column of one table. For example, if the column represents the output of a TABLE statement, then this condition is not met.

• The view must not contain one of the following constructs:

  • SET statement
  • DISTINCT statement
  • Aggregate or analytic function
  • GROUP BY , ORDER BY , MODEL , CONNECT BY , or START WITH expressions
  • Expression for collections in SELECT sheet
  • Subquery in SELECT sheet
  • Subquery with WITH READ ONLY
  • Joins ( joins ), with some exceptions listed in the Oracle Database Administrator's Guide

Additionally, if in principle the view being modified contains pseudo-columns or expressions, then table records cannot be modified with an UPDATE clause that refers to those pseudo-columns or expressions.

If a view containing a join of tables is to be made mutable, then all of the following conditions must be met:

• DML statement must affect only one join table
• For an INSERT clause, the view must not be created with WITH CHECK OPTION , and all columns into which values ​​are inserted must come from a stored-key table. A key-preserved table is a base table in which each primary or unique key value will retain its uniqueness also in the view after the join.

• For an UPDATE clause, the view must not be created with WITH CHECK OPTION , and all columns to be modified must come from a table with stored keys.

• For a DELETE clause, if the join results in more than one table with preserved keys, then the delete will be from the first table specified in the FROM , regardless of whether the view was created with or without the WITH CHECK OPTION .

_{Source of @DCookie answer. When translating, it is verified from off. documentation of the current release 19c}