MySQLのINFORMATION_SCHEMAを使って特定データベースのトリガー詳細情報を取得する方法

はじめに

MySQLでは、標準SQLで定義されているINFORMATION_SCHEMAデータベースを利用することで、テーブルやビュー、ストアドプロシージャなど、データベース内の各種オブジェクトのメタ情報を取得できます。トリガー（TRIGGER）に関する詳細情報も同様に、INFORMATION_SCHEMA.TRIGGERSテーブルを参照することで確認可能です。

基本構文

特定のデータベース（スキーマ）に登録されたトリガーの詳細を取得するには、次のようにWHERE句でTRIGGER_SCHEMAを絞り込んでクエリを実行します。

SELECT * FROM information_schema.triggers
WHERE information_schema.triggers.trigger_schema LIKE '%database_name%';

より正確に検索したい場合は、LIKEではなく=を使って完全一致で指定することもできます。

SELECT * FROM information_schema.triggers
WHERE trigger_schema = 'query';

実行例

以下は、「query」という名前のデータベースに登録されているトリガー情報を取得した実行例です。出力を見やすくするために\Gオプションを使用しています。

mysql> SELECT * FROM information_schema.triggers WHERE
    -> information_schema.triggers.trigger_schema LIKE '%query%'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: query
              TRIGGER_NAME: trigger_before_delete_sample
        EVENT_MANIPULATION: DELETE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: query
        EVENT_OBJECT_TABLE: sample
              ACTION_ORDER: 1
            ACTION_CONDITION: NULL
            ACTION_STATEMENT: BEGIN
SET @count = if (@count IS NULL, 1, (@count+1));
INSERT INTO sample_rowaffected values (@count);
END
          ACTION_ORIENTATION: ROW
               ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2017-11-21 12:31:58.70
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: cp850
      COLLATION_CONNECTION: cp850_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
*************************** 2. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: query
              TRIGGER_NAME: before_inser_studentage
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: query
        EVENT_OBJECT_TABLE: student_age
              ACTION_ORDER: 1
            ACTION_CONDITION: NULL
            ACTION_STATEMENT: IF NEW.age < 0 THEN SET NEW.age = 0;
END IF
          ACTION_ORIENTATION: ROW
               ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2017-11-21 11:26:15.34
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: cp850
      COLLATION_CONNECTION: cp850_general_ci
        DATABASE_COLLATION: latin1_swedish_ci
2 rows in set (0.03 sec)

主なカラムの意味

取得結果には多くのカラムが含まれますが、特に把握しておくと便利な主なカラムは以下の通りです。

• TRIGGER_NAME: トリガーの名前
• EVENT_MANIPULATION: トリガーが発火するイベント（INSERT / UPDATE / DELETE）
• ACTION_TIMING: 発火のタイミング（BEFORE / AFTER）
• EVENT_OBJECT_TABLE: トリガーが設定されているテーブル名
• ACTION_STATEMENT: トリガーが実行する実際のステートメント（本体）
• DEFINER: トリガーを作成したユーザー

補足：SHOW TRIGGERSとの使い分け

同様の情報はSHOW TRIGGERS FROM データベース名;でも取得できます。ただし、INFORMATION_SCHEMA.TRIGGERSを直接参照する方法には、次のような利点があります。

• WHERE句による柔軟な条件指定が可能
• 必要なカラムだけをSELECTで絞り込める
• 他のテーブルと結合（JOIN）して応用的な分析ができる

このように、INFORMATION_SCHEMA.TRIGGERSを活用すれば、特定データベースに存在するすべてのトリガーの定義内容や発火条件、作成者などの詳細情報を効率よく確認できます。