Post reply

Determine if a view is updatable?

  • January 13, 2005 at 10:02 pm

    #61518

    Is there a way to determine if a view is updatable (before attempting INSERT/UPDATE/DELETE and having the error occur)? 

    I was hoping to find something like

    OBJECTPROPERTY(OBJECT_ID('my_view'),'IsUpdatable')

    but that property doesn't exist.

    Any other ideas or suggestions on how to do this?

  • January 14, 2005 at 4:54 am

    #537436

    AFAIK, there is no easy way to determine if a view is updateable or not.

    The first 'problem' is - what is an updateable view? There may be several different definitions to this, depending on the decided rules.

    eg - you update through a view, and the update then causes this piece of data to not be seen anymore through the view. If the view is compiled with WITH CHECK OPTION this particular effect will be disallowed, while other updates will be allowed. Should the view be considered updateable or not in this case?

    The view DDL might itself look a certain way that makes it impossible to update through it - but.. there may exist an INSTEAD OF TRIGGER on the view that will make it updateable anyway.

    If it is a partitioned view, it may, or may not be updatable.

    If it is a 'normal' view, it may, or may not be updatable depending on a number of things..

    -- short snip from BOL --

    If a view does not have INSTEAD OF triggers, or if it is not a partitioned view, then it is updateable 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.
    • 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.

    -- end BOL --

    As you can see it seems a daunting task to determine this 'on the fly' so to speak. I think that the best thing is to only attempt modifications (insert/update/delete) on views that are purposefully designed and tested for it, not otherwise.

    /Kenneth

     

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

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