mysql触发器遍历_mysql触发器的实战经验

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

OLD列是只读的，NEW列则可以在触发器程序中再次赋值。

查看触发器

查看触发器语法如下，如果知道触发器所在数据库，以及触发器名称等具体信息：

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_name

另外还有一点需要注意，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语句触发；

mysql触发器程序执行的顺序

这里需要说明一下，在 innodb上所建立的表是事务性表，也就是事务安全的。“对于事务性表，如果触发程序失败(以及由此导致的整个语句的失败)，该语句所执行的所有更改将 回滚。对于非事务性表，不能执行这类回滚”(摘自mysql使用手册)。因而，即使语句失败，失败之前所作的任何更改依然有效，也就是说，对于 innodb引擎上的数据表，如果触发器中的sql或引发触发器的sql执行失效，则事务回滚，所有操作会失效。

当一个表既有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;

}

}

}

从上面代码可以找到本章开始时抛出问题的答案。

1)   如果before型触发器执行失败，直接goto跳到err2位置，不会执行后续sql语句；

2)   如果sql执行失败，直接goto跳到err位置，不会执行或许的after型触发器；

3)   如过after触发器执行失败，goto到err2位置，恢复执行过的操作，且在事务型的表上做标记。

另外，在使用复杂的sql时，由于有些复杂的sql是mysql自己定义的，所以存在不确定性，使用简单的sql比较可控。

4   Mysql触发器在数据库同步中的表现

4.1   触发器运行失败时，数据库同步会失败吗？

有同步关系如下dbA?dbB。初始时同步正常。

1)在dbB上建立触发器：

DELIMITER |

create trigger trigger_after_planinfo_update

after update

ON FC_Word.planinfo FOR EACH ROW

BEGIN

insert into FC_Output.abc (planid) values (New.planid);

END

|

2)在dbA上执行sql，执行成功；

mysql> update planinfo set showprob=200 where planid= 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

3)由于dbB上没有FC_Output.abc表，触发器会执行失败，这时，检查一下同步状态：

Slave_IO_Running: Yes

Slave_SQL_Running: NO

Last_Errno: 1146

Last_Error:

Error 'Table 'FC_Output.abc' doesn't exist' on query. Default database:

'FC_Word'. Query: 'update planinfo set showprob=200 where planid= 1'

可以看到IO线程运行正常，sql线程运行失败，并提示触发器运行失败的错误信息。

回忆一下3.1和3.3所述部分，无论是before部分的触发器还是after类型的触发器，对于innodb引擎，当触发器执行失败时，相应sql也会执行失败，所以数据库同步也会失败。

4.2   创建、删除触发器写bin-log

创建和删除触发器的语句也会写入bin-log里，所以也会如一般的insert，update，delete语句一样同步到下游数据库中，即上游创建触发器，下游也会创建。

这里再引出两个小问题：有同步关系dbA?dbB，

1)   在dbA上创建一个触发器，如果dbB上已经有同表同类型的触发器，同步状态如何？

2)   在dbB上删除一个触发器，如果dbB上没有对应触发器，同步状态如何？

这两个问题可以类比同步中的insert语句和delete语句，答案就是

1)   同步失败，因为不允许重复创建同表同类型的触发器；

2)   同步正常，因为drop一个不存在的触发器，不影响运行结果；

5   Mysql触发器经典案例

5.1   案例1 一条sql涉及多个表的update时，触发得到update之前的旧值

【现象】表test_info上建有触发器如下：

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER trig_test_info_update

AFTER UPDATE

ON FC_Word.test_info FOR EACH ROW

BEGIN

DECLARE tlevel INTEGER DEFAULT 0;

DECLARE ttype INTEGER DEFAULT 0;

SET tlevel = 4;

SET ttype = 33;

INSERT

INTO TEST_Output.fcevent (te, le, uid, pid, uid, wid, bi, mbid, wl)

SELECT ttype, tlevel, NEW.uid, NEW.pid, NEW.uid, NEW.wid, NEW.bi,

NEW.mbid, wl FROM TEST_Word.wext2 where wid = NEW.wid;

/*。。。其余部分逻辑省略*/

END IF;

END;

这个触发器程序有点长，可以单看飘黄的两句，即更新操作满足第一个条件执行飘黄语句时，触发器的行为。触发器是建立在test_info表上的，飘黄语句中可以看到，也需要查询wext2表。

执行如下sql1：

Update test_info a, wext2 b set a.th=(a.th+1), a.w4=(a.w4&8), b.wl=NULL where a.wid=b.wid and a.wid=142394379;

可以看到sql中既修改了test_info2表，同时修改了wext2表，程序原意是触发得到wext2表wl字段修改后的新值(即NULL)；不过实验得到，执行上述sql后，触发器程序查询到的wurl是sql修改之前的旧值。

再执行下面类似sql2：

Update wext2 a, test_info2 b set b.th=(b.th+1), b.w4=(b.w4&8), a.wl=NULL where a.wid=b.wid and a.wid=142394379;

实验的到，执行上述sql后，触发器程序查询到的wurl是sql修改之后的新值。

【原

因】原因当然与sql中的别名a，b无关，而是和wext2表和test_info表的书写顺序有关。如本文3.4部分所述，一条sql涉及多个表的

update操作时，数据表字段、触发器执行顺序是mysql源码包装过的。在执行上述sql1时，先执行test_info的更新，然后是after触

发器，最后是wext2的更新，也就是说，在执行after触发器时，wext2还没有进行更新，所以触发得到的是旧值。而执行sql2时，先执行

wext2更新，然后是test_info更新，最后是after触发器，也就是说，在执行after触发器时，wext2已经更新完毕，所以出去得到的

是新值。

引起上述现象是顺序关系的，无论该表是否支持事务。在使用复杂的sql时，由于有些复杂的sql是mysql自己定义的，所以存在不确定性，存在风险，使用简单的sql比较可控。

5.2   案例2 mysql5.0.19版本修改表结构后触发器失效

【现象】userpref表上建有after类型触发器，修改userpref表的外键关联后，在userpref表中的新增记录没有触发下来，即触发器失效。

【原

因】mysql5.0.19修改表结构是，触发器消失。这是mysql5.0.19的一个bug，在创建触发器时，会把触发器的内容保存在

information_schema.TRIGGERS表中，同时在var目录下创建触发器的数据库目录下创建一个触发器名称为前缀，以TRN为后缀的

文件，当修改触发器的表时，information_schema.TRIGGERS表的内容会删除，导致触发器消失。

在mysql5.0.45版本中，这个bug已经被修复。Mysql5.0.45版本的触发器，无论是修改表的索引、外键，还是改变表字段，触发器都不会失效。

5.3   案例3 删除数据表后触发器失效

【现

象】联调环境中存在dbA?dbB，主库dbA上没有触发器，在从库dbB上的FC_Word.wnegative表，FC_Word.wbuget

表上建有触发器；触发器开始运行正常，期间没有对从库的任何直接操作，有一日发现对wnegative表上的修改无法触发。查看从库状态，同步正常；用

select TRIGGER_NAME from

information_schema.TRIGGERS发现wnegative表上的触发器消失了；在var/FC_Word目录下也没有

wnegative的.TRN文件，wnegative表上的触发器不见了。

【分析】查找dbB的查询日志，发现有一条：

100223 18:27:45 135939 Query       DROP TABLE IF EXISTS `wnegative`

135939 Query       CREATE TABLE `wnegative` (

KEY `Index_wnegative_planid` (`planid`),

KEY `Index_wnegative_unitid` (`unitid`)

135939 Query       /*!40000 ALTER TABLE `wnegative` DISABLE KEYS */

100223 18:27:46 135939 Query       INSERT INTO `wnegative` VALUES (614,1,289026,2911155,1848481);

可

以看到，在100223 18:27:45时，删除了表wnegative，紧接着有创建表wnegative；查找触发表发现，在100223

18:27:45时间后对wnegative的修改就没有触发了，而在这个之前对wnegative的修改是触发正常的。故，怀疑对wnegative表

的删除使wnegative表上的触发器也被删除。对wnegative表的删除是在主库dbA上操作后，被同步到dbB上。

【原因】在删除wnegative表时，mysql同时删除了wegative表上的触发器。

可以通过下面实验证明上述猜测：

1)   首先在wnegative建立after insert型触发器；

2)   增加一条wnegative中记录；

3)   查看结果发现触发器正确触发；

4)   删除wnegative表；

5)   使用select TRIGGER_NAME from information_schema.TRIGGERS查看所有触发器，wnegative表上触发器已经不存在了；同时到var/FC_Word目录下，对应触发器的.TRN文件也不存在了；

6)   重新创建wnegative表，并增加一条wnegative中记录；没有了wnegative表上触发器，自然也不能触发任何结果。

6   结束语

本文建立在实验和案例的基础上，数据库基于线上系统使用的mysql5.0.45版本，分析了触发器相关的一些特殊情况下msyql的处理方式:)

picked from: http://hi.baidu.com/avauntage/item/023ac310391b1a011894ecd2