In Oracle is it possible to INSERT or UPDATE a record through a view

Question

In Oracle  is it possible to INSERT or UPDATE a record  a row  through a view

User · Answer

There are two times when you can update a record through a view:

If the view has no joins or procedure calls and selects data from a single underlying table.
If the view has an INSTEAD OF INSERT trigger associated with the 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.

User · Answer

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    Each column in the view must map to a column of a single table  For example  if a view column maps to the output of a TABLE clause  an unnested collection   then the view is not inherently updatable  The view must not contain any of the following constructs    A set operator a DISTINCT operator An aggregate or analytic function A GROUP BY  ORDER BY  MODEL  CONNECT BY  or START WITH clause A collection expression in a SELECT list A subquery in a SELECT list A subquery designated WITH READ ONLY Joins  with some exceptions  as documented in Oracle Database Administrator s Guide    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    The DML statement must affect only one table underlying the join  For an INSERT statement  the view must not be created WITH CHECK OPTION  and all columns into which values are inserted must come from a key-preserved table  A key-preserved table is one for which every primary key or unique key value in the base   table is also unique in the join view  For an UPDATE statement  all columns updated must be extracted from a key-preserved table  If the view was created WITH CHECK OPTION  then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE  For a DELETE statement  if the join results in more than one key-preserved table  then Oracle Database deletes from the first table named in the FROM clause  whether or not the view was created WITH CHECK OPTION

User · Answer

Oracle has two different ways of making views updatable:-

The view is "key preserved" with respect to what you are trying to update. This means the primary key of the underlying table is in the view and the row appears only once in the view. This means Oracle can figure out exactly which underlying table row to update OR
You write an instead of trigger.

I would stay away from instead-of triggers and get your code to update the underlying tables directly rather than through the view.

User · Answer

YES  you can Update and Insert into view and that edit will be reflected on the original table     BUT 1-the view should have all the NOT NULL values on the table 2-the update should have the same rules as table     updating primary key related to other foreign key   etc

[sql] In Oracle, is it possible to INSERT or UPDATE a record through a view?

The answer is

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to views

Tags