If I update a view, will my original tables get up

问题:

Hypothetically I have two tables Employee and Locations. Additionaly I have a view viewEmpLocation which is made by joining Employee and Locations.

If I update the view, will the data in the original table get updated?

回答1:

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 defined

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

回答2:

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.

回答3:

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/