语法
存储过程语法如下:
CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名称>
FOR EACH ROW
删除存储过程:
DROP TRIGGER<触发器名称>
实例
create trigger trigger_test_innodb
after insert on test_innodb
for each row
begin
insert into test_myisam values(10001,'zhangsan');
end;
效果
SELECT * FROM information_schema.`TRIGGERS`;
+-----------------+----------------+---------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------+----------------+----------------------+----------------------+--------------------+
| TRIGGER_CATALOG | TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_CATALOG | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_ORDER | ACTION_CONDITION | ACTION_STATEMENT | ACTION_ORIENTATION | ACTION_TIMING | ACTION_REFERENCE_OLD_TABLE | ACTION_REFERENCE_NEW_TABLE | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW | CREATED | SQL_MODE | DEFINER | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+-----------------+----------------+---------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------+----------------+----------------------+----------------------+--------------------+
| def | test | trigger_test_innodb | INSERT | def | test | test_innodb | 0 | NULL | begin
insert into test_myisam values(10001,'zhangsan');
end | ROW | AFTER | NULL | NULL | OLD | NEW | NULL | | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
+-----------------+----------------+---------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set
mysql> insert into test_innodb values(10002,'zhaoliu');
Query OK, 1 row affected
由于作者数据较多就不执行查询语句了,请自行查看
名词解释
大写 | 小写 | 词义 | 用法 |
TRIGGER | trigger | 触发器 | TRIGGER <触发器名称> |
BEFORE | before | 事件发生前触发 | before update |
AFTER | after | 事件发生后触发 | before update |
ON | no | 触发那张表 | on <表名> |
FOR EACH ROW | for each row | 触发器的执行间隔 (一行一执行||整个表执行一次) | for each row |