04-查看触发器
可以通过执行SHOW TRIGGERS命令查看触发器的状态、语法等信息，但是因为不能查询指定的触发器，所以每次都返回所有的触发器的信息，使用起来不是很方便，具体语法如下：
mysql> show triggers \G
1. row
Trigger: customer_create_date
Event: INSERT
Table: customer
Statement: SET NEW.create_date = NOW()
Timing: BEFORE
Created: NULL
sql_mode:
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_Z ERO,TRADITIONAL,NO_AUTO_CREATE_USER
Definer: root@localhost
2. row
…
另一个查看方式是查询系统表的information_schema.triggers表，这个方式可以查询指定触发器的指定信息，操作起来明显方便很多：
mysql> desc triggers;
+---------------------------+--------------+------+----+---------+------+
| Field | Type | Null | Key | Default | Extra|
+---------------------------+--------------+------+----+---------+------+
| TRIGGER_CATALOG | varchar(512) | YES | | | |
| TRIGGER_SCHEMA | varchar(64) | NO | | | |
| TRIGGER_NAME | varchar(64) | NO | | | |
| EVENT_MANIPULATION | varchar(6) | NO | | | |
| EVENT_OBJECT_CATALOG | varchar(512) | YES | | | |
| EVENT_OBJECT_SCHEMA | varchar(64) | NO | | | |
| EVENT_OBJECT_TABLE | varchar(64) | NO | | | |
| ACTION_ORDER | bigint(4) | NO | | 0 | |
| ACTION_CONDITION | longtext | YES | | | |
| ACTION_STATEMENT| longtext| NO ||||
| ACTION_ORIENTATION| varchar(9) | NO ||||
| ACTION_TIMING| varchar(6) | NO ||||
| ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES ||||
| ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES ||||
| ACTION_REFERENCE_OLD_ROW | varchar(3) | NO ||||
| ACTION_REFERENCE_NEW_ROW | varchar(3) | NO ||||
| CREATED | datetime | YES | | | |
| SQL_MODE | longtext | NO | | | |
| DEFINER | longtext | NO | | | |
+---------------------------+--------------+------+----+---------+------+
19 rows in set (0.00 sec)
mysql> select * from triggers where trigger_name = 'ins_film_bef' \G
1. row
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: sakila
TRIGGER_NAME: ins_film_bef
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: sakila
EVENT_OBJECT_TABLE: film
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO tri_demo (note) VALUES ('before insert');
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: NULL
SQL_MODE:
DEFINER: root@localhost
1 row in set (0.01 sec)