【Oracle】トリガーでログを記録する為のテーブルとトリガーのプロシージャーの作成
ダミーのようなテーブルをあらかじめ作成して
テーブルにデータが追加、更新、削除が行われたとき、
そこに変更ログを記録するようなことができると、
更新以前の状況の更新過程が分かるようになるので便利かもしれない。
そんなダミーテーブルの作成と、更新を記録するトリガーを作成する為の
プロシージャーを載せた。
ただ、このプロシージャーは、対象にするテーブルに主キーを設定しておかないと、作られないようにしてある。
適当にログテーブルを作って、人的にテーブルスペースを圧迫してほしくないので、
主キーのあるテーブルのみ有効ということにしておいた。
まずは、トリガーの自動作成プロシージャー。
CREATE OR REPLACE PROCEDURE CREATE_LOG_TRIGGER ( STR_TABLE IN VARCHAR2, OUT_STS OUT VARCHAR2, OUT_MSG OUT VARCHAR2 ) AS CURSOR REC_CUR IS SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = STR_TABLE; TYPE REC_TYPE IS TABLE OF REC_CUR%ROWTYPE; CURSOR REC_CONS IS SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = STR_TABLE AND CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = STR_TABLE AND CONSTRAINT_TYPE = 'P' ); TYPE REC_TYPE2 IS TABLE OF REC_CONS%ROWTYPE; CURSOR REC_COLUMNS IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = STR_TABLE; TYPE REC_TYPE3 IS TABLE OF REC_COLUMNS%ROWTYPE; B_WRET_REC REC_TYPE; A_WRET_REC REC_TYPE2; C_WRET_REC REC_TYPE3; W_COUNT NUMBER(10); TRG_NAME VARCHAR2(100); LOG_TBL_NAME VARCHAR2(100); STRSQL CLOB; W_CRLF VARCHAR2(4) := CHR(13) || CHR(10); BEGIN OUT_STS := ' '; OUT_MSG := ' '; LOG_TBL_NAME := STR_TABLE || '_LOG'; TRG_NAME := STR_TABLE || '_LOG_TRG'; OPEN REC_COLUMNS; FETCH REC_COLUMNS BULK COLLECT INTO C_WRET_REC; CLOSE REC_COLUMNS; OPEN REC_CUR; FETCH REC_CUR BULK COLLECT INTO B_WRET_REC; CLOSE REC_CUR; OPEN REC_CONS; FETCH REC_CONS BULK COLLECT INTO A_WRET_REC LIMIT 100; CLOSE REC_CONS; /*主キーがない場合は作らない*/ IF A_WRET_REC.COUNT = 0 THEN OUT_STS := 'NONE'; OUT_MSG := '主キーなし'; RETURN; END IF; STRSQL := 'CREATE OR REPLACE TRIGGER ' || TRG_NAME || ' ' || W_CRLF || 'AFTER INSERT OR DELETE OR UPDATE ON ' || STR_TABLE || ' ' || W_CRLF || 'FOR EACH ROW' || W_CRLF || W_CRLF || 'DECLARE' || W_CRLF || W_CRLF || 'W_REC ' || LOG_TBL_NAME || '%ROWTYPE; ' || W_CRLF || 'W_REC_OLD ' || LOG_TBL_NAME || '%ROWTYPE; ' || W_CRLF || 'W_INS_FLG VARCHAR2(50); ' || W_CRLF || W_CRLF || '--' || STR_TABLE || '更新ログ出力トリガー:' || TRG_NAME || W_CRLF || '--※このトリガーは自動生成されています。' || W_CRLF || '-- トリガー名を絶対に変更しないこと！！' || W_CRLF || W_CRLF /*行トリガー作成*/ || '--行トリガー' || W_CRLF; STRSQL := STRSQL || 'BEGIN' || W_CRLF || W_CRLF || 'IF (INSERTING) THEN ' || W_CRLF || W_CRLF || 'W_INS_FLG := ''INSERT'';' || W_CRLF || W_CRLF || 'END IF; ' || W_CRLF || W_CRLF || 'IF (UPDATING) THEN ' || W_CRLF || W_CRLF || 'W_INS_FLG := ''UPDATE'';' || W_CRLF || W_CRLF || 'END IF; ' || W_CRLF || W_CRLF || 'IF (DELETING) THEN ' || W_CRLF || W_CRLF || 'W_INS_FLG := ''DELETE'';' || W_CRLF || W_CRLF || 'END IF; ' || W_CRLF || W_CRLF || W_CRLF; FOR I IN 1..C_WRET_REC.COUNT LOOP STRSQL := STRSQL || 'W_REC.' || C_WRET_REC(I).COLUMN_NAME || ' := ' || ':NEW.' || C_WRET_REC(I).COLUMN_NAME || ';' || W_CRLF; END LOOP; STRSQL := STRSQL || W_CRLF || W_CRLF; FOR I IN 1..C_WRET_REC.COUNT LOOP STRSQL := STRSQL || 'W_REC_OLD.' || C_WRET_REC(I).COLUMN_NAME || ' := ' || ':OLD.' || C_WRET_REC(I).COLUMN_NAME || ';' || W_CRLF; END LOOP; STRSQL := STRSQL || W_CRLF || W_CRLF || 'IF W_INS_FLG = ''DELETE'' THEN ' || W_CRLF || W_CRLF || 'W_REC_OLD.LOG_ID :=TO_CHAR(SYSTIMESTAMP,''yyyymmddhh24missff3'') || ''-'' || DBMS_RANDOM.STRING(''U'',15);' || W_CRLF || 'W_REC_OLD.INS_FLG :=W_INS_FLG;' || W_CRLF || 'W_REC_OLD.INS_DATE :=TO_CHAR(SYSDATE,''YYYYMMDD'');' || W_CRLF || 'W_REC_OLD.INS_TIME :=TO_CHAR(SYSTIMESTAMP,''YYYY/MM/DD HH24;MI:SS.ff3'');' || W_CRLF || 'INSERT INTO ' || LOG_TBL_NAME || ' VALUES W_REC_OLD;' || W_CRLF || W_CRLF || 'ELSE ' || W_CRLF || W_CRLF || 'W_REC.LOG_ID :=TO_CHAR(SYSTIMESTAMP,''yyyymmddhh24missff3'') || ''-'' || DBMS_RANDOM.STRING(''U'',15);' || W_CRLF || 'W_REC.INS_FLG :=W_INS_FLG;' || W_CRLF || 'W_REC.INS_DATE :=TO_CHAR(SYSDATE,''YYYYMMDD'');' || W_CRLF || 'W_REC.INS_TIME :=TO_CHAR(SYSTIMESTAMP,''YYYY/MM/DD HH24;MI:SS.ff3'');' || W_CRLF || 'INSERT INTO ' || LOG_TBL_NAME || ' VALUES W_REC;' || W_CRLF || W_CRLF; STRSQL := STRSQL || 'END IF; ' || W_CRLF || W_CRLF; /*トリガー末尾*/ STRSQL := STRSQL || W_CRLF || 'END ' || TRG_NAME || ';'; EXECUTE IMMEDIATE STRSQL; OUT_STS := 'OK'; END CREATE_LOG_TRIGGER;
これ単体で実行しても意味がないので、
上記のプロシージャーがコンパイルされた上で、以下を実行する。
以下は、ダミーテーブルと上記のCREATE_LOG_TRIGGERを
実行するプロシージャー。
もちろんユーザーにトリガーとテーブルを作る権限がないと動かない。
CREATE OR REPLACE PROCEDURE LOG_TABLE_CREATE ( STR_TABLE IN VARCHAR2, OP_FLG IN NUMBER /*0:作成 1:削除*/ , OUT_STS OUT VARCHAR2, OUT_MSG OUT VARCHAR2 ) AS CURSOR REC_CUR IS SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME NOT LIKE '%_LOG' AND TABLE_NAME LIKE CASE NVL(STR_TABLE,' ') WHEN ' ' THEN TABLE_NAME ELSE STR_TABLE END; TYPE REC_TYPE IS TABLE OF REC_CUR%ROWTYPE; /*プライマリーキーを探す*/ CURSOR REC_CONS ( TB_NAME VARCHAR2 ) IS SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = TB_NAME AND CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = TB_NAME AND CONSTRAINT_TYPE = 'P' ); TYPE REC_TYPE3 IS TABLE OF REC_CONS%ROWTYPE; B_WRET_REC REC_TYPE; C_WRET_REC REC_TYPE3; W_COUNT NUMBER(10); LOG_TABLE_NAME VARCHAR2(100); LOG_PKEY_NAME VARCHAR2(100); TRG_NAME VARCHAR2(100); BEGIN OUT_STS := ' '; OUT_MSG := ' '; OPEN REC_CUR; LOOP FETCH REC_CUR BULK COLLECT INTO B_WRET_REC LIMIT 100; EXIT WHEN B_WRET_REC.COUNT = 0; FOR I IN 1..B_WRET_REC.COUNT LOOP LOG_TABLE_NAME := B_WRET_REC(I).TABLE_NAME || '_LOG'; TRG_NAME := B_WRET_REC(I).TABLE_NAME || '_LOG_TRG'; LOG_PKEY_NAME := ' '; W_COUNT := 0; /*テーブルが見つからなければ削除処理はしない*/ SELECT COUNT(*) INTO W_COUNT FROM USER_TABLES WHERE TABLE_NAME = LOG_TABLE_NAME; IF W_COUNT > 0 THEN /*トリガーの存在を確認*/ W_COUNT := 0; SELECT COUNT(TRIGGER_NAME) INTO W_COUNT FROM USER_TRIGGERS WHERE TRIGGER_NAME = TRG_NAME; /*存在しているのであればトリガーを削除する*/ IF W_COUNT > 0 THEN /*トリガー削除*/ EXECUTE IMMEDIATE 'DROP TRIGGER ' || TRG_NAME; END IF; /*プライマリーキーがあるか確認*/ BEGIN SELECT CONSTRAINT_NAME INTO LOG_PKEY_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = LOG_TABLE_NAME AND CONSTRAINT_TYPE = 'P'; EXCEPTION WHEN OTHERS THEN LOG_PKEY_NAME := ' '; END; IF LOG_PKEY_NAME <> ' ' THEN /*プライマリーキー削除*/ EXECUTE IMMEDIATE 'ALTER TABLE ' || LOG_TABLE_NAME || ' DROP CONSTRAINT ' || LOG_PKEY_NAME; END IF; /*テーブル削除*/ EXECUTE IMMEDIATE 'DROP TABLE ' || LOG_TABLE_NAME; END IF; IF OP_FLG = 0 THEN /*元テーブルの主キーがあるかチェック*/ C_WRET_REC := REC_TYPE3 (); OPEN REC_CONS(B_WRET_REC(I).TABLE_NAME); FETCH REC_CONS BULK COLLECT INTO C_WRET_REC; CLOSE REC_CONS; /*主キーがなければログテーブルの作成はしない*/ IF C_WRET_REC.COUNT > 0 THEN /*テーブル作成*/ EXECUTE IMMEDIATE 'CREATE TABLE ' || LOG_TABLE_NAME || ' AS SELECT * FROM ' || B_WRET_REC(I).TABLE_NAME || ' WHERE ROWNUM = 0'; /*カラム追加*/ EXECUTE IMMEDIATE 'ALTER TABLE ' || LOG_TABLE_NAME || ' ADD ( ' || 'LOG_ID VARCHAR2(50 byte) NOT NULL,' || 'INS_FLG VARCHAR2(50 byte) NOT NULL,' || 'INS_DATE VARCHAR2(50 byte) NOT NULL,' || 'INS_TIME VARCHAR2(50 byte) NOT NULL' || ') '; /*主キー追加 */ EXECUTE IMMEDIATE 'ALTER TABLE ' || LOG_TABLE_NAME || ' ADD ' || 'CONSTRAINT ' || LOG_TABLE_NAME || '_PKEY ' || 'PRIMARY KEY (LOG_ID) '; /*トリガー作成*/ CREATE_LOG_TRIGGER(B_WRET_REC(I).TABLE_NAME,OUT_STS,OUT_MSG); END IF; END IF; END LOOP; END LOOP; CLOSE REC_CUR; END LOG_TABLE_CREATE;
これをSQLDEVELOPERのデバッグサンプルのSQLを引用して実行してみる。
と、その前に作成対象のテーブルにプライマリキーを貼る。
ちなみに使ったサンプルのテーブルのスクリプトは以下の記事にあるので
使いたければどうぞ。
fubukin.hatenablog.com
さて、では以下を実行してみる。
DECLARE STR_TABLE VARCHAR2(200); OP_FLG NUMBER; OUT_STS VARCHAR2(200); OUT_MSG VARCHAR2(200); BEGIN STR_TABLE := 'TABLE1'; OP_FLG := 0; LOG_TABLE_CREATE( STR_TABLE => STR_TABLE, OP_FLG => OP_FLG, OUT_STS => OUT_STS, OUT_MSG => OUT_MSG ); /* Legacy output: DBMS_OUTPUT.PUT_LINE('OUT_STS = ' || OUT_STS); */ :OUT_STS := OUT_STS; /* Legacy output: DBMS_OUTPUT.PUT_LINE('OUT_MSG = ' || OUT_MSG); */ :OUT_MSG := OUT_MSG; --rollback; END;
左が更新したらログを出力するテーブル、右がログを出力するテーブル
そして、自動作成されたトリガーは以下になる。
CREATE OR REPLACE TRIGGER TABLE1_LOG_TRG AFTER INSERT OR DELETE OR UPDATE ON TABLE1 FOR EACH ROW DECLARE W_REC TABLE1_LOG%ROWTYPE; W_REC_OLD TABLE1_LOG%ROWTYPE; W_INS_FLG VARCHAR2(50); --TABLE1更新ログ出力トリガー:TABLE1_LOG_TRG --※このトリガーは自動生成されています。 -- トリガー名を絶対に変更しないこと！！ --行トリガー BEGIN IF ( INSERTING ) THEN W_INS_FLG := 'INSERT'; END IF; IF ( UPDATING ) THEN W_INS_FLG := 'UPDATE'; END IF; IF ( DELETING ) THEN W_INS_FLG := 'DELETE'; END IF; W_REC.A1 :=:NEW.A1; W_REC.A2 :=:NEW.A2; W_REC.A3 :=:NEW.A3; W_REC.A4 :=:NEW.A4; W_REC.A5 :=:NEW.A5; W_REC.A6 :=:NEW.A6; W_REC.A7 :=:NEW.A7; W_REC.A8 :=:NEW.A8; W_REC.A9 :=:NEW.A9; W_REC.A10 :=:NEW.A10; W_REC.A11 :=:NEW.A11; W_REC.A12 :=:NEW.A12; W_REC_OLD.A1 :=:OLD.A1; W_REC_OLD.A2 :=:OLD.A2; W_REC_OLD.A3 :=:OLD.A3; W_REC_OLD.A4 :=:OLD.A4; W_REC_OLD.A5 :=:OLD.A5; W_REC_OLD.A6 :=:OLD.A6; W_REC_OLD.A7 :=:OLD.A7; W_REC_OLD.A8 :=:OLD.A8; W_REC_OLD.A9 :=:OLD.A9; W_REC_OLD.A10 :=:OLD.A10; W_REC_OLD.A11 :=:OLD.A11; W_REC_OLD.A12 :=:OLD.A12; IF W_INS_FLG = 'DELETE' THEN W_REC_OLD.LOG_ID := TO_CHAR(SYSTIMESTAMP,'yyyymmddhh24missff3') || '-' || DBMS_RANDOM.STRING('U',15); W_REC_OLD.INS_FLG := W_INS_FLG; W_REC_OLD.INS_DATE := TO_CHAR(SYSDATE,'YYYYMMDD'); W_REC_OLD.INS_TIME := TO_CHAR(SYSTIMESTAMP,'YYYY/MM/DD HH24;MI:SS.ff3'); INSERT INTO TABLE1_LOG VALUES W_REC_OLD; ELSE W_REC.LOG_ID := TO_CHAR(SYSTIMESTAMP,'yyyymmddhh24missff3') || '-' || DBMS_RANDOM.STRING('U',15); W_REC.INS_FLG := W_INS_FLG; W_REC.INS_DATE := TO_CHAR(SYSDATE,'YYYYMMDD'); W_REC.INS_TIME := TO_CHAR(SYSTIMESTAMP,'YYYY/MM/DD HH24;MI:SS.ff3'); INSERT INTO TABLE1_LOG VALUES W_REC; END IF; END TABLE1_LOG_TRG;
自動生成は作ってみると後々、手動で作る手間も時間も省けるから楽だね。
