觸發器:特定事件自動執行的 PL/SQL 區塊,類似存儲過程但不可直接呼叫,由事件驅動。
| 類型 | 建立對象 | 觸發事件 | 典型場景 |
|---|---|---|---|
| DML Trigger | Table | INSERT/UPDATE/DELETE | 審計、衍生欄位、複雜約束 |
| INSTEAD OF | View | INSERT/UPDATE/DELETE | 多表視圖可更新化 |
| DDL Trigger | Database/Schema | CREATE/ALTER/DROP | 結構變更審計、防誤刪 |
| Database Event | Database/Schema | STARTUP/SHUTDOWN/LOGON/LOGOFF/ERROR | 啟動初始化、登入審計 |
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;
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 新增一筆。
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;
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;
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;
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;
-- 視圖:班級平均分
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 子句可減少觸發器啟動次數DB_TRIGGER_MAX_DEPTH 或邏輯互斥