oracleテーブルのデータ変更時に自動的にログファイルに記録するトリガー

１．まず、対象スキーマにUTL_FILEの実行権限を付与する

echo GRANT EXECUTE ON SYS.UTL_FILE TO SCHEMANAME; | sqlplus / as sysdba

２．ついでに、ログ書き込み用ディレクトリオブジェクトを作成しておく

echo CREATE DIRECTORY TMPLOG AS 'D:\temp'; | sqlplus / as sysdba

３．本ちゃんのトリガー生成用SQL

WITH DEF AS (SELECT
  'tablename' AS TABLE_NAME
, 'TMPLOG' AS DIR_NAME
, 'test.txt' AS FILE_NAME
  FROM DUAL
),
TBC AS (SELECT
  USER_TAB_COMMENTS.COMMENTS AS TABLE_COMMENT
FROM
  DEF
  LEFT OUTER JOIN USER_TAB_COMMENTS
    ON DEF.TABLE_NAME = USER_TAB_COMMENTS.TABLE_NAME
),
ITM AS (SELECT
  USER_TAB_COLUMNS.COLUMN_ID AS ID
, USER_TAB_COLUMNS.COLUMN_NAME AS CLM
, USER_COL_COMMENTS.COMMENTS AS CMT
FROM
  USER_TAB_COLUMNS
  INNER JOIN USER_COL_COMMENTS
    ON USER_TAB_COLUMNS.TABLE_NAME = USER_COL_COMMENTS.TABLE_NAME
    AND USER_TAB_COLUMNS.COLUMN_NAME = USER_COL_COMMENTS.COLUMN_NAME
  INNER JOIN DEF
    ON USER_TAB_COLUMNS.TABLE_NAME = DEF.TABLE_NAME
),
INS0 AS (SELECT
'    NBUF := REPLACE(REPLACE(:NEW.' || ITM.CLM || ', CHR(13), ''\r''), CHR(10), ''\n''); ' || CHR(13) ||
'    IF LTRIM(LPAD(NBUF, 25)) = NBUF THEN NBUF := LPAD(NBUF, 25); END IF; ' || CHR(13) ||
'    UTL_FILE.PUT_LINE_NCHAR(FD, RPAD('' I ' || NVL2(ITM.CMT, ITM.CLM || '(' || ITM.CMT || ')', ITM.CLM) || ''', 48) || NBUF); ' AS COL
FROM
  ITM
ORDER BY
  ITM.ID
),
INS AS (SELECT 
'  IF INSERTING THEN' AS COL
FROM DUAL
UNION ALL
SELECT COL FROM INS0
UNION ALL
SELECT 
'  END IF;' AS COL
FROM DUAL
),
DEL0 AS (SELECT
'    OBUF := REPLACE(REPLACE(:OLD.' || ITM.CLM || ', CHR(13), ''\r''), CHR(10), ''\n''); ' || CHR(13) ||
'    IF LTRIM(LPAD(OBUF, 25)) = OBUF THEN OBUF := LPAD(OBUF, 25); END IF; ' || CHR(13) ||
'    UTL_FILE.PUT_LINE_NCHAR(FD, RPAD('' D ' || NVL2(ITM.CMT, ITM.CLM || '(' || ITM.CMT || ')', ITM.CLM) || ''', 48) || OBUF); ' AS COL
FROM
  ITM
ORDER BY
  ITM.ID
),
DEL AS (SELECT 
'  IF DELETING THEN' AS COL
FROM DUAL
UNION ALL
SELECT COL FROM DEL0
UNION ALL
SELECT 
'  END IF;' AS COL
FROM DUAL
),
UPD AS (SELECT
'  IF UPDATING(''' || ITM.CLM || ''') THEN ' || CHR(13) ||
'    OBUF := REPLACE(REPLACE(:OLD.' || ITM.CLM || ', CHR(13), ''\r''), CHR(10), ''\n''); ' || CHR(13) ||
'    IF LTRIM(LPAD(OBUF, 25)) = OBUF THEN OBUF := LPAD(OBUF, 25); END IF; ' || CHR(13) ||
'    NBUF := REPLACE(REPLACE(:NEW.' || ITM.CLM || ', CHR(13), ''\r''), CHR(10), ''\n''); ' || CHR(13) ||
'    IF LTRIM(LPAD(NBUF, 25)) = NBUF THEN NBUF := LPAD(NBUF, 25); END IF; ' || CHR(13) ||
'    UTL_FILE.PUT_LINE_NCHAR(FD, RPAD('' U ' || NVL2(ITM.CMT, ITM.CLM || '(' || ITM.CMT || ')', ITM.CLM) || ''', 48) || OBUF || '' → '' || NBUF); ' || CHR(13) ||
'  END IF; '  AS COL
FROM
  ITM
ORDER BY
  ITM.ID
)
SELECT 'CREATE OR REPLACE TRIGGER LOG_' || DEF.TABLE_NAME || ' AFTER INSERT OR UPDATE OR DELETE ON ' || DEF.TABLE_NAME || ' FOR EACH ROW ' || CHR(13) ||
'DECLARE ' || CHR(13) ||
'  FD UTL_FILE.FILE_TYPE; ' || CHR(13) ||
'  OBUF VARCHAR2(512); ' || CHR(13) ||
'  NBUF VARCHAR2(512); ' || CHR(13) ||
'BEGIN ' || CHR(13) ||
'  FD := UTL_FILE.FOPEN_NCHAR(''' || DEF.DIR_NAME || ''', ''' || DEF.FILE_NAME || ''', ''a'', 32767); ' || CHR(13) ||
'  UTL_FILE.PUT_LINE_NCHAR(FD, TO_CHAR(SYSTIMESTAMP, ''yyyy/mm/dd hh24:mi:ss.ff3'') || '' triggered''); ' AS COL
FROM DEF
UNION ALL
SELECT '  UTL_FILE.PUT_LINE_NCHAR(FD, ''' || NVL2(TBC.TABLE_COMMENT, DEF.TABLE_NAME || '(' || TBC.TABLE_COMMENT || ')', DEF.TABLE_NAME) || ' START''' || '); ' FROM DEF, TBC
UNION ALL
SELECT COL FROM INS
UNION ALL
SELECT COL FROM DEL
UNION ALL
SELECT COL FROM UPD
UNION ALL
SELECT '  UTL_FILE.PUT_LINE_NCHAR(FD, ''' || NVL2(TBC.TABLE_COMMENT, DEF.TABLE_NAME || '(' || TBC.TABLE_COMMENT || ')', DEF.TABLE_NAME) || ' END''' || '); ' FROM DEF, TBC
UNION ALL
SELECT
'  UTL_FILE.FCLOSE(FD); ' || CHR(13) ||
'END;' AS COL
FROM DUAL