updating view with SP

Question

updating view with SP

smknox

Hall of Fame

Points: 3275
More actions
November 16, 2004 at 2:31 pm

#86936

easy newbie question:
I am writing a SP to update a complex view. Do I update each underlying
table individually or can I update the view itself? The FE is Access and I
know that views are for all practical purposes not updateable from Access. So
if I have 5 or 6 tables involved, it doesn't make sense to me that the view
is updateable from SQL when it's not in Access.
Thanks for the help!
I don't want to use an "instead of" event on the view, or a trigger.
--

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 1

In SQL Server, some views are updatable. Here are some of the rules (quoted from Books Online, topic "CREATE VIEW", paragraph "Updatable views"):

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.

Razvan