萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle Trigger 觸發器完整指南:類型、語法與六大實戰場景

核心概念

觸發器:特定事件自動執行的 PL/SQL 區塊,類似存儲過程但不可直接呼叫,由事件驅動。

六大分類

類型建立對象觸發事件典型場景
DML TriggerTableINSERT/UPDATE/DELETE審計、衍生欄位、複雜約束
INSTEAD OFViewINSERT/UPDATE/DELETE多表視圖可更新化
DDL TriggerDatabase/SchemaCREATE/ALTER/DROP結構變更審計、防誤刪
Database EventDatabase/SchemaSTARTUP/SHUTDOWN/LOGON/LOGOFF/ERROR啟動初始化、登入審計

通用語法 (DML)

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE [OF col...] | DELETE} [OR ...]
ON [schema.]table_name | view_name
[FOR EACH ROW [WHEN (condition)]]
DECLARE
  -- 宣告區
BEGIN
  -- 執行區
EXCEPTION
  -- 異常處理
END;

六大實戰場景 (以 TRIGGERTEST 表為例)

1. DML 行級觸發器:同步備份表

CREATE TABLE triggertest (id VARCHAR2(20), name VARCHAR2(20), score NUMBER);
CREATE TABLE tab1 AS SELECT * FROM triggertest;

CREATE OR REPLACE TRIGGER trigger1
AFTER INSERT ON triggertest
FOR EACH ROW
BEGIN
  INSERT INTO tab1(id, name) VALUES ('22','33');
END;

測試:INSERT INTO triggertest(id) VALUES('aabbcc'); → tab1 新增一筆。

2. 限制非工作時間修改表

CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT OR DELETE OR UPDATE ON triggertest
FOR EACH ROW
BEGIN
  IF TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN') 
     OR TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '09:00' AND '18:00' THEN
    RAISE_APPLICATION_ERROR(-20001, '非工作時間禁止修改');
  END IF;
END;

3. 業務規則:特定人員分數只能升不能降

CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT OR DELETE OR UPDATE ON triggertest
FOR EACH ROW
WHEN (OLD.NAME = '月兒')
BEGIN
  IF UPDATING('SCORE') AND :NEW.SCORE < :OLD.SCORE THEN
    RAISE_APPLICATION_ERROR(-20001, '月兒分數只能升不能降');
  END IF;
END;

4. 觸發器中呼叫存儲過程

CREATE OR REPLACE PROCEDURE testpro1 AS
BEGIN INSERT INTO tab1 VALUES('AAA','BBB',200); END;

CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT OR DELETE OR UPDATE ON triggertest
FOR EACH ROW
BEGIN testpro1(); END;

5. 級聯更新:主表名稱變更同步從表

CREATE OR REPLACE TRIGGER trigger1
AFTER UPDATE OF name ON triggertest
FOR EACH ROW
BEGIN
  UPDATE tab1 SET name = :NEW.name WHERE name = :OLD.name;
END;

6. INSTEAD OF 讓不可更新視圖可更新

-- 視圖:班級平均分
CREATE VIEW student_view AS
SELECT classid, AVG(score) avg_score FROM student GROUP BY classid;

-- 直接 DELETE 視圖會報 ORA-01732
-- 解決:INSTEAD OF DELETE
CREATE OR REPLACE TRIGGER student_view_delete
INSTEAD OF DELETE ON student_view
FOR EACH ROW
BEGIN
  DELETE FROM student WHERE classid = :OLD.classid;
END;

關鍵提醒

  • :OLD/:NEW 僅在行級觸發器可用
  • WHEN 子句可減少觸發器啟動次數
  • 避免觸發器中執行長時間作業,影響 DML 效能
  • 循環觸發風險:A 觸發 B,B 又觸發 A → 設定 DB_TRIGGER_MAX_DEPTH 或邏輯互斥
copyright © 萬盛學電腦網 all rights reserved