目录

Views

Declaring Views

Example: View Definition

Example: Accessing a View

Advantages of Views

Triggers on Views

Interpreting a View Insertion（视图插入操作的解释）

The Trigger

Views

A view is a relation defined in terms of stored tables (called base tables ) and other views.（视图是一种储存表和其他视图之间的一种关系）

Two kinds:

• Virtual = not stored in the database; just a query for constructing the relation.

• Materialized = actually constructed and stored.

可以将视图分为两种形式：虚拟视图（并没有真实存在，只是构造关系的查询语句）、物化视图

Declaring Views

CREATE [MATERIALIZED] VIEW
<name> AS <query>;

Default is virtual.（缺省值是虚拟的）

Example: View Definition

CREATE VIEW CanDrink AS
SELECT drinker, beer
FROM Frequents, Sells
WHERE Frequents.bar = Sells.bar;

SELECT beer FROM CanDrink
WHERE drinker = ’Sally’;

Triggers on Views

• Generally, it is impossible to modify a virtual view, because it doesn’t exist.（通常情况下，修改视图是不可能的，因为视图并不存在）

• But an INSTEAD OF trigger lets us interpret view modifications in a way that makes sense.（但是可以通过建立触发器，使得对于视图的操作能够映射到对应的基表中）

• Example:

View Synergy has (drinker, beer, bar) triples such that the bar serves the beer, the drinker frequents the bar and likes the beer.

Interpreting a View Insertion（视图插入操作的解释）

1. We cannot insert into Synergy --- it is a virtual view.

2. But we can use an INSTEAD OF trigger to turn a (drinker, beer, bar) triple into three insertions of projected pairs, one for each of Likes, Sells, and Frequents.（可以使用触发器将视图投影成三个数对，对应三张表）

3. Sells.price will have to be NULL.（要注意的是Sells表中的price一定要置空）

The Trigger

CREATE TRIGGER ViewTrig
INSTEAD OF INSERT ON Synergy
REFERENCING NEW ROW AS n
FOR EACH ROW
BEGIN
INSERT INTO LIKES VALUES(n.drinker, n.beer);
INSERT INTO SELLS(bar, beer) VALUES(n.bar, n.beer);
INSERT INTO FREQUENTS VALUES(n.drinker, n.bar);
END;