Here the view information
You can do this with updatable view but there are some restrictions.
Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.
Expect differences in behavior when working with updatable views with more than one table involved in the DELETE, INSERT, or UPDATE statements.
Updatable Views
Microsoft SQL Server 2000 enhances the class of updatable views in two ways:
INSTEAD OF Triggers: INSTEAD OF triggers can be created on a view in order to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger allows the user to specify the set of actions that need to take place in order to process the data modification statement. Thus, if an INSTEAD OF trigger exists for a view on a given data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement. For more information about INSTEAD OF triggers, see Designing INSTEAD OF triggers.
Partitioned Views: If the view is of a specified form called 'partitioned view,' the view is updatable, subject to certain restrictions. Partitioned views and their updatability are discussed later in this topic.
When needed, SQL Server will distinguish Local Partitioned Views as the views in which all participating tables and the view are on the same SQL Server, and Distributed Partitioned Views as the views in which at least one of the tables in the view resides on a different (remote) server.
If a view does not have INSTEAD OF triggers, or if it is not a partitioned view, then it is updatable only if the following conditions are satisfied:
The select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION (unless the view is a partitioned view as described later in this topic), or DISTINCT clauses. Aggregate functions can be used in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.
select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.
The FROM clause in the select_statement references at least one table. select_statement must have more than non-tabular expressions, which are expressions not derived from a table. For example, this view is not updatable:
CREATE VIEW NoTable AS
SELECT GETDATE() AS CurrentDate,
@@LANGUAGE AS CurrentLanguage,
CURRENT_USER AS CurrentUser
INSERT, UPDATE, and DELETE statements also must meet certain qualifications before they can reference a view that is updatable, as specified in the conditions above. UPDATE and INSERT statements can reference a view only if the view is updatable and the UPDATE or INSERT statement is written so that it modifies data in only one of the base tables referenced in the FROM clause of the view. A DELETE statement can reference an updatable view only if the view references exactly one table in its FROM clause.
Jaime E. Maccou
Applications Analyst
|
