| [轉(zhuǎn)帖]用約束和觸發(fā)器實施商業(yè)規(guī)則 |
| 在商界，我們的確通常需要確保始終實施某些規(guī)則。例如，參與項目的雇員必須被雇用?；蛘呦胍承┦录杏媱澋匕l(fā)生。例如，如果銷售員售出一批商品，則應(yīng)增加其傭金。 DB2 通用數(shù)據(jù)庫為此提供了一套有用的方法。 唯一約束是禁止在表的一列或多列中出現(xiàn)重復(fù)值的規(guī)則。 參考完整性約束確保在整個指定的表中數(shù)據(jù)一致性。 表檢查約束是一些條件，它們定義為表定義的一部分，限制一列或多列中使用的值。觸發(fā)器允許您定義一組操作，這些操作通過對指定的表進行刪除、插入或更新操作來執(zhí)行或觸發(fā)。觸發(fā)器可用于寫入其他表、修改輸入值以及發(fā)布警報信息。 第一節(jié)提供關(guān)鍵字的概念性概述。接著，通過示例和圖表進一步探討參考完整性、約束以及觸發(fā)器。 1、關(guān)鍵字 關(guān)鍵字是可用來標識或存取特定行的一組列。 由不止一列組成的關(guān)鍵字稱為組合關(guān)鍵字。在具有組合關(guān)鍵字的表中，組合關(guān)鍵字中各列的排序不受這些列在表中排序的約束。 唯一關(guān)鍵字 唯一關(guān)鍵字被定義為它的任何值都不相同。唯一關(guān)鍵字的列不能包含空值。在執(zhí)行 INSERT 和 UPDATE 語句期間，數(shù)據(jù)庫管理程序強制執(zhí)行該約束。一個表可以有多個唯一關(guān)鍵字。唯一關(guān)鍵字是可選的，并且可在 CREATE TABLE 或 ALTER TABLE 語句中定義。 主關(guān)鍵字 主關(guān)鍵字是一種唯一關(guān)鍵字，表定義的一部分。一個表不能有多個主關(guān)鍵字，并且主關(guān)鍵字的列不能包含空值。主關(guān)鍵字是可選的，并且可在 CREATE TABLE 或 ALTER TABLE 語句中定義。 外部關(guān)鍵字 外部關(guān)鍵字在參考約束的定義中指定。一個表可以有零個或多個外部關(guān)鍵字。如果組合外部關(guān)鍵字的值的任何部分為空，則該值為空。外部關(guān)鍵字是可選的，并且可在 CREATE TABLE 語句或 ALTER TABLE 語句中定義。 2、唯一約束 唯一約束確保關(guān)鍵字的值在表中是唯一的。唯一約束是可選的，并且可以通過使用指定 PRIMARY KEY 或 UNIQUE 子句的 CREATE TABLE 或 ALTER TABLE 語句來定義唯一約束。例如，可在一個表的雇員編號列上定義一個唯一約束，以確保每個雇員有唯一的編號。 3、參考完整性約束 通過定義唯一約束和外部關(guān)鍵字，可以定義表與表之間的關(guān)系，從而實施某些商業(yè)規(guī)則。唯一關(guān)鍵和外部關(guān)鍵字約束的組合通常稱為參考完整性約束。外部關(guān)鍵字所引用的唯一約束稱為父關(guān)鍵字。外部關(guān)鍵字表示特定的父關(guān)鍵字，或與特定的父關(guān)鍵字相關(guān)。例如，某規(guī)則可能規(guī)定每個雇員（EMPLOYEE 表）必須屬于某現(xiàn)存的部門（DEPARTMENT 表）。因此，將 EMPLOYEE 表中的“部門號”定義為外部關(guān)鍵字，而將 DEPARTMENT 表中的“部門號”定義為主關(guān)鍵字。下列圖表提供參考完整性約束的直觀說明。 圖 4. 外部約束和主約束定義關(guān)系并保護數(shù)據(jù)  4、表檢查約束 表檢查約束指定對于表的每行都要進行判定的條件?？蓪€別列指定檢查約束。可使用 CREATE 或 ALTER TABLE 語句添加檢查約束。 下列語句創(chuàng)建具有下列約束的表： 部門編號的值必須在范圍 10 至 100 內(nèi) 雇員的職務(wù)只能為下列之一： "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 為空值，則插入繼續(xù)進行而不出錯，盡管 DEPT 的值應(yīng)該象約束中定義的那樣在 10 和 100 之間。 下列語句將一個約束添加至名為 COMP 的 EMPLOYEE 表中，該約束為雇員的總報酬必須超過 $15,000： ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000) 將檢查表中現(xiàn)存的行以確保這些行不違反新約束?？赏ㄟ^使用如下的 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 語句以延期對表的約束檢查。然后可將一個或多個約束添加至表而不檢查這些約束。接著再次發(fā)出 SET CONSTRAINTS 語句，反過來將約束檢查打開并執(zhí)行任何延期的約束檢查。 5、觸發(fā)器 一個觸發(fā)器定義一組操作，這組操作通過修改指定基表中數(shù)據(jù)的操作來激活。 可使用觸發(fā)器來執(zhí)行對輸入數(shù)據(jù)的驗證；自動生成新插入行的值；為了交叉引用而讀取其他表；為了審查跟蹤而寫入其他表；或通過電子郵件信息支持警報。使用觸發(fā)器將導(dǎo)致應(yīng)用程序開發(fā)及商業(yè)規(guī)則的全面實施更快速并且應(yīng)用程序和數(shù)據(jù)的維護更容易。 DB2 通用數(shù)據(jù)庫支持幾種類型的觸發(fā)器。可定義觸發(fā)器在 DELETE、INSERT 或 UPDATE 操作之前或之后激活。每個觸發(fā)器包括一組稱為觸發(fā)操作的 SQL 語句，這組語句可包括一個可選的搜索條件。 可進一步定義后觸發(fā)器以對每一行都執(zhí)行觸發(fā)操作，或?qū)φZ句執(zhí)行一次觸發(fā)操作，而前觸發(fā)器總是對每一行都執(zhí)行觸發(fā)操作。 在 INSERT、UPDATE 或 DELETE 語句之前使用觸發(fā)器，以便在執(zhí)行觸發(fā)操作之前檢查某些條件，或在將輸入值存儲在表中之前更改輸入值。使用后觸發(fā)器，以便在必要時傳播值或執(zhí)行其他任務(wù)，如發(fā)送信息等，這些任務(wù)可能是觸發(fā)器操作所要求的。 下列示例說明了前觸發(fā)器和后觸發(fā)器的使用?？紤]一個記錄并跟蹤股票價格波動的應(yīng)用程序。該數(shù)據(jù)庫包含兩個表，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‘ 應(yīng)更新 CURRENTQUOTE 的 STATUS 列以反映股票是否： 在升值 處于本年度的新高 在下跌 處于本年度的新低 價位穩(wěn)定 這通過使用下列前觸發(fā)器來實現(xiàn)： (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 的觸發(fā)器定義為一個應(yīng)該在更新 CURRENTQUOTE 表的 QUOTE 列之前激活的觸發(fā)器。第二行指定，在將 CURRENTQUOTE 表的實際更新所引起的任何更改應(yīng)用于數(shù)據(jù)庫之前，要應(yīng)用觸發(fā)操作。第二行也意味著觸發(fā)操作將不會激活任何其他觸發(fā)器。第三行指定一些名稱，必須將這些名稱作為列名的限定符用于新值 (NEWQUOTE) 和舊值 (OLDQUOTE)。用這些相關(guān)名（NEWQUOTE 和 OLDQUOTE）限定的列名稱為轉(zhuǎn)換變量。第四行表示應(yīng)對每一行都執(zhí)行觸發(fā)操作。 (2) 這標記此觸發(fā)器的觸發(fā)操作中第一個也是唯一的一個 SQL 語句的開始。 SET 轉(zhuǎn)換變量語句在一個觸發(fā)器中用來將值賦給表的行中的列，該表正在由激活該觸發(fā)器的語句進行更新。此語句正將一個值賦給 CURRENTQUOTE 表的 STATUS 列。 (3) 該賦值語句右邊使用的表達式為 CASE 表達式。 CASE 表達式擴充為 END 關(guān)鍵字。 (4) 第一種情況檢查新報價 (NEWQUOTE.QUOTE) 是否超過當前日歷年度中股票符號的最高價。子查詢正在使用由跟在后面的后觸發(fā)器更新的 QUOTEHISTORY 表。 (5) 第二種情況檢查新報價 (NEWQUOTE.QUOTE) 是否小于當前日歷年度中股票符號的最低價。子查詢正在使用由跟在后面的后觸發(fā)器更新的 QUOTEHISTORY 表。 (6) 最后三種情況將新報價 (NEWQUOTE.QUOTE) 與表 (OLDQUOTE.QUOTE) 中的報價比較，以確定新報價是大于、小于還是等于舊報價。 SET 轉(zhuǎn)換變量語句在此處結(jié)束。 除了更新 CURRENTQUOTE 表中的項之外，還需要通過將新報價連同時間戳記一起復(fù)制到 QUOTEHISTORY 表中來創(chuàng)建一個審查記錄。這通過使用下列后觸發(fā)器來實現(xiàn)： (1) CREATE TRIGGER RECORD_HISTORY AFTER UPDATE OF QUOTE ON CURRENTQUOTE REFERENCING NEW AS NEWQUOTE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC (2) INSERT INTO QUOTEHISTORY VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP); END (1) 此代碼塊將命名為 RECORD_HISTORY 的觸發(fā)器定義為應(yīng)該在更新 CURRENTQUOTE 表的 QUOTE 列之后激活的觸發(fā)器。第三行指定應(yīng)該作為列名的限定符用于新值 (NEWQUOTE) 的名稱。第四行表示應(yīng)對每一行都執(zhí)行觸發(fā)操作。 (2) 此觸發(fā)器的觸發(fā)操作包括單個 SQL 語句，該語句使用已更新的行中的數(shù)據(jù)（NEWQUOTE.SYMBOL 和 NEWQUOTE.QUOTE）和當前的時間戳記將該行插入 QUOTEHISTORY 表。 CURRENT TIMESTAMP 是包含時間戳記的專用寄存器。專用寄存器中提供了列表和解釋。 |
