Inserting into a View

You may insert rows into a view. This actually puts the rows in the table which the view references. Using the view TitlesForNewMoon from before, this will insert a row into the titles table:

insert     TitlesForNewMoon(title_id, type, title, pub_id, price,
           advance, royalty)
values     (‘PS6201’, ‘psychology’,
           ‘Seasonal Affective Disorder: Coping in the
           Caymans’, ‘0736’, $35, $1000, 12)

To insert into a view, keep the following restrictions in mind:

•  If the view does not list all of the columns in the table it references, you cannot insert into those columns, and the columns must therefore accept null values.

•  If the view performs the work of a join, make sure you are actually inserting into only one table at a time. You could not insert into ViewCA because you would need to either insert into two tables at once, or leave out values for non-nullable columns. Neither of these options is permitted.

Inserting into a View Defined WITH CHECK OPTION

The WITH CHECK OPTION parameter forces a user making inserts or updates to the view to make only changes that he can subsequently see. If the TitlesForNewMoon view were creates WITH CHECK OPTION, this would mean that inserting into the view would not allow titles to be added that were published by another publisher.

Here is an example. First, drop the TitlesForNewMoon view. Then add the view, as below, WITH CHECK OPTION:

drop    view  TitlesForNewMoon
go
create  view  TitlesForNewMoon  as
select    *
from          titles
where         pub_id = ‘0736’
with   check  option

To test this, try to add a new title to the table that is published by Five Lakes Publishing, with pub_id 1622:

insert    TitlesForNewMoon(title_id, type, title, pub_id, price,
          advance,  royalty)
values    (‘PC5450’, ‘popular_comp’, ‘500  Party
          Tricks with SQL’, ‘1622’, $20, $1000, 18)

Msg 550, Level 16, State 2
The attempted insert or update failed because the target view either
    specifies WITH CHECK OPTION or spans a view which specifies
    WITH CHECK OPTION and one or more rows resulting from the
       operation did not qualify under the CHECK OPTION constraint.
Command has been aborted.

The error message covers a lot of territory, but it boils down to this: “You tried to add a row to the view that you wouldn’t be able to see later.” If I added this row, then selected from the view, I wouldn’t see the title because TitlesForNewMoon selects only rows published by New Moon Books.

Updating a View

You may update data in tables by referencing the view, but similar restrictions apply. You may not update columns in two tables at once. With UPDATE, though, you can always run multiple UPDATES on the same row.

The view cannot group or aggregate data. These types of results are calculated, and thus do not exist as data in a table.

To update an existing row in ViewCA (which is actually one or more rows in each of the three tables), you would need to run three updates: one to update au_lname, au_id, au_fname, phone, and contract in authors, a second to update the au_ord and royaltyper columns in titleauthor, and a final update to change columns drawn from titles.

Often, it is easier (and much more direct) to update the base tables directly.

Deleting from a View

To delete from a view, the view must not perform a join. If it did, you would be trying to delete rows from two tables at once, which isn’t currently possible.

In addition, as with updates, the view cannot perform groupings, aggregates, or UNIONs.

If a view has a WITH CHECK OPTION defined, the user must be able to see the row that he is deleting.

In the following example, I delete the row from titles that I added successfully earlier in the day:

delete TitlesForNewMoon
where  title_id = ‘PS6201’

(1  row(s)  affected)

I am able to delete from the TitlesForNewMoon view because this view accesses a single table and does not include any restricted SELECT items, such as groupings. I provide the primary key for the titles table, title_id, to uniquely identify the row to be deleted.

Advanced View Examples

Consider this section “extra credit” reading. The topics I’ll discuss here are what I’d consider really neat, but aren’t critical to your understanding of T-SQL.