如何确定视图的列是派生的还是常量?

2021-09-17 00:00:00 sql-server view system-views

假设我有下表:

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
)

以及以下视图:

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

TaxedPrice 是派生列，Tax 是常量列.

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)

这是返回的错误信息:

更新或插入视图或函数Item"失败，因为它包含派生字段或常量字段.

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?

推荐答案

看起来没有系统视图保存您要查找的信息.可以通过解析视图定义或异常处理来找出派生列或常量列...不好，但没有找到其他方法...

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...

相关文章