The definition of a multiple-table view is a single table view when the data source for the view has only one datasheet, and the view is a multiple-table view when the data source of the view is multiple data tables.
Updatable view definition: In the vast majority of people's concept, views are read-only and are not allowed to be modified. ORACLE 8i and above, a single table view if you do not set with Read only, the view is updatable and the action of the views will be written directly to the datasheet.
Then, if the view's data source is more than one datasheet, and if the multiple table view implements an updatable view, it can greatly increase the efficiency of the encoding.
1 Preface
The definition of a multiple-table view is a single table view when the data source for the view has only one datasheet, and the view is a multiple-table view when the data source of the view is multiple data tables.
Updatable view definition: In the vast majority of people's concept, views are read-only and are not allowed to be modified. ORACLE 8i and above, a single table view if you do not set with Read only, the view is updatable and the action of the views will be written directly to the datasheet.
Then, if the view's data source is more than one datasheet, and if the multiple table view implements an updatable view, it can greatly increase the efficiency of the encoding. 2 Multi-table updatable view Application scope
In the process of implementation, we often will be such as product number, unit of measurement, customer information, etc. stored in a separate data table, in sales documents, out of storage documents, and other places, reference to their primary key ID, you can point to the relevant details.
In the query view, we only need to determine the relationship can be, but in the data entry interface, we have to implement the multiple table information synchronization editing function, often encounter problems. Need to apply a variety of programming methods to achieve user needs.
Multi-table updatable view greatly simplifies the workload of foreground programming, and for the foreground form, you can assume that the multiple-table updatable view is a complete business data table, while the stored logic of the data is built into the instead of triggers in the background view. Background implementation of more than 3 table updatable views
By establishing the instead of Trigger of the multiple table view and defining the data storage logic in Trigger, the multiple table updatable views < Complex technical difficulties are realized, and the solutions are often very simple >. Examples are as follows:
3.1 Creating test data tables
--===================================================
--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 Multiple table View triggers 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 the definition of a multiple-table updatable view, and you can try using an INSERT or delete or update SQL statement to test it.
3.4 Database Background considerations
When the view is using create Or Replace view ... After recompiling, the trigger is overwritten and cannot be found. So you remember to re-create the trigger after you recompile the multiple tables to update the view. 4 Multi-table updatable view foreground implementation and precautions
4.1 Basic Implementation
In the data source, the data source that defines the data block is a multiple table updatable view, can realize the foreground setting.
Of course, there are many things to note, otherwise we will find it difficult in the actual application process.
4.2 Form Front Desk considerations
4.2.1 primary keys, if multiple tables can be updated in the view, including outreach < 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 prompts the view not to allow updates.
4.2.2 SQL, multiple table views if you use union or distinct, the foreground form may not implement the update feature. |
