suppose I have tables Employee, Locations. and I have a view viewEmpLocation which is made by joining Employee and Locations.
Now If I update the view, will data in original table get updated.
+3
A:
see Using Views in Microsoft SQL Server
When modifying data through a view (that is, using INSERT or UPDATE statements) certain limitations exist depending upon the type of view. Views that access multiple tables can only modify one of the tables in the view. Views that use functions, specify DISTINCT, or utilize the GROUP BY clause may not be updated. Additionally, inserting data is prohibited for the following types of views:
* views having columns with derived (i.e., computed) data in the SELECT-list * views that do not contain all columns defined as NOT NULL from the tables from which they were definedIt is also possible to insert or update data through a view such that the data is no longer accessible via that view, unless the WITH CHECK OPTION has been specified.
KM
2010-04-15 19:45:36
There are so many caveats on inserting through a view, that I personally find it simpler to insert directly to the tables. After all,you still have to look up what fields go to what table in order to comply with rules and if you know that there is no reason not to directly update or insert the table.
HLGEM
2010-04-16 13:42:57
I have never actually done an UPDATE or INSERT on a view, but it might benefit you if you have an indexed view that you can use to quickly locate the rows that need the change. In that case I would construct the view for the sole purpose of the update.
KM
2010-04-16 13:59:33
+2
A:
Yes.
The data "in" a view has no existence independent from the tables that make up the view. The view is, in essence, a stored SELECT statement that masquerades as a table. The data is stored in the original tables and only "assembled" into the view when you want to look at it. If the view is updateable (not all views are) the updates are applied to the table data.
Larry Lustig
2010-04-15 20:03:32
I understand your point. But a new question arises, if view is only a select statement, then How Indexes are maintained on a view. Are all the indexes get rebuilt as soon as someone queries a view.
vaibhav
2010-04-15 20:10:11
There are no indexes on views, only on the underlying tables. The indexes are used by the DBMS to assemble the rows from the different tables when you request data from the view. (This ignores a fairly new kind of view, the "materialized view" in which the database stores a "shadow" copy of the data, effectively pre-computing the view for you).
Larry Lustig
2010-04-15 20:16:50
A:
You could use a trigger on the view to do an insert/update/delete to the actual tables.
http://www.devarticles.com/c/a/SQL-Server/Using-Triggers-In-MS-SQL-Server/1/
Kyle J V
2010-04-15 20:58:50
I appreciate feedback on my answer, but if it's negative, why does nobody comment? I'd like to be corrected if wrong.
Kyle J V
2010-04-16 18:30:54