sql入门:用约束和触发器实施商业规则

　　在商界，我们的确通常需要确保始终实施某些规则。例如，参与项目的雇员必须被雇用。或者想要某些事件有计划地发生。例如，如果销售员售出一批商品，则应增加其佣金。
　　
　　DB2 通用数据库为此提供了一套有用的方法。 唯一约束是禁止在表的一列或多列中出现重复值的规则。 参考完整性约束确保在整个指定的表中数据一致性。 表检查约束是一些条件，它们定义为表定义的一部分，限制一列或多列中使用的值。触发器允许您定义一组操作，这些操作通过对指定的表进行删除、插入或更新操作来执行或触发。触发器可用于写入其他表、修改输入值以及发布警报信息。
　　
　　第一节提供关键字的概念性概述。接着，通过示例和图表进一步探讨参考完整性、约束以及触发器。
　　
　　
　　1、关键字
　　关键字是可用来标识或存取特定行的一组列。
　　
　　由不止一列组成的关键字称为组合关键字。在具有组合关键字的表中，组合关键字中各列的排序不受这些列在表中排序的约束。
　　
　　
　　唯一关键字
　　唯一关键字被定义为它的任何值都不相同。唯一关键字的列不能包含空值。在执行 INSERT 和 UPDATE 语句期间，数据库管理程序强制执行该约束。一个表可以有多个唯一关键字。唯一关键字是可选的，并且可在 CREATE TABLE 或 ALTER TABLE 语句中定义。
　　
　　
　　主关键字
　　主关键字是一种唯一关键字，表定义的一部分。一个表不能有多个主关键字，并且主关键字的列不能包含空值。主关键字是可选的，并且可在 CREATE TABLE 或 ALTER TABLE 语句中定义。
　　
　　
　　外部关键字
　　外部关键字在参考约束的定义中指定。一个表可以有零个或多个外部关键字。如果组合外部关键字的值的任何部分为空，则该值为空。外部关键字是可选的，并且可在 CREATE TABLE 语句或 ALTER TABLE 语句中定义。
　　
　　
　　2、唯一约束
　　唯一约束确保关键字的值在表中是唯一的。唯一约束是可选的，并且可以通过使用指定 PRIMARY KEY 或 UNIQUE 子句的 CREATE TABLE 或 ALTER TABLE 语句来定义唯一约束。例如，可在一个表的雇员编号列上定义一个唯一约束，以确保每个雇员有唯一的编号。
　　
　　
　　3、参考完整性约束
　　通过定义唯一约束和外部关键字，可以定义表与表之间的关系，从而实施某些商业规则。唯一关键和外部关键字约束的组合通常称为参考完整性约束。外部关键字所引用的唯一约束称为父关键字。外部关键字表示特定的父关键字，或与特定的父关键字相关。例如，某规则可能规定每个雇员（EMPLOYEE 表）必须属于某现存的部门（DEPARTMENT 表）。因此，将 EMPLOYEE 表中的“部门号”定义为外部关键字，而将 DEPARTMENT 表中的“部门号”定义为主关键字。下列图表提供参考完整性约束的直观说明。
　　
　　图 4. 外部约束和主约束定义关系并保护数据
　　
　　
　　
　　4、表检查约束
　　表检查约束指定对于表的每行都要进行判定的条件。可对个别列指定检查约束。可使用 CREATE 或 ALTER TABLE 语句添加检查约束。
　　
　　下列语句创建具有下列约束的表：
　　
　　部门编号的值必须在范围 10 至 100 内
　　雇员的职务只能为下列之一： "Sales"、"Mgr"或"Clerk"
　　1986 年之前雇用的每个雇员的工资必须超过 $40,500。
　　
　　
　　 CREATE TABLE EMP
　　　　　 (ID　　　　　 SMALLINT NOT NULL,
　　　　　　NAME　　　　 VARCHAR(9),
　　　　　　DEPT　　　　 SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
　　　　　　JOB　　　　　CHAR(5)　 CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),
　　　　　　HIREDATE　　 DATE,
　　　　　　SALARY　　　 DECIMAL(7,2),
　　　　　　COMM　　　　 DECIMAL(7,2),
　　　　　　PRIMARY KEY (ID),
　　　　　　CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )
　　
　　仅当条件判定为假时才会违反约束。例如，如果插入行的 DEPT 为空值，则插入继续进行而不出错，尽管 DEPT 的值应该象约束中定义的那样在 10 和 100 之间。
　　
　　下列语句将一个约束添加至名为 COMP 的 EMPLOYEE 表中，该约束为雇员的总报酬必须超过 $15,000：
　　
　　
　　 ALTER TABLE EMP
　　　　ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
　　
　　将检查表中现存的行以确保这些行不违反新约束。可通过使用如下的 SET CONSTRAINTS 语句将此检查延期：
　　
　　 SET CONSTRAINTS FOR EMP OFF
　　 ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
　　 SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED
　　
　　
　　首先使用 SET CONSTRAINTS 语句以延期对表的约束检查。然后可将一个或多个约束添加至表而不检查这些约束。接着再次发出 SET CONSTRAINTS 语句，反过来将约束检查打开并执行任何延期的约束检查。
　　
　　
　　5、触发器
　　一个触发器定义一组操作，这组操作通过修改指定基表中数据的操作来激活。
　　
　　可使用触发器来执行对输入数据的验证；自动生成新插入行的值；为了交叉引用而读取其他表；为了审查跟踪而写入其他表；或通过电子邮件信息支持警报。使用触发器将导致应用程序开发及商业规则的全面实施更快速并且应用程序和数据的维护更容易。
　　
　　DB2 通用数据库支持几种类型的触发器。可定义触发器在 DELETE、INSERT 或 UPDATE 操作之前或之后激活。每个触发器包括一组称为触发操作的 SQL 语句，这组语句可包括一个可选的搜索条件。
　　
　　可进一步定义后触发器以对每一行都执行触发操作，或对语句执行一次触发操作，而前触发器总是对每一行都执行触发操作。
　　
　　在 INSERT、UPDATE 或 DELETE 语句之前使用触发器，以便在执行触发操作之前检查某些条件，或在将输入值存储在表中之前更改输入值。使用后触发器，以便在必要时传播值或执行其他任务，如发送信息等，这些任务可能是触发器操作所要求的。
　　
　　下列示例说明了前触发器和后触发器的使用。考虑一个记录并跟踪股票价格波动的应用程序。该数据库包含两个表，CURRENTQUOTE 和 QUOTEHISTORY，定义如下：
　　
　　
　　 CREATE TABLE CURRENTQUOTE
　　 (SYMBOL VARCHAR(10),
　　　QUOTE DECIMAL(5,2),
　　　STATUS VARCHAR(9))
　　
　　 CREATE TABLE　QUOTEHISTORY
　　 (SYMBOL VARCHAR(10),
　　　QUOTE DECIMAL(5,2),
　　　TIMESTAMP TIMESTAMP)
　　
　　当使用如下语句更新 CURRENTQUOTE 的 QUOTE 列时：
　　
　　
　　 UPDATE CURRENTQUOTE
　　　　SET QUOTE = 68.5
　　　　WHERE SYMBOL = 'IBM'
　　
　　应更新 CURRENTQUOTE 的 STATUS 列以反映股票是否：
　　
　　在升值
　　处于本年度的新高
　　在下跌
　　处于本年度的新低
　　价位稳定
　　这通过使用下列前触发器来实现：
　　
　　(1)
　　
　　 CREATE TRIGGER STOCK_STATUS
　　　　NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
　　　　REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
　　 FOR EACH ROW MODE DB2SQL　　　　　　　　　　　　　　　　　
　　
　　(2)
　　
　　　SET NEWQUOTE.STATUS =
　　
　　(3)
　　
　　　　 CASE
　　
　　(4)
　　
　　　　　　WHEN NEWQUOTE.QUOTE >=
　　　　　　　　　　　(SELECT MAX(QUOTE)
　　　　　　　　　　　　　FROM QUOTEHISTORY　　　　
　　　　　　　　　　　　　WHERE SYMBOL = NEWQUOTE.SYMBOL　　　　　　　　
　　　　　　　　　　　　　AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )　　
　　　　　　THEN 'High'
　　
　　(5)
　　
　　　　　WHEN NEWQUOTE.QUOTE <=　　　　　　　　　　　　　　
　　　　　　　　　　　(SELECT MIN(QUOTE)
　　　　　　　　　　　　　FROM QUOTEHISTORY
　　　　　　　　　　　　　WHERE SYMBOL = NEWQUOTE.SYMBOL　　　　　　　　　　　
　　　　　　　　　　　　　AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )　　　
　　　　　THEN 'Low'
　　
　　(6)
　　
　　　　　WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
　　　　　　 THEN 'Rising'
　　　　　WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE　　　　　　　　　　　
　　　　　　 THEN 'Dropping'
　　　　　WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
　　　　　　 THEN 'Steady'
　　 END　　　　　　　　　　　　　　　　　　　　　　　　　
　　
　　
　　
　　(1)
　　此代码块将名为 STOCK_STATUS 的触发器定义为一个应该在更新 CURRENTQUOTE 表的 QUOTE 列之前激活的触发器。第二行指定，在将 CURRENTQUOTE 表的实际更新所引起的任何更改应用于数据库之前，要应用触发操作。第二行也意味着触发操作将不会激活任何其他触发器。第三行指定一些名称，必须将这些名称作为列名的限定符用于新值 (NEWQUOTE) 和旧值 (OLDQUOTE)。用这些相关名（NEWQUOTE 和 OLDQUOTE）限定的列名称为转换变量。第四行表示应对每一行都执行触发操作。