資料庫完整性約束：Domain 定義與 Trigger 進階應用

資料庫完整性約束：Domain 定義與 Trigger 進階應用

Domain（域）中的完整性限制

SQL 支援 Domain 概念，可用 CREATE DOMAIN 定義資料型別及其約束條件。

範例 1：建立性別域

CREATE DOMAIN GenderDomain CHAR(2) CHECK (VALUE IN ('男', '女'));

後續欄位定義可簡化為：Ssex GenderDomain。

範例 2：命名約束

CREATE DOMAIN GenderDomain CHAR(2) CONSTRAINT GD CHECK (VALUE IN ('男', '女'));

範例 3：修改域約束

-- 刪除舊約束
ALTER DOMAIN GenderDomain DROP CONSTRAINT GD;
-- 新增新約束（改為 1/0）
ALTER DOMAIN GenderDomain ADD CONSTRAINT GDD CHECK (VALUE IN ('1', '0'));

Trigger（觸發器）

觸發器是定義在關聯表上、由事件驅動的特殊程序，由伺服器自動激活，可執行比 CHECK 約束更複雜的檢查與操作，提供更精細、強大的資料控制能力。

建立觸發器語法

CREATE TRIGGER <觸發器名> {BEFORE | AFTER} <觸發事件> ON <表名>
FOR EACH {ROW | STATEMENT} [WHEN <觸發條件>] <觸發動作體>

• 建立者：表擁有者
• 觸發事件：INSERT、DELETE、UPDATE
• 類型：行級 (FOR EACH ROW) / 敘述級 (FOR EACH STATEMENT)
• 觸發條件：WHEN 子句，條件為真才執行；省略則無條件觸發
• 動作體：匿名 PL/SQL 區塊或呼叫預存程序

行級 vs 敘述級示例

表 TEACHER 有 1000 列，執行 UPDATE TEACHER SET Deptno=5;：

• 敘述級觸發器：動作執行 1 次
• 行級觸發器：動作執行 1000 次

範例 1：BEFORE 行級觸發器 — 薪資下限強制

CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
FOR EACH ROW
AS
BEGIN
  IF (new.Job = '教授') AND (new.Sal < 4000) THEN
    new.Sal := 4000;
  END IF;
END;

範例 2：AFTER 行級觸發器 — 薪資變動稽核

-- 建立稽核表
CREATE TABLE Sal_log (
  Eno NUMERIC(4) REFERENCES teacher(eno),
  Sal NUMERIC(7,2),
  Username CHAR(10),
  Date TIMESTAMP
);

-- INSERT 觸發器
CREATE TRIGGER Insert_Sal
AFTER INSERT ON Teacher
FOR EACH ROW
AS
BEGIN
  INSERT INTO Sal_log VALUES (new.Eno, new.Sal, CURRENT_USER, CURRENT_TIMESTAMP);
END;

-- UPDATE 觸發器
CREATE TRIGGER Update_Sal
AFTER UPDATE ON Teacher
FOR EACH ROW
AS
BEGIN
  IF (new.Sal <> old.Sal) THEN
    INSERT INTO Sal_log VALUES (new.Eno, new.Sal, CURRENT_USER, CURRENT_TIMESTAMP);
  END IF;
END;

觸發器激活順序

1. 執行該表上所有 BEFORE 觸發器
2. 執行激活的 SQL 敘述
3. 執行該表上所有 AFTER 觸發器

UPDATE Teacher SET Sal=800 WHERE Ename='陳平';
-- 順序：
-- 1. Insert_Or_Update_Sal (BEFORE)
-- 2. UPDATE 敘述
-- 3. Insert_Sal (AFTER INSERT) -- 不會觸發，因為是 UPDATE
-- 4. Update_Sal (AFTER UPDATE)

刪除觸發器

DROP TRIGGER Insert_Sal ON Teacher;

資料庫完整性機制總覽

• 完整性約束定義機制
• 完整性檢查機制
• 違背約束時的處理動作