Can a view be updated/inserted/deleted? If Yes under what conditions ?

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

Showing Answers 1 - 12 of 12 Answers


  • Nov 27th, 2005

it is possible through instead of triggers

  Was this answer useful?  Yes

Archana Patil

  • Dec 20th, 2005

View can be update/insert/delete if it contain fields of one table, and 1.primary key should contain in select clause used for view

2. but again if select stmt used in view conatins group by caluse then we cant update view.

3. If select caluse contains more than one tables then we can use instead of trigger for dml operations.

  Was this answer useful?  Yes


  • Mar 24th, 2006

if a view is update, deleted , or inserted wil the changes be refelected on the base table

  Was this answer useful?  Yes

Lavanya Chowdary

  • May 6th, 2007

If the view is a simple view means if it is created based on single table with simple select statement.

If the view is a complex view means if it is created based on multiple tables some times it is not possible to update complex views. We had some restrictions for the DML operations on a complex view.
Whatever the updations that are not possbile with the normal DML statements that will be updated through instead of triggers.

  Was this answer useful?  Yes


  • Mar 29th, 2008

updating a view means nothing but updating the base tables on which  that view is built.If the view id created from a single table.then it is possible to insert,update,delete through the view.Conversly,if a view is created by two or more tables,then in order to perform insert.update and delete operations through that view,we need to write insteadof trigger for insert,update and delete respectively.

  Was this answer useful?  Yes


  • Apr 9th, 2008

hi friends
As ur discussions we can perform dml operations on simple view ,
But ,we cannot do any dmls on complex views at that time we can use
instead of triggers then we can do the dmls on any views.

  Was this answer useful?  Yes

Take an example
Table : Employee(id number primary key,name varchar2(20),departmentid number)

If created view in such a fashion, it includes all Not Null attribues & it does not contains group by and disctinct key word, then view is updatable

Updatab;e/insertable view : create view updatable_view_emp as select id,name from employee.

Non updatable: create view nonupdatable_view_emp as select name from employee.
                          create view nonupdatble_view as select from employee   
                                       group   by departmentid;

  Was this answer useful?  Yes


  • Apr 10th, 2015

Well Archana i tried the about queries of view but your first statement violates the condition i can make a view without the parent table in the view being primary key

  Was this answer useful?  Yes

Maheswara Raju. Varakavi

  • Jun 5th, 2015

If a view is updated, deleted , or inserted will the changes be reflected on the base table?
YES--> If it is in the case of simple view (which consists only one base table).
NO---->If it is in the case of complex view( which consists multiple base tables, and joins).
However, there is an indirect way of performing DML operations on Complex views, by using "Instead Of trigger". In the body of Instead of trigger we will use co-relational identifiers, and DML statements to perform DML operations indirectly on the complex view. Then that changes will reflect in base tables of that particular complex view.

  Was this answer useful?  Yes

Mahendra K Garnayak

  • Jun 7th, 2016

Thanks M.R. Varakavi for providing a valuable information.
could you please confirm in instead of trigger action will be performed on which table in case of join condition

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account

Send   Reset


Related Answered Questions


Related Open Questions