pooja

• Nov 27th, 2005

 

it is possible through instead of triggers

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.

CH

• Mar 24th, 2006

 

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

sbagai2001

 Profile Answers by sbagai2001 Questions by sbagai2001

• May 31st, 2006

 

Can you please give me an example of this

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 8th, 2006

 

yes possible with only simple view

possible with compex views with instead trigger

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.

sandeep5580

 Profile Answers by sandeep5580 

• 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.

venkat82.k

 Profile Answers by venkat82.k 

• 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.
okkkkkkkkkkkkk

ravisavaliya

 Profile Answers by ravisavaliya 

• Apr 22nd, 2008

 

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 id.name from employee   
                                       group   by departmentid;

Arjunsingh

• 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

Maheswara Raju. Varakavi

• Jun 5th, 2015

 

@CH,
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.

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

Maheswara Raju. Varakavi

• Aug 29th, 2016

 

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.

Code
SELECT        dbo.Customer_Mas.CustomerId, dbo.Customer_Mas.CustomerName, dbo.CustomerGroup_Mas.CustomerGroupId, dbo.CustomerGroup_Mas.GroupName, 


                         dbo.BlockQuantityMaster.CustomerId AS Expr1, dbo.BlockQuantityMaster.CustomerGroupId AS Expr2, dbo.BlockQuantityMaster.ProductCategoryID, 


                         dbo.BlockQuantityMaster.ProductID, dbo.BlockQuantityMaster.BrandNewQty, dbo.BlockQuantityMaster.ReplacementQty, dbo.Product.ProductID AS Expr3, 


                         dbo.Product.ProductName


FROM            dbo.BlockQuantityMaster INNER JOIN


                         dbo.Product ON dbo.BlockQuantityMaster.ProductID = dbo.Product.ProductID LEFT OUTER JOIN


                         dbo.Customer_Mas ON dbo.BlockQuantityMaster.CustomerId = dbo.Customer_Mas.CustomerId LEFT OUTER JOIN


                         dbo.CustomerGroup_Mas ON dbo.BlockQuantityMaster.CustomerGroupId = dbo.CustomerGroup_Mas.CustomerGroupId


GROUP BY dbo.Customer_Mas.CustomerId, dbo.Customer_Mas.CustomerName, dbo.CustomerGroup_Mas.CustomerGroupId, dbo.CustomerGroup_Mas.GroupName, 


                         dbo.BlockQuantityMaster.CustomerId, dbo.BlockQuantityMaster.CustomerGroupId, dbo.BlockQuantityMaster.ProductCategoryID, dbo.BlockQuantityMaster.ProductID, 


                         dbo.BlockQuantityMaster.BrandNewQty, dbo.BlockQuantityMaster.ReplacementQty, dbo.Product.ProductID, dbo.Product.ProductName