In Oracle, is it possible to INSERT or UPDATE a record (a row) through a view?
You can insert, update, and delete rows in a view, subject to the following limitations: If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows. You can't directly modify data in views based on union queries.
Yes, you can insert, update and delete a record in a view but there are some restrictions. Use the following procedure to create a sample to understand how to perform such tasks. Step 1: Create a schema of a table named "Employee" in your Database.
When you update a view oracle will update the underlying base table. You can use DML operations on a view if the view does not include Group by, Start with, Connect by, Distinct clauses or any subqueries and set operations(Union, union all, intersect and minus).
DML operations could be performed through a simple view. DML operations could not always be performed through a complex view.
Views in Oracle may be updateable under specific conditions. It can be tricky, and usually is not advisable.
From the Oracle 10g SQL Reference:
Notes on Updatable Views
An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.
To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:
In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions.
If you want a join view to be updatable, then all of the following conditions must be true:
Oracle has two different ways of making views updatable:-
I would stay away from instead-of triggers and get your code to update the underlying tables directly rather than through the view.
There are two times when you can update a record through a view:
Generally, you should not rely on being able to perform an insert to a view unless you have specifically written an INSTEAD OF trigger for it. Be aware, there are also INSTEAD OF UPDATE triggers that can be written as well to help perform updates.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With