INSTEAD OF 触发器 － 所有视图都是可更新的！

核心提示：简介视图通常用来区分逻辑数据库模式和物理模式，遗憾的是，INSTEAD OF 触发器 － 所有视图都是可更新的！，在 UPDATE、DELETE 或 INSERT 操作中通常都达不到预期的透明度，因为除了最简单的视图之外所有的视图都不可更新，或者直接的、间接的、特殊的、由此产生的、附带的损失或惩罚性的损失赔偿负责，即使

简介

视图通常用来区分逻辑数据库模式和物理模式。遗憾的是，在 UPDATE、DELETE 或 INSERT 操作中通常都达不到预期的透明度，因为除了最简单的视图之外所有的视图都不可更新。本文评估哪些视图自己是可更新的，然后引入 INSTEAD OF 触发器 － 这是 DB2® Universal Database™ V8.1 for Linux、UNIX® 和 Windows® 的新功能，它使所有视图都可以更新。

先决条件

本文中的许多示例都使用 SQL 过程化语言（SQL Procedural Language，SQL PL）。由于 SQL PL 在其主体中使用分号（ ;）来分隔各个语句，所以对 CLP 或命令中心（Command Center）必须使用另一个定界符。在本篇文章中我将使用美元符号（ $）来给语句定界。

要启动 CLP 会话以使用本文中的示例，请从 shell 输入： db2 -td$ 。对于命令中心，通过单击 Tools -> ToolsSettings -> Use statement termination character来设置并激活定界符。

纵览可更新视图

在我们向您完整地介绍可更新视图之前，我们需要基本了解一下“可更新”是什么意思以及 DB2（或就这一点而言的任何数据库管理系统）必须解决哪些基本问题才能通过视图更新数据库中的行。首先，缺省情况下 DB2 所拥有的全部视图信息就是视图的定义，即指定由该视图派生出的表的查询。

为了精确起见，我们需要区别三个级别的可更新能力：

可删除：为了从视图中删除一行，DB2 必须能够将视图中指定的那一行映射到基本表中有且仅有的一行。
可更新：为了更新视图中的列，DB2 必须不仅能够将视图中指定的一行映射到基本表中的一行，它还必须能够将要更新的一列映射到基本表中的一列。因此，根据定义所有可更新的视图必须是可删除的。
可插入：为了将行插入到视图中，DB2 必须能将新的行映射到一张表中并能够将指定的所有列映射到该表中的列。因此所有可插入的视图要定义成是可更新的，从而也是可删除的。

现在让我们定义一些表，然后研究这些表的各种视图的可更新能力：

CREATE　TABLE　T1(c1　INT,　c2　FLOAT)$　
INSERT　INTO　T1　VALUES　(5,　6.0),　
　　　　　　　　　　　(6,　7.0),　
　　　　　　　　　　　(5,　6.0)$　
CREATE　TABLE　T2(c1　INT,　c2　FLOAT)$　
INSERT　INTO　T2　VALUES　(5,　9.0),　
　　　　　　　　　　　(5,　4.0),　
　　　　　　　　　　　(7,　5.0)$

对于本文中的所有示例，让我们假设提供了下列数据。示例不应该相互影响。可以通过在 CLP 中使用 +c option或从命令中心取消激活 Options -> Execution -> Automatically Commit 来关闭（OFF）自动提交，从而使它们不相互影响。在每个示例结束时使用 ROLLBACK 命令。

示例 1：

CREATE　VIEW　V1(c1)　
AS　SELECT　c1　FROM　T1　WHERE　c2　>　0$

这是一个非常简单的视图。派生的表包含了 T1 的行子集和列子集。这个视图是可删除的，因为 DB2 可以跟踪每一行的起源并在基本表中删除各自的行。

DELETE　FROM　V1　WHERE　c1　=　6$

但是请注意，如果有人执行了定位删除并删除 V1.c1 等于 5 的那一行，那么就看不出将删除 T1.c1 等于 5 的那两行中的哪一行。

视图也是可更新的：

UPDATE　V1　SET　c1　=　c1　+　5　WHERE　c1　=　5$

可以直接将 V1.c1 映射到 T1.c1。

INSERT 会怎么样呢？显然，应当将插入到 V1 中的任意行插入到 T1 中。为 V1.c1 指定的值应当用于 T1.c1。但是 T1.c2 呢？将使用哪些值？毕竟，视图并不知道这一列，因此用户不能为它指定值。

INSERT　INTO　V1　VALUES　(8)$

这里需要一个定义，而 SQL 标准已经提供了该定义：将每个未提供的列值初始化成该列的隐式缺省值或显式缺省值。由于没有为 T1.c2 指定显式缺省值，所以选择值 NULL 并将（8, NULL）插入到 T1 中。

示例 2：

当有表达式时会是怎样？

CREATE　VIEW　V2(c1,　c2)　
AS　SELECT　c1,　c2　*　c2　FROM　T1$

与第一个示例相比较，在可删除性方面没什么改变。DB2 仍然知道视图中的某一行是由基本表中的哪一行生成的。V2.c1 列是可更新的，因此该视图也是可更新的。

但是，V2.c2 是不可更新的。原因是从任何给定的 V2.c2 无法确定 T1.c2 的值。DB2 可以设法使用除法操作来查找匹配值；但是，这个简单的示例恰恰阐明这样的数学方法只能在用在最为简单的情形中。这个小小的尝试用来说明这样的算法的功能和限制，显示出这个算法缺乏可用性。

那么对于可插入性这到底又意味着什么呢？在 DB2 V8 之前，为使视图可插入，就要求视图的所有列都是可更新的。在 DB2 V8 中，只要有一个可更新的列就足够了。只要没有指定其它列，那么就可以忽略它们。

INSERT　INTO　V2(c1)　VALUES　(7)$

系统将把（7, NULL）插入到 T1 中。请注意，即使没有一列是可更新的，视图也是可删除的。同理，反对将不可更新的列插入到视图中的唯一理由就是在不是单一列的情况下既没有定义 VALUES 也没有定义 SELECT。

示例 3：

CREATE　VIEW　V3(c1,　c2,　c3)　
AS　SELECT　T1.c1,　T1.c2,　T2.c2　
　　　FROM　T1,　T2　WHERE　T1.c1　=　T2.c1$

这个视图是从连接（join）派生出的。在本例中，其结果是：

SELECT　*　FROM　V3　ORDER　BY　c1,　c2,　c3$　
　　　　C1　C2　　C3　
--　---　---　
　5　6.0　4.0　
　5　6.0　4.0　
　5　6.0　9.0　
　5　6.0　9.0　

这个视图是不可删除的。该视图中的每一行都可以追溯到表 T1 和 T2 中的某一行，通过删除 T1 和 T2 中的各自行来删除第一行（5, 6.0, 4.0），那么也会间接删除第二行（5, 6.0, 4.0）和两个（5, 6.0, 9.0）行中的一行。这个行为不是很直观，对于不知道视图查询的用户尤为如此。

有的时候在视图中删除一行导致基本表中也删除一行，同时并没有对视图造成不希望出现的影响。例如，如果 T1.c1 和 T2.c1 都是唯一的，就属于上述情况。DB2 现在不考虑这种特殊情况。

由于 V3 是不可删除的，因此它也是不可更新的。再次声明，如果您想更新视图中的某一行，会发生令人奇怪的事情；如果想更新 V1.c1，以使视图中不同行拥有不同的值，那么情况会更糟糕。

同样，INSERT 的语义不清楚。根据为基本表插入操作选择的语义，添加另一行（5, 6.0, 9.0）会产生不同的结果。这是否意味着可更新视图不能引用另一张表？并非如此。如 示例 2所讨论的那样，带有表达式的视图具有很好的可更新性。因此标量子查询就非常适合。

示例 4：

CREATE　VIEW　V4(c1,　c2)　
AS　SELECT　c1,　c2　FROM　T1　
　　UNION　ALL　
　　SELECT　c1,　c2　FROM　T2$

V4 中的每一行明显源自特定表中的某一行。因此，基于 UNION ALL 的视图是可删除的。如果列没有基于表达式，那么该列也是可更新的。但是，该视图是不可插入的，原因很明显：它不能确定任何给定的行应当插入到哪一张基本表中。允许将某一行同时插入到两张基本表中并不合适，因为随后从视图中进行选择将两次显示该行。为允许通过 UNION ALL 进行 INSERT，需要对基本表进行约束：只能将任一给定行分派给一张表。

请参阅 http://www.ibm.com/developerworks/cn/dmdd/library/techarticles/0209rielau/0209rielau.shtml以获取有关通过 UNION ALL 视图进行 INSERT 的更多详细情况。

对通过 UNION ALL 得到的视图有一些限制，应当加以注意。UNION ALL 视图的列是不可更新的，即使 DB2 自身已插入了表达式也是如此。

CREATE　VIEW　V5(c1,　c2)　
AS　SELECT　c2,　c1　FROM　T1　
　　UNION　ALL　
　　SELECT　c1,　c2　FROM　T2$

DB2 必须同时将 T2.c1 和 T1.c1 的数据类型转换为 FLOAT。V5.c1 和 V5.c2 现在都基于表达式，因此它们是不可更新的。但是，V5 仍然是可删除的。

CREATE　VIEW　V6(c1,　c2)　
AS　SELECT　c1,　c2　FROM　T1　
　　UNION　ALL　
　　SELECT　c1,　c2　FROM　T1$

象 V6 这样的视图也可称为“菱形”，因为处理从一个源分散为两个操作（SELECT），然后再返回到一起（UNION ALL）。菱形是只读的。不能删除行。原因在于 T1 中的每一行在 V6 中被表示了两次。因此在 V6 中不可能只删除一行。另外也不可能只更新一行。

当使用 UNION（或 DISTINCT）时会出现相反的问题。现在可以将视图中的每一行映射到基本表中可能的许多行。应当只删除基本表中的一行还是删除基本表中的所有匹配行？

我们已经讨论了一些不同类型视图的可更新性背后的想法。其它视图是不可更新的；但是，让我们将目光转向视图可更新性的重要“武器”：INSTEAD OF 触发器。

INSTEAD OF 触发器

视图是由其主体定义的。对于 SELECT 查询而言这一点非常明显。对于我们在上面所讨论的 UPDATE、DELETE 和 INSERT 而言，尽管对于某些类有可能假设视图如何定义明显的语义，而在大多数情况下无法做到这一点。这就是 INSTEAD OF 触发器的突破口。INSTEAD OF 触发器阻止 DB2 去尝试解释更新操作的视图定义。与此“相反”，它将执行触发器主体，依靠定义者来得出有意义的语义。

例如：

CREATE　VIEW　V7(c1,　c2)　
AS　SELECT　DISTINCT　c1,　c2　FROM　T1$

可以将 INSTEAD OF 触发器定义成删除 T1 中与 V7 中给定行相匹配的所有行或者根据某条预定规则仅删除一行。

让我们定义一个触发器，它删除基本表中所有的匹配行：

CREATE　TRIGGER　V7_DELETE　INSTEAD　OF　DELETE　ON　V7　
　REFERENCING　OLD_TABLE　AS　OLD_TAB　
　OLD　AS　O　FOR　EACH　ROW　MODE　DB2SQL　
　DELETE　FROM　T1　WHERE　o.c1　=　c1　AND　o.c2　=　c2$

从表面上看，与普通触发器相比，只有两个子句发生了变化。首先我们定义了关键字 INSTEAD OF。其次我们指定了视图名而不是基本表名。

INSTEAD OF 是一个非常清晰的子句。它并不表示在试图进行删除操作之前执行触发器。也不表示在进行删除操作之后执行触发器。按照字面意思，就是表示忘掉删除操作。转而执行这段代码。

INSTEAD OF 触发器总是为视图而创建的，永远不是为基本表或别名而创建的。DB2 V8 中的 INSTEAD OF 触发器始终是 FOR EACH ROW 触发器。这意味着针对每个可以对视图进行删除、更新或插入操作的行，都要执行一次这个触发器。

该触发器中转换表和转换变量的概念与普通触发器中的概念一致。下面的 表 1显示了根据 INSTEAD OF 触发器的类型，可以在哪里使用转换变量和转换表：

表 1. 转换变量

	OLD	OLD_TABLE	NEW	NEW_TABLE
INSERT	NO	NO	YES	YES
UPDATE	YES	YES	YES	YES
DELETE	YES	YES	NO	NO

OLD_TABLE 和 NEW_TABLE 转换表指定了由视图处理的行集。OLD 和 NEW 转换变量是由当前对触发器的调用所处理的各自转换表中的特定行。

在 DELETE 或 UPDATE 触发器中，OLD_TABLE 是符合删除条件的行集。这表示它们是视图上的游标能够看到的行。下面是我们示例中的 OLD_TABLE 值：

OLD_TAB:　
　C1　　C2　
---　----　
　5　6.0　
　6　7.0　

触发器处理行的顺序是任意的。这意味着没有规定上面的触发器先将（5, 6.0）还是先将（6, 7.0）看作 OLD 转换行。还有一点需要注意，对于所有触发器（包括 INSTEAD OF 触发器），转换表是在触发器被触发之前完全确定好的。这意味着我们可以在 T1 中进行删除，而不会看到 OLD_TABLE 中的更改。

请注意，上面的触发器在触发操作中不能有 WHEN 子句。INSTEAD OF 触发器是无条件触发的。对于 BEFORE 和 AFTER UPDATE 触发器的典型列列表而言也是如此。这些也是一种触发器条件，而 INSTEAD OF 触发器不会考虑它们。

CREATE　TRIGGER　V7_UPDATE　INSTEAD　OF　UPDATE　ON　V7　
　REFERENCING　NEW　AS　n　OLD　AS　o　
　FOR　EACH　ROW　MODE　DB2SQL　
　UPDATE　T1　SET　(c1,　c2)　=　(n.c1,　n.c2)　
　　WHERE　c1　=　o.c1　AND　c2　=　o.c2$

无论更新哪一列都会触发该触发器。

图 1显示了 INSTEAD OF 触发器的完整语法图：

图 1. INSTEAD OF 触发器的语法图

>>-CREATE　TRIGGER--　
　　　　　trigger-name--INSTEAD　OF--+-INSERT-+--ON--　
　　　　　view-name------->　
　　　　　　　　　　　　　　　　　　　　　　　+-DELETE-+　
　　　　　　　　　　　　　　　　　　　　　　　'-UPDATE-'　
　
>-----+---------------------------------------------------------------------+>　
　　　|　　　　　　　.----------------------------------.　　　　　　　　　　|　
　　　|　　　　　　　V　　　　.-AS-.　　　　　　　　　　　|　　　　　　　　　　|　
　　　'-REFERENCING---+-OLD--+----+--　
　　　　　correlation-name-+-+-+---------------+-'　
　　　　　　　　　　　|　　　.-AS-.　　　　　　　　　　|　　
　　　　　　　　　　　+-NEW-+----+--　
　　　　　correlation-name--+　
　　　　　　　　　　　|　　　　　　.-AS-.　　　　　　　|　
　　　　　　　　　　　+-OLD_TABLE-+----+--　
　　　　　identifier--+　
　　　　　　　　　　　|　　　　　　.-AS-.　　　　　　　|　
　　　　　　　　　　　'-NEW_TABLE-+----+--　
　　　　　identifier--'　
　
>----FOR　EACH　ROW---MODE　DB2SQL----　
　　　　　SQL-procedure-statement------------------><　

注：DB2 V8.1 文档显示需要 DEFAULTS NULL子句。这个子句在 V8.1 中已经 除去了。请参阅下面的 异常表示例以获取 INSTEAD OF 触发器上下文中缺省值的行为。

SQL-procedure-statement可以是 SQL 语句（比如 INSERT、UPDATE、DELETE、SELECT 或 VALUES），也可以是使用 BEGIN ATOMIC ... END 的动态复合语句。

通过示例进行学习

理论到此结束。让我们研究一下您在实际生活中可能会碰到的某些示例。

垃圾箱

INSTEAD OF 触发器编写得好坏都取决于您自己。视图定义和 INSTEAD OF 触发器之间的唯一联系在于转换表的行定义（特征符），这些表的列类型和名称与从视图查询中继承的列类型和名称相匹配。

这是什么意思？如果您定义了 INSTEAD OF 触发器，您就可以做您喜欢做的任何事。

CREATE　VIEW　GARBAGE(text)　
AS　SELECT　CAST(NULL　AS　CLOB(20M))　
　　　FROM　SYSIBM.SYSDUMMY1　
　　　WHERE　1　=　0$　
　　　
CREATE　TRIGGER　INSERT_GARBAGE　
INSTEAD　OF　INSERT　ON　GARBAGE　FOR　EACH　ROW　MODE　DB2SQL　
BEGIN　ATOMIC　
END$　
INSERT　INTO　GARBAGE　VALUES　'unrecoverable　document'$

哦，我们在这里为自己做了一个漂亮的垃圾箱。鉴于此，只允许对视图拥有 CONTROL 特权的用户、视图定义者、SYSADM 或 DBADM 才能在视图上创建 INSTEAD OF 触发器。

透明加密

在介绍了 INSTEAD OF 触发器这种最简单的应用之后，让我们讨论更常见的类型，也就是使用 INSTEAD OF 触发器在视图中为加密表达式提供“逆”操作。

CREATE　TABLE　USERS　
　　(user　VARCHAR(20),　
　　system　VARCHAR(30),　
　　login　VARCHAR(20),　
　　password　VARCHAR(40)　FOR　BIT　DATA)$

上面的表包含了不同系统上用户的标识和加密密码。下面的视图假定在用户提供了正确凭证的前提下，对与当前用户相关的行进行解密。

CREATE　VIEW　MY_LOGINS(system,　login,　password)　
AS　SELECT　system,　login,　decrypt_char(password)　
　　　FROM　USERS　AS　u　WHERE　u.user　=　USER$　

为了通过这个视图进行更新或插入，我们需要定义 INSTEAD OF 触发器，该触发器对用户提供的密码进行加密以将其存储在基本表中。

CREATE　TRIGGER　INSERT_MY_LOGINS　INSTEAD　OF　INSERT　
　ON　MY_LOGINS　REFERENCING　NEW　AS　n　
　FOR　EACH　ROW　MODE　DB2SQL　
　INSERT　INTO　USERS　
　　VALUES(USER,　n.system,　n.login,　
　　　　　　encrypt(password))$　
CREATE　TRIGGER　UPDATE_MY_LOGINS　INSTEAD　OF　UPDATE　
　ON　MY_LOGINS　REFERENCING　OLD　AS　o　NEW　AS　n　
　FOR　EACH　ROW　MODE　DB2SQL　
　UPDATE　USERS　U　
　　SET　system　=　n.system,　
　　　　login　=　n.login,　
　　　　password　=　encrypt(n.password)　
　　WHERE　system　=　o.system　
　　　AND　login　=　o.login　
　　　AND　U.user　=　USER$

加密数据通过 ENCRYPTION PASSWORD 特殊寄存器受到保护。

SET　ENCRYPTION　PASSWORD　=　'unbreakable'$

现在把我们的机密信息填入表中：

INSERT　INTO　MY_LOGINS　
　VALUES('AFS',　　'srielau',　'mydogsname'),　
　　　　('Linux',　'root',　　'oopsIforgot'),　
　　　　('IIUG',　'Rielau',　'123456789')$　
SELECT　*　FROM　MY_LOGINS　WHERE　system　=　'Linux'$　
SYSTEM　LOGIN　　　PASSWORD　
------　-------　　------------　
Linux　　root　　　oopsIforgot

在基本表上密码是加过密的。没有上面设置的密码，任何人（即使是 DB2 支持人员）都不能取回信息。

SELECT　*　FROM　USERS　U　ORDER　BY　U.user,　system,　login$　
　　　　USER　　　SYSTEM　　LOGIN　　　PASSWORD　
--------　-------　-------　　-------------　
SRIELAU　AFS　　　srielau　　0x...........　
SRIELAU　IIUG　　　Rielau　　0x...........　
SRIELAU　Linux　　root　　　0x...........　

可以更新视图中的 password 或任何其它字段。

UPDATE　MY_LOGINS　
　SET　password　=　'mycatsname'　
　WHERE　system　=　'AFS'　AND　login　=　'srielau'$

但是，首先有一点，视图是可删除的。因此我们不用 INSTEAD OF DELETE 触发器就可以删除各项。

DELETE　FROM　my_logins　WHERE　SYSTEM　=　'AFS'$

有关加密的更多详细情况，请参阅 http://www7b.boulder.ibm.com/dmdd/library/techarticle/benfield/0108benfield.html。

让我们用一个较复杂的安全性示例来增加点复杂性。

组级和行级安全性

经理可以查看其雇员的工资，但是不能查看其同级经理或那些经理所管辖的那些雇员的工资。二线经理可以看到其直接以及间接报告的工资。

让我们对这样的模式进行建模，然后定义 INSTEAD OF 触发器，以允许经理可以更新其管辖雇员的信息。

CREATE　TABLE　PROFILES　
(empid　INT,　name　VARCHAR(20),　sqlid　VARCHAR(18),　
　mgrid　INT,　salary　DECIMAL(9,2),　ismgr　CHAR(1))$

我们想填入一些数据，在本例中是一个四层的层次结构。我们是名叫“MySelf”的二线经理。

INSERT　INTO　PROFILES　
VALUES(0001,　'SuperBoss',　'sboss',　NULL,　500000,　'Y'),　
　　　(1001,　'BigBoss',　　'bboss',　0001,　200000,　'Y'),　
　　　(1002,　'MySelf',　　　USER,　　0001,　250000,　'Y'),　
　　　(2001,　'FirstLine',　'fline',　1001,　100000,　'Y'),　
　　　(2002,　'MiddleMen',　'mmen',　1001,　110000,　'Y'),　
　　　(2003,　'Yeti',　　　　'yeti',　1002,　90000,　'Y'),　
　　　(2004,　'BigFoot',　　'bfoot',　1002,　80000,　'N'),　
　　　(3001,　'TinyToon',　　'ttoon',　2001,　50000,　'N'),　
　　　(3002,　'Mouse',　　　'Mouse',　2001,　40000,　'N'),　
　　　(3003,　'Whatsisname','wname',　2002,　45000,　'N'),　
　　　(3004,　'Hasnoclue',　'hclue',　2002,　38000,　'N'),　
　　　(3005,　'Doesallwork','dwork',　2003,　15000,　'N')$

递归视图允许我们确定所有雇员的工资，包括我们自己的工资。

CREATE　VIEW　my_emps(empid,　level,　salary)　
AS　WITH　rec(empid,　level,　salary)　
　　　　　AS　(SELECT　empid,　0,　salary　FROM　PROFILES　
　　　　　　　　WHERE　sqlid　=　USER　
　　　　　　　UNION　ALL　
　　　　　　　SELECT　P.empid,　level-1,　P.salary　
　　　　　　　　FROM　PROFILES　P,　REC　R　
　　　　　　　　WHERE　level　>　-100　
　　　　　　　　　AND　R.EMPID　=　P.MGRID)　
　　SELECT　empid,　level,　salary　FROM　rec$

下面的视图将是个接口，被任何人用来读取雇员信息。我们考虑了雇员标识（empid）、雇员姓名（name）、他们经理的姓名（name）、他们的 SQLID（sqlid）以及他们是否是经理（ismgr）和公共信息。雇员只能看到他们自己的工资及其下属的工资。

CREATE　VIEW　PROFILES_V(empid,　name,　mgrname,　
　　　　　　　　　　　　salary,　sqlid,　ismgr)　
　AS　SELECT　P.empid,　P.name,　
　　　　　　(SELECT　name　FROM　PROFILES　M　
　　　　　　　WHERE　M.empid　=　P.mgrid),　
　　　　　　ME.salary,　P.sqlid,　P.ismgr　
　　　FROM　PROFILES　P　LEFT　OUTER　JOIN　MY_EMPS　ME　
　　　　ON　ME.empid　=　P.empid$

根据我们早先的分析，我们知道这个视图是不可删除的，因此也是不可更新或插入的。为了保持封装性，我们需要定义一组完整的 INSTEAD OF 触发器：

CREATE　TRIGGER　INSERT_PROFILES_V　
　INSTEAD　OF　INSERT　ON　PROFILES_V　REFERENCING　NEW　AS　n　
　FOR　EACH　ROW　MODE　DB2SQL　
　BEGIN　ATOMIC　
　　DECLARE　mgrid　INT;　
　　DECLARE　ismgr　CHAR(1);　
　　SET　(mgrid,　ismgr)　
　　　=　(SELECT　empid,　ismgr　FROM　PROFILES　
　　　　　WHERE　name　=　n.mgrname);　
　　IF　mgrid　NOT　IN　(SELECT　empid　FROM　my_emps)　
　　　　OR　ismgr　=　'N'　
　　THEN　
　　　SIGNAL　SQLSTATE　'70000'　
　　　　　SET　MESSAGE_TEXT　=　'Not　Authorized!';　
　　END　IF;　
　　INSERT　INTO　PROFILES　
　　　VALUES(n.empid,　n.name,　n.sqlid,　
　　　　　　　mgrid,　n.salary,　n.ismgr);　
　END$

这个触发器强制执行这条业务规则：假如用户自己是经理，那么他们只能插入作为其下属的雇员。

下面的 INSTEAD OF 更新触发器强制执行这样一条业务规则：只能更新下属的雇员，并且如果一个雇员从一个经理的手下调到了另一个经理的手下，那么这两个经理必须都是执行更新操作的那个用户的下属。

DB2 没法知道用户实际上更新了哪些列，指出这一点很重要。DB2 只能比较 OLD 和 NEW 转换变量以检测更改。在本例中，触发器旨在始终更新整个行。这样做对性能的影响并不象一开始看上去的那么糟，因为 DB2 的记录器可以检测到实际上更改的是哪个部分然后避免额外的日志记录。

CREATE　TRIGGER　UPDATE_PROFILES_V　
　INSTEAD　OF　UPDATE　ON　PROFILES_V　REFERENCING　NEW　AS　n　
　OLD　AS　o　FOR　EACH　ROW　MODE　DB2SQL　
　BEGIN　ATOMIC　
　　DECLARE　oldmgrid,　newmgrid　INT;　
　　SET　oldmgrid　=　(SELECT　empid　FROM　PROFILES　
　　　　　　　　　　WHERE　name　=　o.mgrname),　
　　　　newmgrid　=　(SELECT　empid　FROM　PROFILES　
　　　　　　　　　　WHERE　name　=　n.mgrname);　
　　IF　oldmgrid　NOT　IN　(SELECT　empid　FROM　my_emps)　
　　　　OR　newmgrid　NOT　IN　(SELECT　empid　FROM　my_emps)　
　　　　OR　o.empid　=　(SELECT　empid　FROM　PROFILES　P　
　　　　　　　　　　　WHERE　USER　=　P.sqlid)　
　　THEN　
　　　SIGNAL　SQLSTATE　'70000'　
　　　　　SET　MESSAGE_TEXT　=　'Not　Authorized!';　
　　END　IF;　
　　UPDATE　PROFILES　SET　empid　=　n.empid,　
　　　　　　　　　　　　mgrid　=　newmgrid,　
　　　　　　　　　　　　salary　=　n.salary,　
　　　　　　　　　　　　sqlid　=　n.sqlid,　
　　　　　　　　　　　　name　=　n.name,　
　　　　　　　　　　　　ismgr　=　n.ismgr　
　　　WHERE　empid　=　o.empid;　
　END$

最后，下面的 INSTEAD OF 删除触发器保证了用户只能删除其下属，而永远不能删除他们自己。

CREATE　TRIGGER　DELETE_PROFILES_V　
　INSTEAD　OF　DELETE　ON　PROFILES_V　
　REFERENCING　OLD　AS　o　FOR　EACH　ROW　MODE　DB2SQL　
　BEGIN　ATOMIC　
　　DECLARE　mgrid　INT;　
　　SET　mgrid　=　(SELECT　empid　FROM　PROFILES　
　　　　　　　　　WHERE　name　=　o.mgrname);　
　　IF　mgrid　NOT　IN　(SELECT　empid　FROM　my_emps)　
　　　　OR　o.empid　=　(SELECT　empid　FROM　PROFILES　P　
　　　　　　　　　　　WHERE　USER　=　P.sqlid)　
　　THEN　
　　　SIGNAL　SQLSTATE　'70000'　
　　　　　SET　MESSAGE_TEXT　=　'Not　Authorized!';　
　　END　IF;　
　　DELETE　FROM　PROFILES　WHERE　empid　=　o.empid;　
　END$

完成我们的 DB 模式并将一些初始数据填入表中之后，我们可以查看一切是否正常工作：

SELECT　*　FROM　PROFILES_V　ORDER　BY　empid$　
　　　　EMPID　NAME　　　　　MGRNAME　　SALARY　　SQLID　　ISMGR　
-----　------------　----------　---------　--------　-----　
　　1　SuperBoss　　-　　　　　　　　　-　sboss　　Y　
　1001　BigBoss　　　SuperBoss　　　　　-　bboss　　Y　
　1002　MySelf　　　　SuperBoss　250000.00　SRIELAU　Y　
　2001　FirstLine　　BigBoss　　　　　　-　fline　　Y　
　2002　MiddleMen　　BigBoss　　　　　　-　mmen　　　Y　
　2003　Yeti　　　　　MySelf　　　90000.00　yeti　　　Y　
　2004　BigFoot　　　MySelf　　　80000.00　bfoot　　N　
　3001　TinyToon　　　FirstLine　　　　　-　ttoon　　N　
　3002　Mouse　　　　FirstLine　　　　　-　Mouse　　N　
　3003　Whatsisname　MiddleMen　　　　　-　wname　　N　
　3004　Hasnoclue　　MiddleMen　　　　　-　hclue　　N　
　3005　Doesallwork　Yeti　　　　15000.00　dwork　　N　

到目前为止，非常顺利。让我们看看 INSTEAD OF 触发器是否象所设计的那样工作。首先我们雇佣 NewGuy 并让他在 Yeti 手下工作：

INSERT　INTO　PROFILES_V　
VALUES　(3006,　'NewGuy',　'Yeti',　35000,　'nguy',　'N')$　
SELECT　*　FROM　PROFILES_V　WHERE　empid　=　3006$　
　　　　EMPID　NAME　　　　　MGRNAME　　SALARY　　SQLID　　ISMGR　
-----　------------　----------　---------　--------　-----　
　3006　NewGuy　　　　Yeti　　　　35000.00　nguy　　　N　

接下来我们把 Doesallwork 提升为 Myself 手下的管理人员并将他的工资提高 30%：

UPDATE　PROFILES_V　
　SET　ismgr　=　'Y',　
　　　salary　=　salary　*　1.30,　
　　　mgrname　=　'MySelf'　
　WHERE　name　=　'Doesallwork'$　
　　
SELECT　*　FROM　PROFILES_V　WHERE　name　=　'Doesallwork'$　
　　　　EMPID　NAME　　　　　MGRNAME　　SALARY　　SQLID　　ISMGR　
-----　------------　----------　---------　--------　-----　
　3005　Doesallwork　MySelf　　　19500.00　dwork　　Y　

为使这个示例保持逼真，Doesallwork 想通了并离开公司：

DELETE　FROM　PROFILES_V　WHERE　name　=　'Doesallwork'$

到目前为止我们已经有多个视图示例，包括没有表的视图、一张表的视图和外连接中所涉及的一张表的视图。让我们现在研究一下所有表都需要更新的多表连接。

垂直分区数据（即星型连接）

要求对所有的表（同时）进行连接和更新的典型方案是数据的垂直分区。为了简单起见，我们使用 PERSONS 模式。一些 PERSONS 被雇佣，另一些则作为学生招收。有一些 PERSONS 既是雇员又是学生。

CREATE　TABLE　PERSONS(ssn　INT　NOT　NULL,　
　　　　　　　　　　　name　VARCHAR(20)　NOT　NULL)$　
　　　　　　　　　　　
CREATE　TABLE　EMPLOYEES(ssn　INT　NOT　NULL,　
　　　　　　　　　　　company　VARCHAR(20)　NOT　NULL,　
　　　　　　　　　　　salary　DECIMAL(9,2))$　
　　　　　　　　　　　　
CREATE　TABLE　STUDENTS(ssn　INT　NOT　NULL,　
　　　　　　　　　　　university　VARCHAR(20)　NOT　NULL,　
　　　　　　　　　　　major　VARCHAR(10))$

要在应用程序中把所有这些表连接在一起，可能会令人头疼。因此我们创建了一个视图：

CREATE　VIEW　PERSONS_V(ssn,　name,　company,　
　　　　　　　　　　　salary,　university,　major)　
AS　SELECT　P.ssn,　name,　company,　
　　　　　salary,　university,　major　
　　　FROM　PERSONS　P　LEFT　OUTER　JOIN　EMPLOYEES　E　
　　　　　　　　　　　　　ON　P.ssn　=　E.ssn　
　　　　　　　　　　LEFT　OUTER　JOIN　STUDENTS　S　
　　　　　　　　　　　　　ON　P.ssn　=　S.ssn$

再次声明，这个视图既是不可插入的，也是不可更新或删除的。因此我们需要生成一组完整的 INSTEAD OF 触发器：

CREATE　TRIGGER　INSERT_PERSONS_V　
　INSTEAD　OF　INSERT　ON　PERSONS_V　
　REFERENCING　NEW　AS　n　FOR　EACH　ROW　MODE　DB2SQL　
　BEGIN　ATOMIC　
　　INSERT　INTO　PERSONS　VALUES　(n.ssn,　n.name);　
　　IF　n.university　IS　NOT　NULL　THEN　
　　　INSERT　INTO　STUDENTS　
　　　　VALUES(n.ssn,　n.university,　n.major);　
　　END　IF;　
　　IF　n.company　IS　NOT　NULL　THEN　
　　　INSERT　INTO　EMPLOYEES　
　　　　VALUES(n.ssn,　n.company,　n.salary);　
　　END　IF;　
　END$

如果某人与大学相关联，上面的触发器则认为他是一个学生；如果某人与公司相关联，上面的触发器则认为他是一个雇员。

实现 INSTEAD OF DELETE 触发器非常简单。从所有这三张表中删除一个人是很安全的，即使这三张表中没有任何项也是如此：

CREATE　TRIGGER　DELETE_PERSONS_V　
　INSTEAD　OF　DELETE　ON　PERSONS_V　
　REFERENCING　OLD　AS　o　FOR　EACH　ROW　MODE　DB2SQL　
　BEGIN　ATOMIC　
　　DELETE　FROM　STUDENTS　WHERE　ssn　=　o.ssn;　
　　DELETE　FROM　EMPLOYEES　WHERE　ssn　=　o.ssn;　
　　DELETE　FROM　PERSONS　WHERE　ssn　=　o.ssn;　
　END$

现在事情变得有些棘手了。正如定义插入触发器时所表明的那样，学生必须和大学相关联，而雇员必须与公司相关联。因此如果某人获得或失去这些关联之一，那么就需要在相应的表中添加或除去行：

CREATE　TRIGGER　UPDATE_PERSONS_V　
　INSTEAD　OF　UPDATE　ON　PERSONS_V　
　REFERENCING　OLD　AS　o　NEW　AS　n　
　FOR　EACH　ROW　MODE　DB2SQL　
　BEGIN　ATOMIC　
　　UPDATE　PERSONS　
　　　SET　(ssn,　name)　=　(n.ssn,　n.name)　
　　　WHERE　ssn　=　o.ssn;　
　　IF　n.university　IS　NOT　NULL　
　　　　AND　o.university　IS　NOT　NULL　THEN　
　　　UPDATE　STUDENTS　
　　　　SET　(ssn,　university,　major)　
　　　　　=　(n.ssn,　n.university,　n.major)　
　　　　WHERE　ssn　=　o.ssn;　
　　ELSEIF　n.university　IS　NULL　THEN　
　　　DELETE　FROM　STUDENTS　WHERE　ssn　=　o.ssn;　
　　ELSE　
　　　INSERT　INTO　STUDENTS　
　　　　VALUES(n.ssn,　n.university,　n.major);　
　　END　IF;　
　　IF　n.company　IS　NOT　NULL　
　　　　AND　o.company　IS　NOT　NULL　THEN　
　　　UPDATE　EMPLOYEES　
　　　　SET　(ssn,　company,　salary)　
　　　　　=　(n.ssn,　n.company,　n.salary)　
　　　　WHERE　ssn　=　o.ssn;　
　　ELSEIF　n.company　IS　NULL　THEN　
　　　DELETE　FROM　EMPLOYEES　WHERE　ssn　=　o.ssn;　
　　ELSE　
　　　INSERT　INTO　EMPLOYEES　
　　　　VALUES(n.ssn,　n.company,　n.salary);　
　　END　IF;　
　END$

既然已经完整定义了视图，它包含了用于插入、删除和更新的触发器，那就让我们对其进行测试：

INSERT　INTO　PERSONS_V　VALUES　
　(123456,　'Smith',　NULL,　NULL,　NULL,　NULL),　
　(234567,　'Jones',　'Wmart',　20000,　NULL,　NULL),　
　(345678,　'Miller',　NULL,　NULL,　'Harvard',　'Math'),　
　(456789,　'McNuts',　'SelfEmp',　60000,　'UCLA',　'CS')$　
　　
SELECT　*　FROM　PERSONS_V　ORDER　BY　SSN$　
　　　　SSN　　　NAME　　COMPANY　SALARY　　UNIVERSITY　MAJOR　
------　------　--------　--------　-----------　------　
123456　Smith　　-　　　　　　　　-　-　　　　　　-　
234567　Jones　　Wmart　　20000.00　-　　　　　　-　
345678　Miller　-　　　　　　　　-　Harvard　　　Math　
456789　McNuts　SelfEmp　60000.00　UCLA　　　　CS　　　

现在 Smith 先生结婚了，采用他妻子的姓“Johnson”，并且他以 15000 的工资被 Mickburgs 聘用，而 Miller 女士从 Harvard 毕业并被 IBM 聘用。

UPDATE　PERSONS_V　SET　(name,　company,　salary)　
　　　　　　　　　　=　('Johnson',　'Mickburgs',　15000)　
　WHERE　SSN　=　123456$　
　　
UPDATE　PERSONS_V　SET　(company,　salary,　university)　
　　　　　　　　　　=　('IBM',　70000,　NULL)　
　WHERE　SSN　=　345678$　
　　
SELECT　*　FROM　PERSONS_V　WHERE　SSN　IN　(123456,　345678)　
　ORDER　BY　SSN$　
　　
　　　　SSN　　　NAME　　　COMPANY　SALARY　　UNIVERSITY　MAJOR　
------　-------　--------　--------　-----------　------　
123456　Johnson　Mickburgs　　15000　-　　　　　　-　
345678　Miller　　IBM　　　　　70000　-　　　　　　-　

Jones 先生长期出国：

DELETE　FROM　PERSONS_V　WHERE　NAME　=　'Jones'$

最后说明一点，在插入时我们可以省略值为 NULL 的列：

INSERT　INTO　PERSONS_V(ssn,　name)　
　VALUES　(567890,　'vanderpoor')$

既然这么说了，那么谈论 DEFAULTS 就有意义了。我们将在下一个（也就是最后一个）示例中谈谈这一点及其它。

异常表处理

将数据插入表时，就会使用数据类型、BEFORE 触发器、检查约束和 RI 约束来限制用某种方式或其它方式插入的数据。SET INTEGRITY、LOAD 和 IMPORT 都允许用户通过将被拒绝的行存储到异常表中来处理有问题的数据。但是，INSERT 和 UPDATE 自身并没有办法可处理有问题的数据。应用程序负责尝试诸如插入之类的操作，如果失败了，就采取必要的步骤。不便之处在于两个方面：

首先，处理不是封装的。

其次，单行插入很浪费，即使通过存储过程完成时也是如此。

下面的示例使用一个 INSTEAD OF INSERT 触发器将有问题的行分派到另一个位置。

CREATE　TABLE　ADDRESSES(name　varchar(10),　
　　　　　　　　　　　　number　INT,　
　　　　　　　　　　　　street　varchar(20),　
　　　　　　　　　　　　country　VARCHAR(10)　
　　　　　　　　　　　　　　　　WITH　DEFAULT　'CANADA')$　
　　　　　　　　　　　　　　　　
CREATE　TABLE　BAD_ADDRESSES　
AS　(SELECT　CAST(NULL　AS　VARCHAR(30))　AS　Reason,　
　　　　　　A.*　
　　FROM　ADDRESSES　A)　DEFINITION　ONLY$　
　　　
CREATE　VIEW　ADDRESSES_V　
AS　SELECT　*　FROM　ADDRESSES$　
CREATE　TRIGGER　INSERT_ADDRESSES_V　
　INSTEAD　OF　INSERT　ON　ADDRESSES_V　
　REFERENCING　NEW　AS　n　FOR　EACH　ROW　MODE　DB2SQL　
　BEGIN　ATOMIC　
　　DECLARE　reason　VARCHAR(30);　
　　SET　reason　
　　　=　CASE　WHEN　n.number　IS　NULL　OR　n.number　<=　0　
　　　　　　　THEN　'Number'　
　　　　　　　WHEN　n.name　IS　NULL　OR　LENGTH(n.name)　=　0　
　　　　　　　THEN　'Name'　
　　　　　　　WHEN　n.street　IS　NULL　
　　　　　　　　OR　LENGTH(n.street)　=　0　
　　　　　　　THEN　'Street'　
　　　　　　　WHEN　country　IS　NULL　
　　　　　　　　OR　country　NOT　IN　('CANADA',　'USA',　
　　　　　　　　　　　　　　　　　'GERMANY',　'FRANCE')　
　　　　　　　THEN　'Country'　
　　　　　　　ELSE　NULL　END;　
　　IF　reason　IS　NOT　NULL　THEN　
　　　INSERT　INTO　BAD_ADDRESSES　
　　　　VALUES(reason,　n.name,　n.number,　
　　　　　　　　n.street,　n.country);　
　　ELSE　
　　　INSERT　INTO　ADDRESSES　
　　　　VALUES(n.name,　n.number,　n.street,　n.country);　
　　END　IF;　
　END$　

上面的触发器将把好的数据和有问题的数据分隔开来，并存储相应的地址。但是这个示例还说明了其它更多的东西。

请注意，当前的 DB2 V8 文档规定 INSTEAD OF 触发器应当将列的所有 DEFAULT 值看成 NULL，这一点很重要。这个行为是由于 DB2 V8 Beta 测试版的客户反馈而进行的更改，因此从 INSTEAD OF 触发器规范中删除了 DEFAULTS NULL 子句。

当定义视图时，对于可更新列从底层表中继承其列的缺省值。如果列（在没有 INSTEAD OF 触发器的帮助下）是不可更新的，那么认为 DEFAULT 为 NULL。在本例中，如果没有指定国家或地区（country），或者如果使用了关键字 DEFAULT，那么 INSTEAD OF 触发器将获取“CANADA”。对于其它所有列该触发器将获取 NULL。

INSERT　INTO　ADDRESSES_V　VALUES　
　('Jones',　510,　'Yonge　St.',　DEFAULT),　
　('Smith',　-1,　'Nowhere',　'USA'),　
　(NULL,　38,　'Am　Feldweg',　'GERMANY'),　
　('Poubelle',　23,　'Rue　de　Jardin',　'FRANCE')$　
　
SELECT　*　FROM　ADDRESSES　ORDER　BY　name$　
　　　　NAME　　　　NUMBER　STREET　　　　　COUNTRY　　
----------　------　--------------　-------　
Jones　　　　　510　Yonge　St.　　　CANADA　　　
Poubelle　　　　23　Rue　de　Jardin　FRANCE　　　
SELECT　*　FROM　BAD_ADDRESSES　ORDER　BY　name$　
　　　　REASON　NAME　　NUMBER　STREET　　　COUNTRY　　
------　-------　------　-----------　-------　
Number　Smith　　　　-1　Nowhere　　　USA　
Name　　-　　　　　　38　Am　Feldweg　GERMANY　

可以轻松地调整该触发器以忽略有问题的行而不是将它们存储起来。

更多想法……

INSTEAD OF 触发器的其它应用可能有：

避免递归的 AFTER 触发器。
有时候碰巧 AFTER 触发器必须更新它自己的主题表，在极端的情况下，这可以使可用的语句堆变得紧张并增加语句的编译时间。对主题表使用视图和创建 INSTEAD OF 触发器可以允许对主题表进行多次更新，不会引起视图的递归触发。

实现外部表。
为使表 UDF 也拥有插入、更新和删除功能，您可能想扩展对表 UDF 的现有读访问权。定义特定于插入、更新和删除的 UDF 是切实可行的。然后您可以在表 UDF 上创建一个视图并定义 INSTEAD OF 触发器以驱动其它 UDF。

使 WebSphere® MQ 看起来象一张表。
这可能类似于上面的外部表示例，但是它具有 WebSphere MQ 函数。

注意事项

我们尝试了许多使用 INSTEAD OF 触发器的示例，但是我们还没有谈到它们的限制。现在让我们来谈一谈。

对称视图
由于 INSTEAD OF 触发器上所具有的无法控制的语义，所以您不能在对称视图上创建 INSTEAD OF 触发器。对称视图所接受的行必须可以由该视图返回。此外，不能在拥有 INSTEAD OF 触发器的视图上面创建对称视图。

可更新游标
目前在可更新游标的上下文中不支持 INSTEAD OF 触发器。这意味着如果定位更新或定位删除分别造成 INSTEAD OF UPDATE 或 INSTEAD OF DELETE 触发器触发，那么该操作将失败。

警告

触发器的触发顺序
INSTEAD OF 触发器被认为是和 AFTER 触发器一起触发的。这意味着如果对 UNION ALL 视图进行更新或删除造成 INSTEAD OF 触发器和／或 AFTER 触发器的触发，那么整组触发器将按照触发器创建时间的先后顺序进行触发。

触发器和约束处理
和 AFTER 触发器一样，INSTEAD OF 触发器主体中的 SQL 语句是完整的语义实体。这意味着：举个例子，INSTEAD OF 触发器内的 UPDATE 语句将造成各自的检查约束、RI 约束和触发器在下一个语句之前触发，最终处理转换表中的下一行。

GET_DIAGNOSTICS ROW_COUNT（又名 SQLCA.ERRD(3)）
在 DB2 V8 之前，通过视图修改的行数始终等于直接在基本表上更新的行数，此处“直接”意味着不包括通过触发器和删除级联 RI 约束修改的行。有了 INSTEAD OF 触发器，这就不再成立了，而且 ROW_COUNT 的含义需要细化。DB2 V8 中的 ROW_COUNT 指定了符合进行用户所给定的更新、删除或插入操作条件的行数。对于进行这类操作的 INSTEAD OF 触发器，这个行数转换成了执行该触发器的次数和转换表的基数。

可能的改进

从 SQL 语言的角度来看，INSTEAD OF 触发器引起了一些有趣的问题和选择。例如：

视图级缺省值
由于 INSTEAD OF 触发器将视图查询的语义与其插入、删除和更新行为完全分离，因此仅从底层表派生出的缺省值似乎不再是足够的了。考虑到具有显式列缺省值（甚至可能有标识列特性）的视图似乎比较明智。

语句级 INSTEAD OF 触发器
虽然如今 DB2 只支持行级 INSTEAD OF 触发器，但是有各种各样的示例说明语句级 INSTEAD OF 触发器也可能很有用。例如，我们在上面的一个示例中所讨论的异常表触发器如果一次处理一组行而不是每一行，那么肯定会获得更好的性能。

为何视图上只有 INSTEAD OF 触发器？
我们已经介绍了视图中的一种触发器，为何就此罢手了呢？BEFORE、AFTER 触发器甚至 IDENTITY 可能是其它值得介绍的触发器。

结束语

在本文中我们讨论了可更新视图的功能和限制。我们介绍了允许您将任何视图转变成可更新视图的 INSTEAD OF 触发器，并发现了用于分布式平台的 DB2 V8 中的这种功能的各种应用。

免责声明

本文包含了样本代码。IBM 授予您（“被许可方”）使用这个样本代码的非专有的、版权免费的许可证。然而，该样本代码是以“按现状”的基础提供的，没有任何形式的（不论是明示的，还是默示的）保证，包括对适销性、适用于特定用途或非侵权性的默示保证。IBM 及其许可方不对被许可方由于使用该软件所导致的任何损失负责。任何情况下，无论损失是如何发生的，也不管责任条款怎样，IBM 或其许可方都不对由使用该软件或不能使用该软件所引起的收入的减少、利润的损失或数据的丢失，或者直接的、间接的、特殊的、由此产生的、附带的损失或惩罚性的损失赔偿负责，即使 IBM 已经被明确告知此类损害的可能性，也是如此。