SQL Server Performance

Updating computed columns via a view

Discussion in 'SQL Server 2005 General Developer Questions' started by rogilvie, Apr 8, 2008.

  1. rogilvie New Member

    Hi,
    I have created a table which contains several standard columns and one computed column. What I'm trying to do is create a view on top of the table (select *) which will allow other users to update / insert into the underlying table. However, I'm having difficulty as the view recognises the computed column as a column which also requires a value to be inputted.
    Beyond changing the initial select statement in the view creation step to an explicit column list (minus the computed column), is there a way around this?
    Thanks
    e.g.
    CREATE TABLE test_table (
    c1 int NULL,
    c2 int NOT NULL,
    c3 as c1 + c2
    )
    go
    CREATE VIEW test_view
    AS
    SELECT * FROM test_table
    GO
    INSERT INTO test_table
    VALUES (2,2)
    -- (1 row(s) affected)
    GO
    INSERT INTO test_view
    VALUES (2,2)
    -- Server: Msg 213, Level 16, State 4, Line 1
    -- Insert Error: Column name or number of supplied values does not match table definition.
    rogilvie, Apr 8, 2008
    #2

  2. Luis Martin Moderator

    Expressions and Computed Columns in INSTEAD OF Triggers

    The select list of a view can have expressions other than simple expressions made up of only a column name. INSTEAD OF triggers on these views must have logic to correctly determine from the values specified on INSERT and UPDATE what values must be set into columns in the base table. Examples of such expressions include:
    • View expressions that do not map to any column in any table, such as a constant or some types of functions.
    • View expressions that map to multiple columns, such as complex expressions formed by concatenating strings from two or more columns.
    • View expressions that transform the value of a single base table column, such as referencing a column in a function.
    These issues also apply to view columns that are simple expressions referencing a computed column in a base table. The expression defining the computed column can have the same form as a more complex expression in the view select list.
    Luis Martin, Apr 8, 2008
    #3

  3. Adriaan New Member

    Have you tried specifying the target column names?
    INSERT INTO test_view (c1, c2)
    VALUES (2,2)
    You will have to pay attention to basic syntax: this is not an update, but an insert.[:)]
    Adriaan, Apr 8, 2008
    #4

  4. Luis Martin Moderator

    Hi Adriaan,
    I get :
    Server: Msg 4406, Level 16, State 1, Line 1
    Update or insert of view or function 'test_view' failed because it contains a derived or constant field.
    When I used your insert.
    I believe the problem is how c3 was defined.
    Luis Martin, Apr 8, 2008
    #5

  5. jagblue New Member

    Hi
    Try to create table like this
    CREATE TABLE test_table (
    c1 int NULL,
    c2 int NOT NULL,
    c3 as c1 + c2 PERSISTED
    )
    and then do
    INSERT VIEW(c1,c2) VALUES(2,2)
    Thanks
    jagblue, Apr 8, 2008
    #6

  6. Adriaan New Member

    Also consider dropping the calculated column, and doing the calculation in the view.
    Adriaan, Apr 11, 2008
    #7

  7. Madhivanan Moderator

    [quote user="Adriaan"]
    Also consider dropping the calculated column, and doing the calculation in the view.
    [/quote]
    I too prefer doing this in the select statement or in a view than in computed column
    I would use it If I want users not to add data in a table
    Madhivanan, Apr 11, 2008
    #8

  8. Adriaan New Member

    [quote user="Madhivanan"]
    [quote user="Adriaan"]
    Also consider dropping the calculated column, and doing the calculation in the view.
    [/quote]
    I too prefer doing this in the select statement or in a view than in computed column
    I would use it If I want users not to add data in a table
    [/quote]Madhivanan,
    There are better options to prevent users from adding data, like not granting INSERT permission, or even denying INSERT permission.
    Adriaan, Apr 11, 2008
    #9

Share This Page