小議Oracle外來鍵約束脩改行為（四）

前面幾篇文章介紹了Oracle所支援的3種約束行為NO ACTION、DELETE SET NULL和DELETE CASCADE。

至於SQL標準中定義的其他操作，Oracle只能透過觸發器來實現，這裡給出一個簡單的UPDATE CASCADE操作的例子。

SQL> DROP TABLE T_C;

表已刪除。

SQL> DROP TABLE T_P;

表已刪除。

SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);

表已更改。

SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C
  2  FOREIGN KEY (FID)
  3  REFERENCES T_P (ID);

表已更改。

SQL> INSERT INTO T_P VALUES (1, 'A');

已建立 1 行。

SQL> INSERT INTO T_P VALUES (2, 'B');

已建立 1 行。

SQL> INSERT INTO T_C VALUES (1, 1, 'A');

已建立 1 行。

SQL> COMMIT;

提交完成。

檢查一下直接更新操作：

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A

SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;
UPDATE T_P SET ID = 3 WHERE ID = 1
*
第 1 行出現錯誤:
ORA-02292: 違反完整約束條件 (YANGTK.FK_T_C) - 已找到子記錄日誌

下面構造一個觸發器：

SQL> CREATE OR REPLACE TRIGGER T_P_UPDATE_CASCADE
  2  BEFORE UPDATE OF ID ON T_P
  3  FOR EACH ROW
  4  WHEN (NVL(NEW.ID, -1) != NVL(OLD.ID, -1))
  5  DECLARE
  6   V_STR VARCHAR2(32767);
  7  BEGIN
  8   FOR I IN (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
  9    FROM ALL_CONSTRAINTS
 10    WHERE CONSTRAINT_TYPE = 'R'
 11    AND R_OWNER = 'YANGTK'
 12    AND R_CONSTRAINT_NAME IN
 13     (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'T_P'))
 14   LOOP
 15    V_STR := 'UPDATE ' || I.TABLE_NAME || ' SET '; 
 16    FOR J IN (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS
 17     WHERE WNER = I.OWNER
 18     AND TABLE_NAME = I.TABLE_NAME
 19     AND CONSTRAINT_NAME = I.CONSTRAINT_NAME)
 20    LOOP
 21     V_STR := V_STR || J.COLUMN_NAME || ' = ' || :NEW.ID
 22      || ' WHERE ' || J.COLUMN_NAME || ' = ' || :OLD.ID;
 23    END LOOP;
 24    EXECUTE IMMEDIATE V_STR;
 25   END LOOP;
 26  END;
 27  /

觸發器已建立

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A

SQL> UPDATE T_P SET ID = 3 WHERE ID = 1;

已更新 1 行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         3 A
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          3 A

採用動態SQL的好處是，即使新增了新的外來鍵表，觸發器也不用重新編輯：

SQL> CREATE TABLE T_C1 (ID NUMBER, FID NUMBER, CONSTRAINT FK_T_C1
  2  FOREIGN KEY (FID) REFERENCES T_P);

表已建立。

SQL> INSERT INTO T_C1 VALUES (1, 3);

已建立 1 行。

SQL> UPDATE T_P SET ID = 1 WHERE ID = 3;

已更新 1 行。

SQL> SELECT * FROM T_P;

        ID NAME
---------- ------------------------------
         1 A
         2 B

SQL> SELECT * FROM T_C;

        ID        FID NAME
---------- ---------- ------------------------------
         1          1 A

SQL> SELECT * FROM T_C1;

        ID        FID
---------- ----------
         1          1