MySql查询触发器

SHOW TRIGGERS LIKE ?;

?->表名，而不是触发器名称

官方文档：https://dev.mysql.com/doc/refman/8.0/en/show-triggers.html

SHOW TRIGGERS [{FROM|IN} _db_name_][LIKE '_pattern_'| WHERE _expr_]

SHOW TRIGGERS 列出当前为数据库中的表定义的触发器（除非给出 FROM 子句，否则为默认数据库）。此语句仅返回您拥有 TRIGGER 权限的数据库和表的结果。 LIKE 子句（如果存在）指示要匹配的表名称（不是触发器名称），并使语句显示这些表的触发器。可以使用 WHERE 子句来使用更通用的条件来选择行，如第 26.8 节“SHOW 语句的扩展”中所述。

mysql> SHOW TRIGGERS LIKE 'acc%'\G
*************************** 1. row ***************************
             Trigger: ins_sum
               Event: INSERT
               Table: account
           Statement: SET @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2018-08-08 10:10:12.61
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                      NO_ZERO_IN_DATE,NO_ZERO_DATE,
                      ERROR_FOR_DIVISION_BY_ZERO,
                      NO_ENGINE_SUBSTITUTION
             Definer: me@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
  • Trigger

The name of the trigger.触发器的名称。

  • Event

The trigger event. This is the type of operation on the associated table for which the trigger activates. The value is INSERT (a row was inserted), DELETE (a row was deleted), or UPDATE (a row was modified).触发事件。这是触发器激活的关联表上的操作类型。值为 INSERT（插入行）、DELETE（删除行）或 UPDATE（修改行）。

  • Table

The table for which the trigger is defined.为其定义触发器的表。

  • Statement

The trigger body; that is, the statement executed when the trigger activates.扳机体；即触发器激活时执行的语句。

  • Timing

Whether the trigger activates before or after the triggering event. The value is BEFORE or AFTER.触发器是在触发事件之前还是之后激活。值为BEFORE或AFTER。

  • Created

The date and time when the trigger was created. This is a TIMESTAMP(2) value (with a fractional part in hundredths of seconds) for triggers.创建触发器的日期和时间。这是触发器的 TIMESTAMP(2) 值（带有百分之一秒的小数部分）。

  • sql_mode

The SQL mode in effect when the trigger was created, and under which the trigger executes. For the permitted values, see Section 5.1.11, “Server SQL Modes”.创建触发器时生效的 SQL 模式，以及触发器在该模式下执行。有关允许的值，请参阅第 5.1.11 节“服务器 SQL 模式”。

  • Definer

The account of the user who created the trigger, in 'user_name'@'host_name' format.创建触发器的用户帐户，格式为 'user_name'@'host_name'。

  • character_set_client

The session value of the character_set_client system variable when the trigger was created.创建触发器时 character_set_client 系统变量的会话值。

  • collation_connection

The session value of the collation_connection system variable when the trigger was created.创建触发器时 collation_connection 系统变量的会话值。

  • Database Collation

The collation of the database with which the trigger is associated.与触发器关联的数据库的排序规则。

Trigger information is also available from the INFORMATION_SCHEMA TRIGGERS table.

创建示例：

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;

查询的另一种：

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
       WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='ins_sum'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: ins_sum
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: account
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: SET @sum = @sum + NEW.amount
        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: 2018-08-08 10:10:12.61
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                            NO_ZERO_IN_DATE,NO_ZERO_DATE,
                            ERROR_FOR_DIVISION_BY_ZERO,
                            NO_ENGINE_SUBSTITUTION
                   DEFINER: me@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci

The TRIGGERS table provides information about triggers. To see information about a table's triggers, you must have the TRIGGER privilege for the table.

TRIGGERS 表提供有关触发器的信息。要查看有关表触发器的信息，您必须拥有该表的 TRIGGER 权限。

The TRIGGERS table has these columns:TRIGGERS 表包含以下列：

  • TRIGGER_CATALOG

The name of the catalog to which the trigger belongs. This value is always def.触发器所属目录的名称。该值始终为def。

  • TRIGGER_SCHEMA

The name of the schema (database) to which the trigger belongs.触发器所属的架构（数据库）的名称。

  • TRIGGER_NAME

The name of the trigger.触发器的名称。

  • EVENT_MANIPULATION

The trigger event. This is the type of operation on the associated table for which the trigger activates. The value is INSERT (a row was inserted), DELETE (a row was deleted), or UPDATE (a row was modified).触发事件。这是触发器激活的关联表上的操作类型。值为 INSERT（插入行）、DELETE（删除行）或 UPDATE（修改行）。

  • EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, and EVENT_OBJECT_TABLE

As noted in Section 25.3, “Using Triggers”, every trigger is associated with exactly one table. These columns indicate the catalog and schema (database) in which this table occurs, and the table name, respectively. The EVENT_OBJECT_CATALOG value is always def.

如第 25.3 节“使用触发器”中所述，每个触发器都与一个表相关联。这些列分别指示该表所在的目录和架构（数据库）以及表名称。 EVENT_OBJECT_CATALOG 值始终为 def。

  • ACTION_ORDER

The ordinal position of the trigger's action within the list of triggers on the same table with the same EVENT_MANIPULATION and ACTION_TIMING values.触发器操作在同一表上具有相同 EVENT_MANIPULATION 和 ACTION_TIMING 值的触发器列表中的顺序位置。

  • ACTION_CONDITION

This value is always NULL.该值始终为NULL。

  • ACTION_STATEMENT

The trigger body; that is, the statement executed when the trigger activates. This text uses UTF-8 encoding.扳机体；即触发器激活时执行的语句。此文本使用 UTF-8 编码。

  • ACTION_ORIENTATION

This value is always ROW.该值始终为ROW。

  • ACTION_TIMING

Whether the trigger activates before or after the triggering event. The value is BEFORE or AFTER.触发器是在触发事件之前还是之后激活。值为BEFORE或AFTER。

  • ACTION_REFERENCE_OLD_TABLE

This value is always NULL.该值始终为NULL。

  • ACTION_REFERENCE_NEW_TABLE

This value is always NULL.该值始终为NULL。

  • ACTION_REFERENCE_OLD_ROW and ACTION_REFERENCE_NEW_ROW

The old and new column identifiers, respectively. The ACTION_REFERENCE_OLD_ROW value is always OLD and the ACTION_REFERENCE_NEW_ROW value is always NEW.

分别是旧的和新的列标识符。 ACTION_REFERENCE_OLD_ROW 值始终为 OLD，ACTION_REFERENCE_NEW_ROW 值始终为 NEW。

  • CREATED

The date and time when the trigger was created. This is a TIMESTAMP(2) value (with a fractional part in hundredths of seconds) for triggers.创建触发器的日期和时间。这是触发器的 TIMESTAMP(2) 值（带有百分之一秒的小数部分）。

  • SQL_MODE

The SQL mode in effect when the trigger was created, and under which the trigger executes. For the permitted values, see Section 5.1.11, “Server SQL Modes”.创建触发器时生效的 SQL 模式，以及触发器在该模式下执行。有关允许的值，请参阅第 5.1.11 节“服务器 SQL 模式”。

  • DEFINER

The account named in the DEFINER clause (often the user who created the trigger), in 'user_name'@'host_name' format.DEFINER 子句中指定的帐户（通常是创建触发器的用户），采用 'user_name'@'host_name' 格式。

  • CHARACTER_SET_CLIENT

The session value of the character_set_client system variable when the trigger was created.创建触发器时 character_set_client 系统变量的会话值。

  • COLLATION_CONNECTION

The session value of the collation_connection system variable when the trigger was created.创建触发器时 collation_connection 系统变量的会话值。

  • DATABASE_COLLATION

The collation of the database with which the trigger is associated.与触发器关联的数据库的排序规则。