Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using a view to update data
- Thread starter masiwan
- Start date
From the Oracle Propaganda:
The rules for updatable join views are as follows:
Rule Description
General Rule
Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.
UPDATE Rule
All updatable columns of a join view must map to columns of a key-preserved table. See "Key-Preserved Tables" for a discussion of key-preserved tables. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non-updatable.
DELETE Rule
Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
INSERT Rule
An INSERT statement must not explicitly or implicitly refer to the columns of a nonkey preserved table. If the join view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.
The rules for updatable join views are as follows:
Rule Description
General Rule
Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.
UPDATE Rule
All updatable columns of a join view must map to columns of a key-preserved table. See "Key-Preserved Tables" for a discussion of key-preserved tables. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non-updatable.
DELETE Rule
Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.
INSERT Rule
An INSERT statement must not explicitly or implicitly refer to the columns of a nonkey preserved table. If the join view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.
You can also build a instead of trigger on the view. Scan for the word "INSTEAD OF" at the following link
Bill
Oracle DBA/Developer
New York State, USA
Bill
Oracle DBA/Developer
New York State, USA
- Status
Similar threads
