How to determine if a column of a view is derived

问题:

Let's say I have the following table:

create table t_Item (
    ItemID int not null identity(1,1) constraint PK_Item primary key,
    Description varchar(256) not null,
    Price decimal(10,2) not null
)

and the following view:

create view Item as
  select ItemID
        ,Description
        ,Price
        ,1.09 Tax
        ,Price * 1.09 TaxedPrice
    from t_Item

TaxedPrice is a derived column, and Tax is a constant column.

Therefore, I can't insert or update any of them. The first following query would pass, whereas the other ones would fail with an error.

insert into Item (Description, Price) values ('Test item', 14.00)

insert into Item (Description, Price, TaxedPrice) values ('Test item', 14.00, 15.26)

insert into Item (Description, Price, Tax) values ('Test item', 14.00, 1.09)

And here is the returned error message:

Update or insert of view or function 'Item' failed because it contains a derived or constant field.

Is there a way, maybe with the system views, to list the view columns which must not be updated?

回答1:

Looks like there is no system view saves information you are looking for. You can find out derived column or constant column by parsing the view definition or by exception handling...not good, but didn't find other ways...

回答2:

1. Any columns come from the following aggregation functions, considered as derived column. AVG, COUNT,SUM,MIN,MAX,GROUPING,STDEV,STDEVP,VAR,VARP
2. If the view definition contains following syntax, all columns considered as derived column. UNION, UNION ALL, CROSSJOIN, EXCEPT, INTERSECT
3. The columns affected by GROUP BY, HAVING, DISTINCT, are also considered as derived column.

I don't think this cover all scenarios, but a start point to write the parser.