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

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

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

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

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

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

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

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

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

---

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

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

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

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

トリガー情報取得内容一覧

取得内容	DBA_TRIGGERS項目 またはALTERコマンド	備考
トリガーの所有者	OWNER
トリガーの名前	TRIGGER_NAME
トリガーが定義される表の所有者	TABLE_OWNER
トリガーのオブジェクト	TABLE_NAME	主にテーブルやマテビューなど
トリガーが使用可能か禁止か	STATUS	ENABLEDか、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のトリガーを確認するＳＱＬについて、ご紹介させていただきました。

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

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

---