mysql latid1_【转】mysql触发器的实战经验（触发器执行失败，sql会回滚吗） | 学步园...

1   引言Mysql的触发器和存储过程一样，都是嵌入到mysql的一段程序。触发器是mysql5新增的功能，目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本，很多程序比如fc-star管理端，sfrd(das)，dorado都会用到触发器程序，实现对于数据库增、删、改引起事件的关联操作。本文介绍了触发器的类型和基本使用方法，讲述了触发器使用中容易产生的误区，从mysql源码中得到触发器执行顺序的结论，本文最后是实战遭遇的触发器经典案例。没有特殊说明时，本文的实验均基于mysql5.0.45版本。2   Mysql触发器的类型2.1   Mysql触发器的基本使用创建触发器。创建触发器语法如下：CREATE TRIGGER trigger_name trigger_time trigger_eventON tbl_name FOR EACH ROW trigger_stmt其中trigger_name标识触发器名称，用户自行指定；trigger_time标识触发时机，用before和after替换；trigger_event标识触发事件，用insert，update和delete替换；tbl_name标识建立触发器的表名，即在哪张表上建立触发器；trigger_stmt是触发器程序体；触发器程序可以使用begin和end作为开始和结束，中间包含多条语句；下面给出sfrd一个触发器实例：CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER trig_useracct_updateAFTER UPDATEON SF_User.useracct FOR EACH ROWBEGINIF OLD.ulevelid = 10101 OR OLD.ulevelid = 10104 THENIF NEW.ulevelid = 10101 OR NEW.ulevelid = 10104 THENif NEW.ustatid != OLD.ustatid OR NEW.exbudget != OLD.exbudget THENINSERT into FC_Output.fcevent set type = 2, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;end if;ELSEINSERT into FC_Output.fcevent set type = 1, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;END IF;END IF;END;上述触发器实例使用了OLD关键字和NEW关键字。OLD和NEW可以引用触发器所在表的某一列，在上述实例中，OLD.ulevelid表示表 SF_User.useracct修改之前ulevelid列的值，NEW.ulevelid表示表SF_User.useracct修改之后 ulevelid列的值。另外，如果是insert型触发器，NEW.ulevelid也表示表SF_User.useracct新增行的

ulevelid列值；如果是delete型触发器OLD.ulevelid也表示表SF_User.useracct删除行的ulevelid列原值。另外，OLD列是只读的，NEW列则可以在触发器程序中再次赋值。上述实例也使用了IF，THEN ，ELSE，END IF等关键字。在触发器程序体中，在beigin和end之间，可以使用顺序，判断，循环等语句，实现一般程序需要的逻辑功能。查看触发器。查看触发器语法如下，如果知道触发器所在数据库，以及触发器名称等具体信息：SHOW TRIGGERS from SF_User like "usermaps%";       //查看SF_User库上名称和usermaps%匹配的触发器如果不了解触发器的具体的信息，或者需要查看数据库上所有触发器，如下：SHOW TRIGGERS;       //查看所有触发器用上述方式查看触发器可以看到数据库的所有触发器，不过如果一个库上的触发器太多，由于会刷屏，可能没有办法查看所有触发器程序。这时，可以采用如下方式：Mysql中有一个information_schema.TRIGGERS表，存储所有库中的所有触发器，desc information_schema. TRIGGERS，可以看到表结构：+----------------------------+--------------+------+-----+---------+-------+| Field                      | Type         | Null | Key | Default | Extra |+----------------------------+--------------+------+-----+---------+-------+| TRIGGER_CATALOG            | varchar(512) | YES |     | NULL    |       || TRIGGER_SCHEMA             | varchar(64) | NO   |     |         |       || TRIGGER_NAME               | varchar(64) | NO   |     |         |       || EVENT_MANIPULATION         | varchar(6)   | NO   |     |         |       || EVENT_OBJECT_CATALOG       | varchar(512) | YES |     | NULL    |       || EVENT_OBJECT_SCHEMA        | varchar(64) | NO   |     |         |       || EVENT_OBJECT_TABLE         | varchar(64) | NO   |     |         |       || ACTION_ORDER               | bigint(4)    | NO   |     | 0       |       || ACTION_CONDITION           | longtext     | YES |     | NULL    |       || ACTION_STATEMENT           | longtext     | NO   |     |         |       || ACTION_ORIENTATION         | varchar(9)   | NO   |     |         |       || ACTION_TIMING              | varchar(6)   | NO   |     |         |       || ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES |     | NULL    |       || ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES |     | NULL    |       || ACTION_REFERENCE_OLD_ROW   | varchar(3)   | NO   |     |         |       || ACTION_REFERENCE_NEW_ROW   | varchar(3)   | NO   |     |         |       || CREATED                    | datetime     | YES |     | NULL    |       || SQL_MODE                   | longtext     | NO   |     |         |       || DEFINER                    | longtext     | NO   |     |         |       |+----------------------------+--------------+------+-----+---------+-------+这样，用户就可以按照自己的需要，查看触发器，比如使用如下语句查看上述触发器：select * from information_schema. TRIGGERS where TRIGGER_NAME= 'trig_useracct_update'G;删除触发器。删除触发器语法如下：DROP TRIGGER [schema_name.]trigger_name2.2   Msyql触发器的trigger_time和trigger_event现在，重新注意到trigger_time和trigger_event，上文说过， trigger_time可以用before和after替换，表示触发器程序的执行在sql执行的前还是后；trigger_event可以用 insert，update，delete替换，表示触发器程序在什么类型的sql下会被触发。在一个表上最多建立6个触发器，即1)before insert型，2)before update型，3)before delete型，4)after insert型，5)after update型，6)after delete型。触发器的一个限制是不能同时在一个表上建立2个相同类型的触发器。这个限制的一个来源是触发器程序体的“begin和end之间允许运行多个语句”(摘自mysql使用手册)。另外还有一点需要注意，msyql除了对insert，update，delete基本操作进行定义外，还定义了load data和replace语句，而load data和replace语句也能引起上述6中类型的触发器的触发。Load data语句用于将一个文件装入到一个数据表中，相当与一系列insert操作。replace语句一般来说和insert语句很像，只是在表中有 primary key和unique索引时，如果插入的数据和原来primary key和unique索引一致时，会先删除原来的数据，然后增加一条新数据；也就是说，一条replace

sql有时候等价于一条insert sql，有时候等价于一条delete sql加上一条insert sql。即是：?   Insert型触发器：可能通过insert语句，load data语句，replace语句触发；?   Update型触发器：可能通过update语句触发；?   Delete型触发器：可能通过delete语句，replace语句触发；3   Mysql触发器的执行顺序先抛出触发器相关的几个问题3.1   如果before类型的触发器程序执行失败，sql会执行成功吗？实验如下：1)在FC_Word.planinfo中建立before触发器：DELIMITER |create trigger trigger_before_planinfo_updatebefore updateON FC_Word.planinfo FOR EACH ROWBEGINinsert into FC_Output.abc (planid) values (New.planid);END|2)查看：mysql> select showprob from planinfo where planid=1;+----------+| showprob |+----------+|        2 |+----------+3)执行sql：update planinfo set showprob=200 where planid=1;      触发触发器程序；4)由于不存在FC_Output.abc，before触发器执行失败，提示：ERROR 1146 (42S02): Table 'FC_Output.abc' doesn't exist5)再次查看：mysql> select showprob from planinfo where planid=1;+----------+| showprob |+----------+|        2 |+----------+即修改sql未执行成功。即如果before触发器执行失败，sql也会执行失败。3.2   如果sql执行失败，会执行after类型的触发器程序吗？实验如下：1)在FC_Word.planinfo中建立after触发器：DELIMITER |create trigger trigger_after_planinfo_updateafter updateON FC_Word.planinfo FOR EACH ROWBEGININSERT INTO FC_Output.fcevent set level = 2, type = 2, tabid = 5, userid = NEW.userid, planid = NEW.planid, planstat2 = NEW.planstat2, showprob =

NEW.showprob, showrate = NEW.showrate, showfactor = NEW.showfactor, planmode = NEW.planmode;END|2)查看触发表：mysql> select * from FC_Output.fcevent where planid=1;Empty set (0.00 sec)没有planid=1的记录3)执行sql：mysql> update planinfo set showprob1=200 where planid=1;4)由于不存在showprob1列，提示错误：ERROR 1054 (42S22): Unknown column 'showprob1' in 'field list'5)再次查看触发表：mysql> select * from FC_Output.fcevent where planid=1;Empty set (0.00 sec)触发表中没有planid=1的记录，sql在执行失败时，after型触发器不会执行。3.3   如果after类型的触发器程序执行失败，sql会回滚吗？实验如下：1)在FC_Word.planinfo中建立after触发器：DELIMITER |create trigger trigger_after_planinfo_updateafter updateON FC_Word.planinfo FOR EACH ROWBEGINinsert into FC_Output.abc (planid) values (New.planid);END|2)查看：mysql> select showprob from planinfo where planid=1;+----------+| showprob |+----------+|        2 |+----------+3)执行sql：update planinfo set showprob=200 where planid=1;触发触发器程序；4)由于不存在FC_Output.abc，after触发器执行失败，提示：ERROR 1146 (42S02): Table 'FC_Output.abc' doesn't exist5)再次查看：mysql> select showprob from planinfo where planid=1;+----------+| showprob |+----------+|        2 |+----------+即修改sql未执行成功。即如果after触发器执行失败，sql会回滚。这里需要说明一下，上述实验所使用的mysql引擎是innodb，innodb引擎也是目前线上凤巢系统、北斗系统以及哥伦布系统所使用的引擎，在 innodb上所建立的表是事务性表，也就是事务安全的。“对于事务性表，如果触发程序失败(以及由此导致的整个语句的失败)，该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚”(摘自mysql使用手册)。因而，即使语句失败，失败之前所作的任何更改依然有效，也就是说，对于

innodb引擎上的数据表，如果触发器中的sql或引发触发器的sql执行失效，则事务回滚，所有操作会失效。3.4   mysql触发器程序执行的顺序当一个表既有before类型的触发器，又有after类型的触发器时；当一条sql语句涉及多个表的update时，sql、触发器的执行顺序经过mysql源码包装过，有时比较复杂。可以先看一段mysql的源代码，当SQL中update多表的时候，Mysql的执行过程如下(省去了无关代码)：/* 遍历要更新的所有表 */for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local){org_updated = updated/* 如果有 BEFORE 触发器，则执行；如果执行失败，跳到err2位置 */if (table->triggers &&table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,TRG_ACTION_BEFORE, TRUE))goto err2;/*执行更新，如果更新失败，跳到err位置*/if(local_error=table->file->update_row(table->record[1], table->record[0])))goto err;updated++; // 更新计数器/* 如果有 AFTER 触发器，则执行；如果执行失败，跳到err2位置*/if (table->triggers &&table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE))goto err2;err:{/*标志错误信息，写日志等*/}err2:{/*恢复执行过的操作*/check_opt_it.rewind();/*如果执行了更新，且表是有事务的，做标志*/if (updated != org_updated){if (table->file->has_transactions())transactional_tables= 1;}}}从上面代码可以找到本章开始时抛出问题的答案。