Methods in Oracle for inserting data into a view

  • 2020-06-12 10:51:55
  • OfStack

Conditions for inserting views:
1. If a view is generated based on a base table, then it is called a non-joined view. All non-joined views can be updated, that is,INSERT,UPDATE,DELETE operations can be performed on this view.
If it's a join view, then follow the basic update guideline. For now, I'll just make a note of the INSERT guideline: you cannot explicitly or implicitly refer to any field in any non-code reserved base table in an INSERT statement, and you cannot perform an INSERT operation on a view if the WITH CHECK OPTION clause is used in the definition view.
Note: Code retention table, non-code retention table interpretation:
In DEPT,DEPT_NO is the primary code; in EMP,EMP_NO is the primary code
Then establish the 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 EMP keeps the table for codes, while DEPT_NO in DEPT is not the primary code, so it keeps the table for non-codes.

Multiple tables are associated and need to insert data into the view, so an alternative trigger is recommended

create trigger [Trigger name]
on [View name]
instead of insert
as
begin
-- Declare variables;
-- Retrieve all column data from inserted table and assign each variable to the declared variable;

-- Insert data into the first table using the data above
-- Use the above data to insert data into the second table
end

In addition, user_updatable_columns table can be used to query whether the table or view is updatable.

Related articles: