How to insert data into a view in Oracle _oracle application
Source: Internet
Author: User
Conditions for inserting a view:
1. If the view is based on a base table, then this is called a connectionless view, and all of the disconnected views are updatable, that is, you can do it on the view, Insert,update,delete the operation.
2. If you are connecting to a view, follow the basic update guidelines. Now I'll just do the INSERT guidelines: you cannot explicitly or implicitly refer to a field in any non-code-reserved underlying table in an INSERT statement, if the WITH CHECK option clause is used in the definition view, Then you can't insert on the view.
Note: Code Reservation table, non-code Reservation table interpretation:
In DEPT, Dept_no is the main code, the EMP, the Emp_no is the main code.
Then establish a connection view:
CREATE View emp_dept as
Select Emp.emp_no,emp.emp_name,emp.dept_no,dept.name
From Emp,dept
where Emp.dept_no=dept.dept_no
In this view, Emp_no still acts as the primary code, so the EMP is the Code Reservation table, and the Dept_no in the dept is not the main code, so it is not a code-reserved table.
To insert data into a view after multiple tables are associated, it is recommended that you use an alternate trigger
Create TRIGGER [trigger name]
On [view name]
Instead of insert
As
Begin
--declaring variables;
--To identify all the data of the columns from the inserted table and assign them to the declared variables respectively;
--insert data into the first table with the above data
--insert data into the second table with the above data
End
It is also possible to query the table or whether the view is updatable through the User_updatable_columns table.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service