mysql实例中的每一个trigger 对应到information_schema.triggers 中有一行

1、information_schema.triggers 表的常用列:

  1、trigger_catalog          :永远是def

  2、trigger_schema          :trigger 所在的数据库名

  3、event_manipulation        :触发trigger 的事件类型可以是 insert | update | delete

  4、event_object_schema        :trigger 所基于的表所在的数据库名

  5、event_object_table        :trigger 所基于的表名

  6、action_statement         :trigger 内部所包涵的SQL语句

2、例子:

drop table if exists t;
drop table if exists t_log; create table t(
id int auto_increment,
x int,
constraint pk__t__id primary key(id))
engine=innodb
default char set utf8; create table t_log(
id int auto_increment,
log_time datetime default now(),
constraint pk__t_log__id primary key(id))
engine=innodb
default char set utf8; delimiter go
create trigger tg__insert__t
before insert
on t
for each row
begin insert into t_log(log_time) values(current_timestamp());
end
go delimiter ;

查看trigger 信息:

mysql> select * from triggers \G
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: tempdb
TRIGGER_NAME: tg__insert__t
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: tempdb
EVENT_OBJECT_TABLE: t
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin insert into t_log(log_time) values(current_timestamp());
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: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
04-30 22:52