1.5  更新视图

视图是一个虚拟的表，当你查询视图时，SQL Server会展开视图的select语句并对基础表执行查询。视图不仅可以作为SELECT查询的目标，它也可以作为修改语句的目标。当你修改视图时，SQL Server将修改基础表。这时的视图就像是一个代理或媒介。当然，如果不允许直接修改基础表，只允许修改视图，就可以限制你要公开的数据。这样，在保障隐私及资料披露方面，视图也可以起到一定程度的保护作用。

例如，实现行级安全（row-level security）的方法之一就是使用视图。

代码清单5-3中的代码用于创建包含loginname列的UserData表，该列接收SUSER_SNAME函数（当前登录名称）的返回值作为默认值。代码创建了一个视图，该视图只向当前用户公开该用户的除loginname之外的所有属性，使用的筛选器是loginname = SUSER_SNAME()。代码拒绝public用户对该表的数据修改语言（DML）的权限，但授予public操作视图的权限。通过使用这些措施，用户就可以只访问和修改他自己的数据了。

代码清单5-3  创建UserData 和 VuserData并设置权限

USE tempdb;

GO

IF OBJECT_ID(‘dbo.VuserData’) IS NOT NULL

  DROP VIEW dbo.VuserData;

GO

IF OBJECT_ID(‘dbo.UserData’) IS NOT NULL

  DROP TABLE dbo.UserData;

GO

CREATE TABLE dbo.UserData

(

  keycol    INT         NOT NULL IDENTITY PRIMARY KEY,

  loginname sysname     NOT NULL DEFAULT (SUSER_SNAME()),

  datacol   VARCHAR(20) NOT NULL,

  /* ... 其他列... */

);

GO

CREATE VIEW dbo.VuserData

AS

SELECT keycol, datacol

FROM dbo.UserData

WHERE loginname = SUSER_SNAME()

GO

DENY SELECT, INSERT, UPDATE, DELETE ON dbo.UserData TO public;

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.VuserData TO public;

GO

修改视图时具有下列限制：

n  只要视图有一列不能隐式获取值，你就不能向视图插入数据。如果列允许NULL、有默认值、设置了IDENTITY属性或类型为ROWVERSION，则说明它可以隐式地获取值。

n  如果视图由一个联接查询（join query）定义，UPDATE或INSERT语句只能影响联接的一端。也就是说，INSERT语句必须定义目标列列表，这些列只能属于联接的一端。同样，UPDATE语句修改的列也必须都属于联接的一端。但是，你可以在查询的其他地方（如赋值语句的右端、查询筛选器等）引用任何列。你不能从由联接查询定义的视图中删除数据。

n  不能修改作为计算结果的列。标量表达式和聚合（aggregate）。SQL Server不会尝试改变数据库引擎的计算结果。

n  如果在创建或修改视图时指定了WITH CHECK OPTION选项，与视图的查询筛选器有冲突的INSERT或UPDTE语句将被拒绝。我将在“视图选项”一节详细描述这一点。

如果视图上定义了INSTEA OF触发器，则违反这些限制的数据修改语句可以被执行。在INSTEAD OF触发器中你可以用自己的代码替换原始修改。例如，你可以用自己的代码替换对计算列的修改并对基础表直接执行修改。我将在第8章讨论触发器。

当你允许对由联接查询定义的视图执行修改时要特别谨慎。有些用户，他们不知道自己修改的目标对象是视图而不是表，可能会发现有时候他们的操作结果不可思议。例如，当他们修改一对多（one-to-many）联接中的“一”这一端。

运行代码清单5-4中的代码示例，创建Customers 和 Orders表以及联接这两个表的视图VcustOrders。

代码清单5-4  创建Customers, Orders, 和 VcustOrders

SET NOCOUNT ON;

USE tempdb;

GO

IF OBJECT_ID(‘dbo.VcustOrders’) IS NOT NULL

  DROP VIEW dbo.VcustOrders;

GO

IF OBJECT_ID(‘dbo.Orders’) IS NOT NULL

  DROP TABLE dbo.Orders;

GO

IF OBJECT_ID(‘dbo.Customers’) IS NOT NULL

  DROP TABLE dbo.Customers;

GO

CREATE TABLE dbo.Customers

(

  cid   INT         NOT NULL PRIMARY KEY,

  cname VARCHAR(25) NOT NULL,

  /*其他列*/

)

INSERT INTO dbo.Customers(cid, cname) VALUES(1, ‘Cust 1’);

INSERT INTO dbo.Customers(cid, cname) VALUES(2, ‘Cust 2’);

CREATE TABLE dbo.Orders

(

  oid INT NOT NULL PRIMARY KEY,

  cid INT NOT NULL REFERENCES dbo.Customers,

  /* 其他列 */

)

INSERT INTO dbo.Orders(oid, cid) VALUES(1001, 1);

INSERT INTO dbo.Orders(oid, cid) VALUES(1002, 1);

INSERT INTO dbo.Orders(oid, cid) VALUES(1003, 1);

INSERT INTO dbo.Orders(oid, cid) VALUES(2001, 2);

INSERT INTO dbo.Orders(oid, cid) VALUES(2002, 2);

INSERT INTO dbo.Orders(oid, cid) VALUES(2003, 2);

GO CREATE VIEW dbo.VcustOrders

AS

SELECT C.cid, C.cname, O.oid

FROM dbo.Customers AS C

  JOIN dbo.Orders AS O

ON O.cid = C.cid;

GO

查询该视图，并检查它的内容，内容如表5-9所示。

SELECT cid, cname，oid FROM dbo.VCustOrders；

表5-9  VcustOrders的内容  

cid	cname	oid
1	Cust 1	1001
1	Cust 1	1002
1	Cust 1	1003
2	Cust 2	2001
2	Cust 2	2002
2	Cust 2	2003

注意其中消费者的属性，比如公司名称（cname），每个订单都有重复。

假设用户拥有该视图的UPDATE权限，他想把订单ID（oid）等于1001的公司名称修改为‘Cust 42’。该用户会提交下面的更新。

UPDATE dbo.VcustOrders

  SET cname = ‘Cust 42’

WHERE oid = 1001;

当然，如果更新的目标是表而不是视图，查询该表时你只能看到有一行cname列为Cust 42。然而更新的目标是视图，SQL Server会修改该视图后面的 Customers表。实际上，修改的是消费者1的cname数据。然后用下面的代码查询VcustOrders视图并检查表5-10显示的输出。

SELECT cid, cname, oid FROM dbo.VcustOrders;

表5-10  更新VcustOrders后的内容

cid	cname	oid
1	Cust 42	1001
1	Cust 42	1002
1	Cust 42	1003

续表

cid	cname		oid
2	Cust 2	2001
2	Cust 2	2002
2	Cust 2	2003

订单1001的cname值被更改。视图中订单1001的cname值其实是订单1001的消费者（消费者的序号为1，该值也保存在Orders表中）在Customers中的值。该视图为消费者序号为1的所有3个订单返回消费者序号为1的cname值。

完成后，运行下面的代码进行清理：

USE tempdb;

GO

IF OBJECT_ID(‘dbo.VuserData’) IS NOT NULL

  DROP VIEW dbo.VuserData;

GO

IF OBJECT_ID(‘dbo.UserData’) IS NOT NULL

  DROP TABLE dbo.UserData;

GO

IF OBJECT_ID(‘dbo.VcustOrders’) IS NOT NULL

  DROP VIEW dbo.VcustOrders;

GO

IF OBJECT_ID(‘dbo.Orders’) IS NOT NULL

  DROP TABLE dbo.Orders;

GO

IF OBJECT_ID(‘dbo.Customers’) IS NOT NULL

  DROP TABLE dbo.Customers;

GO