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