sql - 可以更新/插入/删除 View 吗？如果是，在什么条件下？

Question

我用谷歌搜索了上面的问题并得到了以下答案

YES-->如果是简单 View (只包含一个基表)的情况。

NO---->如果是复杂 View (由多个基表组成，并有join)。

但是，有一种间接方法可以通过使用“Instead Of trigger”对复杂 View 执行 DML 操作。在而不是触发器的主体中，我们将使用关联标识符和 DML 语句在复杂 View 上间接执行 DML 操作。然后，这些更改将反射(reflect)在该特定复杂 View 的基表中。

我的问题是应用于 View 的更改是否会对原始表格产生任何影响？

如果操作单个基表上的 View ，更改是否会反射(reflect)在基表上？

任何人都可以给我举出上述情况的例子吗？

Answer 1

在 Oracle 中，您可以在这些条件下更新 View :

来自: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.

在 SQL Server 中，您可以在 View 中插入、更新和删除行，但要遵守以下限制，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.