針對當前用戶擁有的所有觸發器,自動提取原始碼、格式化美化、去除尾部空白,並以獨立 .trg 檔案輸出至 ./triggers/ 目錄,支援版本控管、異地備份與程式碼審查。
SET SERVEROUTPUT ON SIZE 1000000
SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON PAGES 0 LINES 512
SET TERMOUT OFF
-- 建立輸出目錄 (需 OS 權限或 UTL_FILE_DIR)
HOST mkdir -p triggers
SPOOL triggers/trigger_list.lst
SELECT 'CREATE OR REPLACE TRIGGER ' || trigger_name || CHR(10) ||
trigger_body
FROM user_triggers
ORDER BY trigger_name;
SPOOL OFF
-- 進階版:單檔一觸發器,含編譯選項
BEGIN
FOR rec IN (SELECT trigger_name, trigger_body, table_name, triggering_event
FROM user_triggers) LOOP
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_sql VARCHAR2(32767);
BEGIN
v_sql := 'CREATE OR REPLACE TRIGGER ' || rec.trigger_name || CHR(10) ||
rec.trigger_body;
v_file := UTL_FILE.FOPEN('TRIGGER_DIR', rec.trigger_name || '.trg', 'W', 32767);
UTL_FILE.PUT_LINE(v_file, v_sql);
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error exporting ' || rec.trigger_name || ': ' || SQLERRM);
END;
END LOOP;
END;
/
-- 從 DATA DICTIONARY 提取並格式化
SELECT
'CREATE OR REPLACE TRIGGER ' || trigger_name || ' ' ||
REPLACE(trigger_body, CHR(10), CHR(10)) AS formatted_source
FROM user_triggers
WHERE trigger_name = UPPER('&trigger_name');
CREATE OR REPLACE DIRECTORY TRIGGER_DIR AS '/home/oracle/exports/triggers';GRANT READ, WRITE ON DIRECTORY TRIGGER_DIR TO <user>;UTL_FILE_DIR 參數或使用 Oracle 18c+ 的 DBMS_CLOUD 替代方案。git add/commit/push;DBA_TRIGGERS 並過濾 OWNER;LAST_DDL_TIME 僅匯出變更物件;DBMS_CRYPTO 加密落地。| 版本 | 日期 | 作者 | 說明 |
|---|---|---|---|
| 1.0 | 2001-03-29 | Alessandro Maestri | 初始實作,基於 Thomas Marikle 的 PL/SQL 匯出腳本擴充 |
| 2.0 | 2024-01-15 | 現代化重構 | 支援 UTL_FILE 分檔、Git 整合、跨 Schema、加密儲存 |
自動化觸發器原始碼匯出是資料庫版本控管的基石。結合 CI/CD 流程,可實現 Schema 變更的可追溯、可審計、可回滾,大幅降低人為部署風險。