Results 1 to 2 of 2

Thread: Updateable Views

  1. #1
    Join Date
    Dec 2004
    Posts
    37

    Updateable Views

    Hi I was just wondering how can you tell when a view can be updated? thanks in advance.

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    If you're talking about SQL Server, it either has to be a partitioned view or it has to have an "instead of" trigger.

    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.


    Partitioned Views: If the view is of a specified form called 'partitioned view,' the view is updatable, subject to certain restrictions.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •