Oracle 觸發器實戰：實現表級實時同步備份

場景

業務表 USERTEST 異動（增/刪/改）需即時同步至備份表 USERTEST_TEMP，結構完全一致。

建表

CREATE TABLE USERTEST (
  NAME VARCHAR2(20) NOT NULL,
  AGE NUMBER,
  ISDELETE VARCHAR2(4)
);

CREATE TABLE USERTEST_TEMP (
  NAME VARCHAR2(20) NOT NULL,
  AGE NUMBER,
  ISDELETE VARCHAR2(4)
);

觸發器核心代碼

CREATE OR REPLACE TRIGGER TR_USER_TEMP
BEFORE INSERT OR UPDATE OR DELETE ON USERTEST
FOR EACH ROW
DECLARE
BEGIN
  IF INSERTING THEN
    INSERT INTO USERTEST_TEMP (NAME, AGE, ISDELETE)
    VALUES (:NEW.NAME, :NEW.AGE, :NEW.ISDELETE);
  ELSIF DELETING THEN
    DELETE FROM USERTEST_TEMP WHERE NAME = :OLD.NAME;
  ELSIF UPDATING THEN
    UPDATE USERTEST_TEMP
    SET AGE = :NEW.AGE, ISDELETE = :NEW.ISDELETE
    WHERE NAME = :OLD.NAME;
  END IF;
END;
/
-- 注意：標點必須為英文半角，否則編譯報錯

關鍵語法解析

觸發器限制清單（必背）

1. 不接受參數
2. 同表同類型同觸發時機最多 1 個，總計不超 12 個
3. 觸發器越多，DML 效能影響越大
4. 體積限制 32KB，超大邏輯請拆分為 Procedure + CALL
5. 體內僅允許 DML (SELECT/INSERT/UPDATE/DELETE)，**禁用 DDL**
6. **禁用事務控制** (COMMIT/ROLLBACK/SAVEPOINT)
7. 調用的 Procedure/Function 也不得含事務控制
8. 不得宣告 LONG/BLOB 變數，:NEW/:OLD 不能引用 LONG/BLOB 欄位
9. INSTEAD OF 觸發器僅能為行級，用於 View 可更新

驗證測試

INSERT INTO USERTEST VALUES ('Alice', 25, 'N');
UPDATE USERTEST SET AGE=26 WHERE NAME='Alice';
DELETE FROM USERTEST WHERE NAME='Alice';
SELECT * FROM USERTEST_TEMP;  -- 應與 USERTEST 同步

查詢現有觸發器

SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, STATUS
FROM USER_TRIGGERS;