• Mighty (5/8/2013)


    It also might be because it is still early here, but what is the point exactly?

    The view definition is such that...

    . What is it exactly why it works? The fact that in the view only one table is used or...?

    Hugo's explanation mostly covers it. In addition to what Hugo says, you need to know that MS SQL Server has a rule that delete or insert on a view which involves more than one table can't be done except by an instead of trigger which directly accesses the base tables - so if an instead of delete (or instead of insert) trigger on a view does a delete (or insert) on that view it must not be a view which involves more than one table. According to BOL this applies to all views involving more than one table, even union views (where there is no good reason to have such a rule). So yes, the fact that only one table is used is an important part of the reason it works, as you suggested.

    Of course if two tables are used in a join view, delete will almost always be ambiguous, so it makes sense to insist on a trigger to say what is to be done to the base tables.

    Not all single table views can be deleted directly - think of a view like this one (which is covered in Hugo's explanation)

    create view zv on tab1 as

    select tab1.x, count(*) as k from tab1 group by tab1.x

    go

    Now suppose you get a command "delete from zv where k = 3" and there's a row 'abcde',3 in tab1; which of the three rows in tab1 with 'abcde' in the x column should you delete? You don't know, so there has to be an instead of trigger which tells the system which base table row(s) to delete. So using only one table is only part of the story.

    Tom