【Oracle】トリガー確認と有効化・無効化コマンドを一括で取得するSQL

Oracleのトリガーって意外と便利ですよね。

超簡単に云えば、Insert、Update、Deleteが発動されたら自動的に自由自在にデータを処理できる機能です。

ですが、正直、扱いがめんどくさい機能でもあります。。。

たくさんのテーブルやマテビューにトリガーを仕込んでいる場合など、、、

みたいな事態になりやすいです。

トリガーを扱っている方はそんな風に感じたことはあるのではないでしょうか。

そこで、トリガーの現在の状態確認や全トリガーを管理しやすくするためのALTERコマンドを一発で取得するSQLを作成してみましたので共有したいと思います。

Oralceのトリガーを扱う方々に少しでも参考になれば幸いです。

Oracleのトリガー情報をSQL一発で簡単する内容

Oracleディクショナリからトリガーの状態を抽出して、ALTERコマンドを仕込んだＳＱＬで取得できる内容です。

トリガータイプなど下記項目以外に必要な情報があれば、Oracle公式ページを確認の上、お好みで追加してください。

参照：Oracle® Databaseリファレンス | ALL_TRIGGERS ( DBA_TRIGGERS | USER_TRIGGERS )

Excelなどに貼り付けてフィルタをかけて必要なトリガーに絞り込めるようにすると一括でAlterをかけたい場合にわかりやすいですね。

Oracleのトリガー情報を取得するSQL

全コピーして黄色のマーカー部分のスキーマの部分をご自分の環境に該当する値に変更して実行してみてください。

/* Oracleディクショナリより現在のトリガー情報と変更コマンドを取得するSQL */
SELECT
  ,OWNER
  ,TRIGGER_NAME
  ,TABLE_OWNER
  ,TABLE_NAME
  ,STATUS
  ,CASE
     WHEN STATUS = 'ENABLED'
       THEN 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' '||'ENABLE'||';'
     WHEN STATUS = 'DISABLED'
       THEN 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' '||'DISABLE'||';'
   END AS CURRENT_STATUS_COMMAND
  ,CASE
     WHEN STATUS = 'ENABLED'
       THEN 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' '||'DISABLE'||';'
     WHEN STATUS = 'DISABLED'
       THEN 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' '||'ENABLE'||';'
   END AS CHANGE_STATUS_COMMAND
  ,'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' '||'DISABLE'||';' AS DISABLE_COMMAND
  ,'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' '||'ENABLE'||';' AS ENABLE_COMMAND
FROM
  DBA_TRIGGERS
WHERE
  OWNER IN ('TEST','TEST01')
ORDER BY
   OWNER
  ,TRIGGER_NAME
  ,TABLE_OWNER
  ,TABLE_NAME
/

最後に

Oracleのトリガーを確認するＳＱＬについて、ご紹介させていただきました。

トリガーはちゃんと管理できれば、便利で有効な機能だと思います。

データベースを扱っている人にとっては、知っておいて損はないと思いますので、ぜひ参考にしてみてください。