Unlocking Power with Updatable Views in SQL Server

  • Comments posted to this topic are about the item Unlocking Power with Updatable Views in SQL Server

  • Instead of views, I use a lot TVF and CTE, the limits are the same as for views.

    Here an example:

    create table a (i int)
    create function myfn_a(@i int)
    returns table
    WHERE a.i = @i
    INSERT INTO myfn_a(NULL)
    output inserted.*
    select 1

    update myfn_a(1) SET
    i += 1
    output inserted.*

    delete myfn_a(2)
    output deleted.*

  • My primary use for updateable views is for bulk inserts into tables with more columns than the source files have. The views' columns match the source files' columns. BULK INSERT is oblivious to the extra columns, including the identity columns which are updated in the process.

    I did have one project some years ago with a view based on three underlying tables tied to a UI with inserts, updates and deletes. This was on a SQL Server 2008 R2 server. In that project I created INSTEAD OF triggers to handle all of the DML operations because I was under the impression that they were necessary at the time. However, my read of this article suggests that the DML operations occur automatically without triggers. Is this true?

  • The view used to create the view left out the age column, therefore the insert statements that include age should fail.

  • While I do agree that INSERT statements using the view SHOULD fail (because I believe age is important in this context), it WON'T fail because the age column allows NULL values.

    I would only leave out of views that are meant for DML operations those columns with default values and/or identity values.

Viewing 5 posts - 1 through 4 (of 4 total)

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