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

快速ワーク
スポンサーリンク

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

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

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

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

いつのまにかデータ変わってんじゃんか。。。

トリガーが邪魔してデータ突っ込めねぇ。。。

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

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

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

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


スポンサーリンク

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

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

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

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

トリガー情報取得内容一覧
取得内容DBA_TRIGGERS項目
またはALTERコマンド
備考
トリガーの所有者OWNER
トリガーの名前TRIGGER_NAME
トリガーが定義される表の所有者TABLE_OWNER
トリガーのオブジェクトTABLE_NAME主にテーブルやマテビューなど
トリガーが使用可能か禁止かSTATUSENABLEDか、DISABLEDか
現在の状態にするALTERコマンドCURRENT_STATUS_COMMAND元の状態に戻す用
現在の状態とは逆にするALTERコマンドCHANGE_STATUS_COMMAND状態変更用
ENABLEDだったらDISABLEDに。
DISABLEDだったらENABLEDに。
強制的に使用禁止にするALTERコマンドDISABLE_COMMAND一律DISABLED用
強制的に使用可能にするALTERコマンドENABLE_COMMAND一律ENABLED用

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のトリガーを確認するSQLについて、ご紹介させていただきました。

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

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


コメント