Oracle 自增主鍵實現全方案：Sequence+Trigger、Identity Column、最佳實踐

方案一：Sequence + BEFORE INSERT Trigger (11g 及之前標準做法)

-- 1. 建表
CREATE TABLE example (
  id      NUMBER(4) PRIMARY KEY,
  name    VARCHAR2(25),
  phone   VARCHAR2(10),
  address VARCHAR2(50)
);

-- 2. 建立序列
CREATE SEQUENCE emp_sequence
  INCREMENT BY 1
  START WITH 1
  NOMAXVALUE
  NOCYCLE
  NOCACHE;  -- 關鍵業務建議 NOCACHE 避免跳號

-- 3. 建立觸發器
CREATE OR REPLACE TRIGGER example_bir
BEFORE INSERT ON example
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
  SELECT emp_sequence.NEXTVAL INTO :NEW.id FROM dual;
END;
/

-- 4. 測試
INSERT INTO example (name, phone, address) VALUES ('Cao', '56498543', 'Heibei');
SELECT * FROM example;  -- id 自動填入 1

方案二：保護主鍵不被手動修改/刪除的完整觸發器組

-- 防止 UPDATE ID
CREATE OR REPLACE TRIGGER example_id_upd
BEFORE UPDATE OF id ON example
FOR EACH ROW
BEGIN
  RAISE_APPLICATION_ERROR(-20000, 'Primary key ID cannot be updated.');
END;
/

-- 依狀態碼防止刪除/修改已審核資料
CREATE OR REPLACE TRIGGER example_del_chk
BEFORE DELETE ON example
FOR EACH ROW
BEGIN
  IF :OLD.status = '3' THEN
    RAISE_APPLICATION_ERROR(-20001, 'Approved record cannot be deleted.');
  END IF;
END;
/

方案三：Oracle 12c+ Identity Column (原生自增，強烈推薦)

CREATE TABLE example (
  id      NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  name    VARCHAR2(25),
  phone   VARCHAR2(10),
  address VARCHAR2(50),
  CONSTRAINT pk_example PRIMARY KEY (id)
);
-- 完全取代 Sequence + Trigger，優點：
-- 1. 無需額外物件，DDL 簡潔
-- 2. 效能優於 Trigger (無 PL/SQL 呼叫開銷)
-- 3. 支援 GENERATED BY DEFAULT ON NULL (允許手動覆寫)
-- 4. 可直接 ALTER TABLE ... MODIFY id GENERATED ... 調整參數

Sequence 參數深度解析

遷移建議

• 新專案 (12c+) 直接用 IDENTITY。
• 舊專案維護：保留 Sequence+Trigger，但檢查 NOCACHE 設定。
• 跨資料庫相容：應用層封裝 Repository，隱藏自增實現細節。