Updatable views and their rules

1 Preface
Multi-table View definition: When the view's data source has only one data table, the view is a single-table view, and the view is a multi-table view when the view's data source is multiple data tables.
Updatable view definition: In most people's concepts, views are read-only and are not allowed to be modified. ORACLE 8i or above, single-table view if with Read only is not set, the view is updatable, and the operation of the views is written directly to the data table.
Then, if the view's data source is multiple tables, and if the multi-table view implements an updatable view, it can greatly improve the efficiency of the encoding.

2 Multi-table updatable view Application scope
In the process of implementation, we tend to store such as product number, unit of measure, customer information, etc. in a separate data table, in the sales document, out of the inbound documents, etc., reference their primary key ID, you can point to the relevant details.
In the query view, we only need to determine the correlation relationship, but in the data entry interface, we want to implement multi-table information synchronization editing function, often encounter problems. Need to apply a variety of programming methods to achieve user needs.
The Multi-table updatable view greatly simplifies the work of the foreground programming, and for the foreground form, it can be thought that the multi-table updatable view is a complete business data table, and the storage logic of the data is built in the instead of trigger of the background view.

background implementation of 3 multi-table updatable views
Instead of Trigger for multi-table views, defining data storage logic in Trigger, implements multi-table updatable views < complex technical difficulties, and the solution is often extremely simple >. Examples are as follows:
3.1 Creating a test data table
--===================================================
--Create a test table
--===================================================
Drop Table T1;
Drop Table T2;
CREATE TABLE T1
(T11 numeric (), T12 varchar2 (20));
CREATE TABLE T2
(T11 numeric (), t22 varchar2 (20));
3.2 Multi-table View examples
--===================================================
--Create a Test view
--===================================================
Create Or Replace View T as
Select T1.t11 F1, t1.t12 F2, t2.t22 f3
From T1,t2
Where t1.t11=t2.t11;
3.3 Multi-table View trigger example
--===================================================
--Create an alternate trigger for the view
--===================================================
Create Or Replace Trigger trg_insupddel_t
Instead of Insert or update or delete
On T
For each row
Declare
Begin
If Inserting Then
Insert into T1 (t11,t12) Values (: NEW.F1,:NEW.F2);
Insert into T2 (t11,t22) Values (: NEW.F1,:NEW.F3);
Elsif Updating Then
Update T1 set t11=:new.f1,t12=:new.f2 where t11=:new.f1;
Update T2 set t11=:new.f1,t22=:new.f3 where t11=:new.f1;
Elsif Deleting Then
Delete from T1 where t11=:old.f1;
Delete from T2 where t11=:old.f1;
End if;
End
This is how the definition of a multi-table updatable view works, and you can try to test it with an INSERT or delete or update SQL statement.
3.4 Database Background considerations
When the view uses the Create Or Replace view ... After recompiling, the trigger is overwritten and cannot be found. So you remember, after recompiling the multi-table updatable view, you re-create its trigger.

4 Multi-table updatable view of the foreground implementation and considerations
4.1 Basic implementations
In the data source, the data source that defines the data block is a multi-table updatable view that enables foreground settings.
Of course, there are many things to note, otherwise people will find it difficult in the actual application process.
4.2 Form Front Desk precautions
4.2.1 Primary key, if multiple tables can be updated in the view, including the external < such as: where t1.t11=t2.t11 (+);, you must define the primary key in the form, including the primary key of the data block and the primary key property of the data item. Otherwise, the form will prompt "View does not allow updates".
4.2.2 SQL, multi-table view if you use union or distinct, the foreground form may not be able to implement the update functionality.

Updatable views have the following three rules:

(1) If the view is exported based on multiple tables using join operations, then the update operation on this view can only affect one of the tables at a time.

(2) If the view is exported with grouping and aggregation operations, the update operation is not allowed for this view.

(3) If the view is selected from a table, projected and exported, and in the view contains the table's primary key word or a candidate key, such a view is called the "column subset view." The update operation is performed on such views.

***
Some more specific descriptions of updatable views are described below.

If the view does not have a instead of trigger, or the view is not a partitioned view, the view can only be updated if the following conditions are true:

The SELECT statement does not have an aggregate function in the selection list, nor does it contain Top,group by,union (unless the view is a partitioned view) or a distinct clause. An aggregate function can be used in a subquery in the FROM clause, as long as the value returned by the function is not modified.

There are no derived columns in the select list for the SELECT statement. A derived column is a result set column consisting of any non-simple column expression (using functions, addition or subtraction operators, and so on).

The FROM clause in the SELECT statement references at least one table. The SELECT statement cannot contain only expressions that are not in a tabular format (that is, expressions that are not derived from a table).

INSERT, UPDATE, and DELETE statements must also satisfy certain restrictions, as specified above, before referencing updatable views. Update and INSERT statements can refer to a view only if the view is updatable, and the update or INSERT statement you write modifies only the data in one of the base tables referenced by the view's FROM clause. A DELETE statement can reference an updatable view only if the view references only one table in its from clause.

Updatable views and their rules