Restrictions on Updating Views

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 that have a definition that 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) 

You cannot insert rows into the view categories, because the group to which an inserted row would belong cannot be determined. Updates on the average_price column are not allowed, because there is no way to determine 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 that determines what types of modifications you can make through views that join columns from multiple tables. SAP ASE prohibits delete statements on multitable views, but allows update and insert statements that would not be allowed in other systems.

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 cannot be updated. Updates through a view cannot specify an IDENTITY column value.

To enable such inserts through a view, use set identity_insert on for the column’s base table. You cannot use set identity_insert on for the view through which you are inserting.