数据库触发器

1、数据库触发器详细1 引言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标识建立触发器的表名,即在哪张表上建立触发器

2、;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 NE

3、W.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,

4、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.us

5、eracct新增行的 ulevelid 列值;如果是 delete 型触发器 OLD.ulevelid 也表示表 SF_User.useracct删除行的 ulevelid 列原值。另外, OLD 列是只读的, NEW 列则可以在触发器程序中再次赋值。上述实例也使用了 IF , THEN , ELSE , END IF等关键字。 在触发器程序体中, 在 beigin 和 end 之间,可以使用顺序,判断,循环等语句,实现一般程序需要的逻辑功能。查看触发器。 查看触发器语法如下, 如果知道触发器所在数据库, 以及触发器名称等具体信 息:SHOW TRIGGERS from SF_User lik

6、e "usermaps%" /查看 SF_User库上名称和 usermaps%匹配的触发器如果不了解触发器的具体的信息,或者需要查看数据库上所有触发器,如下:SHOW TRIGGERS; /查看所有触发器用上述方式查看触发器可以看到数据库的所有触发器, 不过如果一个库上的触发器太多, 由 于会刷屏,可能没有办法查看所有触发器程序。这时,可以采用如下方式:Mysql 中有一个 information_schema.TRIGGERS表,存储所有库中的所有触发器, desc information_schema. TRIGGERS,可以看到表结构:+-+-+-+-+-+-+|

7、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_OBJEC

8、T_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 | | | | | ACTI

9、ON_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 |

10、 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_ev

11、ent, 上文说过, 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个相同类型的触发器。 这个限制的

12、一个来源 是触发器程序体的“ begin 和 end 之间允许运行多个语句” (摘自 mysql 使用手册 。另外还有一点需要注意, msyql 除了对 insert , update , delete 基本操作进行定义外,还定义 了 load data和 replace 语句,而 load data和 replace 语句也能引起上述 6中类型的触发器的 触发。Load data 语句用于将一个文件装入到一个数据表中,相当与一系列 insert 操作。 replace 语 句一般来说和 insert 语句很像,只是在表中有 primary key和 unique 索引时,如果插入的数 据和原

13、来 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

14、 类型的触发器程序执行失败, 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;+-+| showpro

15、b |+-+| 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 触

16、发器执行失败, 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.pla

17、nid, 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 plani

18、d=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中建立

19、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 wh

20、ere 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 引擎

21、也是目前线上凤 巢系统、北斗系统以及哥伦布系统所使用的引擎,在 innodb 上所建立的表是事务性表,也 就是事务安全的。 “对于事务性表,如果触发程序失败(以及由此导致的整个语句的失败 , 该语句所执行的所有更改将回滚。对于非事务性表,不能执行这类回滚” (摘自 mysql 使用手册 。因而,即使语句失败,失败之前所作的任何更改依然有效,也就是说,对于 innodb 引擎上的数据表,如果触发器中的 sql 或引发触发器的 sql 执行失效,则事务回滚,所有操 作会失效。3.4 mysql 触发器程序执行的顺序当一个表既有 before 类型的触发器,又有 after 类型的触发器时;当一条

22、sql 语句涉及多个 表的 update 时, sql 、触发器的执行顺序经过 mysql 源码包装过,有时比较复杂。可以先看一段 mysql 的源代码, 当 SQL 中 update 多表的时候, Mysql 的执行过程如下 (省去 了无关代码 :/* 遍历要更新的所有表 */for (cur_table= update_tables; cur_table; cur_table= cur_table->next_localorg_updated = updated/* 如果有 BEFORE 触发器,则执行;如果执行失败,跳到 err2位置 */if (table->trigger

23、s &&table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,TRG_ACTION_BEFORE, TRUE goto err2;/*执行更新,如果更新失败,跳到 err 位置 */if(local_error=table->file->update_row(table->record1, table->record0goto err;updated+; / 更新计数器/* 如果有 AFTER 触发器,则执行;如果执行失败,跳到 err2位置 */if (table->trigg

24、ers &&table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER, TRUE goto err2;err:/*标志错误信息,写日志等 */err2:/*恢复执行过的操作 */check_opt_it.rewind(;/*如果执行了更新,且表是有事务的,做标志 */if (updated != org_updatedif (table->file->has_transactions(transactional_tables= 1;从上面代码可以找到本章开始时抛出问

25、题的答案。1 如果 before 型触发器执行失败,直接 goto 跳到 err2位置,不会执行后续 sql 语句;2 如果 sql 执行失败,直接 goto 跳到 err 位置,不会执行或许的 after 型触发器;3 如过 after 触发器执行失败, goto 到 err2位置,恢复执行过的操作,且在事务型的表 上做标记。另外, 在使用复杂的 sql 时, 由于有些复杂的 sql 是 mysql 自己定义的, 所以存在不确定性, 使用简单的 sql 比较可控。4 Mysql 触发器在数据库同步中的表现4.1 触发器运行失败时,数据库同步会失败吗?有同步关系如下 dbA?dbB 。初始时同

26、步正常。1在 dbB 上建立触发器:DELIMITER |create trigger trigger_after_planinfo_updateafter updateON FC_Word.planinfo FOR EACH ROWBEGINinsert 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 secRows ma

27、tched: 1 Changed: 1 Warnings: 03由于 dbB 上没有 FC_Output.abc表,触发器会执行失败,这时,检查一下同步状态:Slave_IO_Running: YesSlave_SQL_Running: NOLast_Errno: 1146Last_Error: Error 'Table 'FC_Output.abc' doesn't exist' on query. Default database: 'FC_Word'. Query: 'update planinfo set showprob

28、=200 where planid= 1'可以看到 IO 线程运行正常, sql 线程运行失败,并提示触发器运行失败的错误信息。回忆一下 3.1和 3.3所述部分,无论是 before 部分的触发器还是 after 类型的触发器,对于 innodb 引擎,当触发器执行失败时,相应 sql 也会执行失败,所以数据库同步也会失败。4.2 创建、删除触发器写 bin-log创建和删除触发器的语句也会写入 bin-log 里,所以也会如一般的 insert , update , delete 语 句一样同步到下游数据库中,即上游创建触发器,下游也会创建。这里再引出两个小问题:有同步关系 dbA

29、?dbB ,1 在 dbA 上创建一个触发器, 如果 dbB 上已经有同表同类型的触发器, 同步状态如何?2 在 dbB 上删除一个触发器,如果 dbB 上没有对应触发器,同步状态如何?这两个问题可以类比同步中的 insert 语句和 delete 语句,答案就是1 同步失败,因为不允许重复创建同表同类型的触发器;2 同步正常,因为 drop 一个不存在的触发器,不影响运行结果;5 Mysql 触发器经典案例5.1 案例 1 一条 sql 涉及多个表的 update 时,触发得到 update 之前的旧值【现象】表 test_info上建有触发器如下:CREATE /*!50017 DEFIN

30、ER = 'root''localhost' */ TRIGGER trig_test_info_updateAFTER UPDATEON FC_Word.test_info FOR EACH ROWBEGINDECLARE 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;这个触发器程序有点长, 可以单看