Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 9 Views: Limiting Access to Data

Modifying data through views

Although Adaptive Server places no restrictions on retrieving data through views, and although Transact-SQL places fewer restrictions on modifying data through views than other versions of SQL, the following rules apply to various data modification operations:

update, insert, or delete operations that refer to a computed column or a built-in function in a view are not allowed.
update, insert, or delete operations that refer to a view that includes aggregates or row aggregates are not allowed.
insert, delete, and update operations that refer to a distinct view are not allowed.
insert statements are not allowed unless all NOT NULL columns in the underlying tables or views are included in the view through which you are inserting new rows. Adaptive Server has no way to supply values for NOT NULL columns in the underlying objects.
If a view has a with check option clause, all rows inserted or updated through the view (or through any derived views) must satisfy the view's selection criteria.
delete statements are not allowed on multitable views.
insert statements are not allowed on multitable views created with the with check option clause.
update statements are allowed on multitable views where with check option is used. The update fails if any of the affected columns appears in the where clause, in an expression that includes columns from more than one table.
insert and update statements are not allowed on multitable distinct views.
update statements cannot specify a value for an IDENTITY column. The table owner, Database Owner, or a System Administrator can insert an explicit value into an IDENTITY column after setting identity_insert on for the column's base table.
If you insert or update a row through a multitable view, all affected columns must belong to the same base table.
writetext is not allowed on the text and image columns in a view.

When you attempt an update, insert, or delete for a view, Adaptive Server checks to make sure that none of the above restrictions is violated and that no data integrity rules are violated.

Restrictions on updating views

Restrictions on updated views apply to these areas:

Computed columns in a view definition
group by or compute in a view definition
Null values in underlying objects
Views created using with check option
Multitable views
Views with IDENTITY columns

Computed columns in a view definition

This restriction applies to columns of views that are derived from computed columns or built-in functions. For example, the amt_due column in the view accounts is a computed column.

create view accounts (title_id, advance, amt_due) 
as select titles.title_id, advance, 
(price * royalty/100) * total_sales 
from titles, roysched 
where price > $15 
  and advance > $5000 
and titles.title_id = roysched.title_id 
and total_sales between lorange and hirange

The rows visible through accounts are:

select * from accounts

title_id     advance     amt_due 
--------     --------    --------- 
PC1035       7,000.00    32,240.16 
PC8888       8,000.00     8,190.00 
PS1372       7,000.00       809.63 
TC3218       7,000.00       785.63 
 
(4 rows affected)

updates and inserts to the amt_due column are not allowed because there is no way to deduce the underlying values for price, royalty, or year-to-date sales from any value you might enter in the amt_due column. delete operations do not make sense because there is no underlying value to delete.

group by or compute in a view definition

This restriction applies to all columns in views that contain aggregate values--that is, views whose definition includes a group by or compute clause. Here is a view defined with a group by clause and the rows seen through it:

create view categories (category, average_price) 
as select type, avg(price) 
from titles 
group by type

select * from categories

category         average_price 
-------------    ------------- 
UNDECIDED                 NULL 
business                 13.73 
mod_cook                 11.49 
popular_comp             21.48 
psychology               13.50 
trad_cook                15.96 
 
(6 rows affected)

It does not make sense to insert rows into the view categories. To what group of underlying rows would an inserted row belong? Updates on the average_price column are not allowed because there is no way to know from any value you might enter there how the underlying prices should be changed.

NULL values in underlying objects

This restriction applies to insert statements when some NOT NULL columns are contained in the tables or views from which the view is derived.

For example, suppose null values are not allowed in a column of a table that underlies a view. Normally, when you insert new rows through a view, any columns in underlying tables that are not included in the view are given null values. If null values are not allowed in one or more of these columns, no inserts can be allowed through the view.

For example, in this view:

create view business_titles 
as select title_id, price, total_sales 
from titles 
where type = "business"

Null values are not allowed in the title column of the underlying table titles, so no insert statements can be allowed through business_view. Although the title column does not even exist in the view, its prohibition of null values makes any inserts into the view illegal.

Similarly, if the title_id column has a unique index, updates or inserts that would duplicate any values in the underlying table are rejected, even if the entry does not duplicate any value in the view.

Views created using with check option

This restriction determines what types of modifications you can make through views with check options. If a view has a with check option clause, each row inserted or updated through the view must be visible within the view. This is true whether you insert or update the view directly or indirectly, through another derived view.

Multitable views

This restriction determines what types of modifications you can make through views that join columns from multiple tables. Adaptive Server prohibits delete statements on multitable views, but allows update and insert statements that would not be allowed in other systems.

You can insert or update a multitable view if:

The view has no with check option clause.
All columns being inserted or updated belong to the same base table.

For example, consider the following view, which includes columns from both titles and publishers and has no with check option clause:

create view multitable_view
as select title, type, titles.pub_id, state
from titles, publishers
where titles.pub_id = publishers.pub_id

A single insert or update statement can specify values either for the columns from titles or for the column from publishers:

update multitable_view
set type = "user_friendly"
where type = "popular_comp"

However, this statement fails because it affects columns from both titles and publishers:

update multitable_view
set type = "cooking_trad",
state = "WA"
where type = "trad_cook"

Views with IDENTITY columns

This restriction determines what types of modifications you can make to views that include IDENTITY columns. By definition, IDENTITY columns are not updatable. Updates through a view cannot specify an IDENTITY column value.

Inserts to IDENTITY columns are restricted to:

The table owner
The Database Owner or the System Administrator if the table owner has granted them permission
The Database Owner or the System Administrator if impersonating the table owner by using the setuser command.

To enable such inserts through a view, use set identity_insert on for the column's base table. It is not sufficient to use set identity_insert on for the view through which you are inserting.


	Expand Search