> 数据库 > MySQL >

MYSQL触发器的实战经验

mysql触发器的实战经验
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_event
ON 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 TRIGGER trig_useracct_update
AFTER UPDATE
ON SF_User.useracct FOR EACH ROW
BEGIN
IF OLD.ulevelid = 10101 OR OLD.ulevelid = 10104 THEN
IF NEW.ulevelid = 10101 OR NEW.ulevelid = 10104 THEN
if NEW.ustatid != OLD.ustatid OR NEW.exbudget != OLD.exbudget THEN
INSERT into FC_Output.fcevent set type = 2, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
end if;
ELSE
INSERT 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  



(责任编辑:IT)