Oracle 觸發器實戰：序列自動遞增主鍵的標準實作模式

業務場景

資料表主鍵需要自動遞增，Oracle 12c 前無原生 Identity Column，標準做法是 Sequence + Row-Level Trigger 組合。

完整實作步驟

1. 建立序列

CREATE SEQUENCE LZEAM_SP_LOOP_EQ_SEQ 
  MINVALUE 1 
  INCREMENT BY 1 
  START WITH 1 
  NOCACHE 
  NOCYCLE;

NOCACHE 確保序列值不會因實例重啟而跳號（效能稍降），NOCYCLE 避免達到上限後循環。

2. 測試序列

SELECT LZEAM_SP_LOOP_EQ_SEQ.NEXTVAL FROM DUAL;

3. 建立 BEFORE INSERT 行觸發器

CREATE OR REPLACE TRIGGER LZEAM.LZEAM_SP_LOOP_EQ_TRIGGER
BEFORE INSERT ON SP_LOOP_EQ
FOR EACH ROW
BEGIN
  SELECT LZEAM.LZEAM_SP_LOOP_EQ_SEQ.NEXTVAL 
  INTO :NEW.LOOP_EQ_CODE 
  FROM DUAL;
END;
/

關鍵點：

• BEFORE INSERT：在寫入前填充主鍵
• FOR EACH ROW：逐行觸發，確保每筆新資料都取號
• :NEW.LOOP_EQ_CODE：直接指派給新資料列的主鍵欄位

4. 查詢與管理觸發器

-- 查看當前使用者觸發器
SELECT * FROM USER_TRIGGERS;

-- 停用/啟用特定觸發器
ALTER TRIGGER LZEAM_SP_LOOP_EQ_TRIGGER DISABLE;
ALTER TRIGGER LZEAM_SP_LOOP_EQ_TRIGGER ENABLE;

-- 停用/啟用資料表所有觸發器
ALTER TABLE SP_LOOP_EQ DISABLE ALL TRIGGERS;
ALTER TABLE SP_LOOP_EQ ENABLE ALL TRIGGERS;

進階考量

Oracle 12c+ Identity Column

12c 起可直接定義：

CREATE TABLE t (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(100)
);

優點：無需序列與觸發器，效能更好，支援 GENERATED BY DEFAULT ON NULL 允許手動指定值。

命名慣例建議

• 序列：SEQ_<Schema>_<Table>_<Column> 如 SEQ_LZEAM_SP_LOOP_EQ_LOOP_EQ_CODE
• 觸發器：TRG_<Table>_BI_<Column> 如 TRG_SP_LOOP_EQ_BI_LOOP_EQ_CODE

效能優化

• 高併發場景建議使用 CACHE 1000 減少序列鎖競爭
• 批次匯入時可暫時停用觸發器，手動指派主鍵
• 避免在觸發器中執行複雜邏輯或查詢

常見問題排查