Mysql 触发器（trigger）-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

语法

存储过程语法如下:

    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