MySQL触发器的基本使用

一、Mysql触发器的基本使用 ① 创建触发器 创建触发器语法如下： view plain copy CREATE TRIGGER trigger_nametrigger_timetrigger_event ON tbl_name FOR EACHROWtrigger_stmt ■ trigger_name：标识触发器名称，用户自行指定； ■ trigger_time：标识触发

一、Mysql触发器的基本使用

① 创建触发器

创建触发器语法如下：

<p><span>view plain   <span>copy</span></span></p><ol>
<li><span><span><span>CREATE</span><span> </span><span>TRIGGER</span><span> trigger_name trigger_time trigger_event </span></span></span></li>
<li><span><span><span>ON</span><span> tbl_name </span><span>FOR</span><span> EACH ROW trigger_stmt </span></span></span></li>
</ol>

■ trigger_name：标识触发器名称，用户自行指定；

■ trigger_time：标识触发时机，用before和after替换；

■ trigger_event：标识触发事件，用insert，update和delete替换；

■ tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；

■ trigger_stmt：触发器程序体。

触发器程序可以使用begin和end作为开始和结束，中间包含多条语句。下面给出一个触发器实例：

<p><span>view plain   <span>copy</span></span></p><ol>
<li><span><span><span>CREATE</span><span> </span><span>TRIGGER</span><span> trig_useracct_update </span></span></span></li>
<li><span><span><span>AFTER</span><span> </span><span>UPDATE</span><span> </span></span></span></li>
<li><span><span><span>ON</span><span> SF_User.useracct </span><span>FOR</span><span> EACH ROW </span></span></span></li>
<li><span><span><span>BEGIN</span><span> </span></span></span></li>
<li><span><span>IF OLD.ulevelid = 10101 <span>OR</span><span> OLD.ulevelid = 10104 </span><span>THEN</span><span> </span></span></span></li>
<li><span><span>    IF NEW.ulevelid = 10101 <span>OR</span><span> NEW.ulevelid = 10104 </span><span>THEN</span><span> </span></span></span></li>
<li><span><span>        IF NEW.ustatid != OLD.ustatid <span>OR</span><span> NEW.exbudget != OLD.exbudget </span><span>THEN</span><span> </span></span></span></li>
<li><span><span>            <span>INSERT</span><span> </span><span>into</span><span> FC_Output.fcevent </span><span>set</span><span> type = 2, tabid = 1, </span><span>level</span><span> = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget; </span></span></span></li>
<li><span><span>        <span>END</span><span> IF; </span></span></span></li>
<li><span><span>    <span>ELSE</span><span> </span></span></span></li>
<li><span><span>        <span>INSERT</span><span> </span><span>into</span><span> FC_Output.fcevent </span><span>set</span><span> type = 1, tabid = 1, </span><span>level</span><span> = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget; </span></span></span></li>
<li><span><span>    <span>END</span><span> IF; </span></span></span></li>
<li><span><span><span>END</span><span> IF; </span></span></span></li>
<li><span><span><span>END</span><span>; </span></span></span></li>
</ol>

上述触发器实例使用了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之间，可以使用顺序，判断，循环等语句，实现一般程序需要的逻辑功能。

② 查看触发器

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

<p><span>view plain   <span>copy</span></span></p><ol>
<li><span><span><span>SHOW TRIGGERS </span><span>from</span><span> SF_User </span><span>like</span><span> </span><span>"usermaps%"</span><span>; </span></span></span></li>
<li><span><span>/* 查看SF_User库上名称和usermaps%匹配的触发器 */ </span></span></li>
</ol>

如果不了解触发器的具体的信息，或者需要查看数据库上所有触发器，如下：

<p><span>view plain   <span>copy</span></span></p><ol>
<li><span><span>SHOW TRIGGERS; </span></span></li>
<li><span><span>//查看所有触发器 </span></span></li>
</ol>

用上述方式查看触发器可以看到数据库的所有触发器，不过如果一个库上的触发器太多，由于会刷屏，可能没有办法查看所有触发器程序。这时，可以采用如下方式：

MySQL中有一个information_schema.TRIGGERS表，存储所有库中的所有触发器，desc information_schema.TRIGGERS，可以看到表结构：

<p><span>view plain   <span>copy</span></span></p><ol>
<li><span><span>+----------------------------+--------------+------+-----+---------+-------+ </span></span></li>
<li><span><span>| Field                      | Type         | Null | Key | Default | Extra | </span></span></li>
<li><span><span>+----------------------------+--------------+------+-----+---------+-------+ </span></span></li>
<li><span><span>| TRIGGER_CATALOG            | varchar(512) | YES  |     | NULL    |       | </span></span></li>
<li><span><span>| TRIGGER_SCHEMA             | varchar(64)  | NO   |     |         |       | </span></span></li>
<li><span><span>| TRIGGER_NAME               | varchar(64)  | NO   |     |         |       | </span></span></li>
<li><span><span>| EVENT_MANIPULATION         | varchar(6)   | NO   |     |         |       | </span></span></li>
<li><span><span>| EVENT_OBJECT_CATALOG       | varchar(512) | YES  |     | NULL    |       | </span></span></li>
<li><span><span>| EVENT_OBJECT_SCHEMA        | varchar(64)  | NO   |     |         |       | </span></span></li>
<li><span><span>| EVENT_OBJECT_TABLE         | varchar(64)  | NO   |     |         |       | </span></span></li>
<li><span><span>| ACTION_ORDER               | bigint(4)    | NO   |     | 0       |       | </span></span></li>
<li><span><span>| ACTION_CONDITION           | longtext     | YES  |     | NULL    |       | </span></span></li>
<li><span><span>| ACTION_STATEMENT           | longtext     | NO   |     |         |       | </span></span></li>
<li><span><span>| ACTION_ORIENTATION         | varchar(9)   | NO   |     |         |       | </span></span></li>
<li><span><span>| ACTION_TIMING              | varchar(6)   | NO   |     |         |       | </span></span></li>
<li><span><span>| ACTION_REFERENCE_OLD_TABLE | varchar(64)  | YES  |     | NULL    |       | </span></span></li>
<li><span><span>| ACTION_REFERENCE_NEW_TABLE | varchar(64)  | YES  |     | NULL    |       | </span></span></li>
<li><span><span>| ACTION_REFERENCE_OLD_ROW   | varchar(3)   | NO   |     |         |       | </span></span></li>
<li><span><span>| ACTION_REFERENCE_NEW_ROW   | varchar(3)   | NO   |     |         |       | </span></span></li>
<li><span><span>| CREATED                    | datetime     | YES  |     | NULL    |       | </span></span></li>
<li><span><span>| SQL_MODE                   | longtext     | NO   |     |         |       | </span></span></li>
<li><span><span>| DEFINER                    | longtext     | NO   |     |         |       | </span></span></li>
<li><span><span>+----------------------------+--------------+------+-----+---------+-------+ </span></span></li>
</ol>

这样，用户就可以按照自己的需要，查看触发器，比如使用如下语句查看上述触发器：

<p><span>view plain   <span>copy</span></span></p><ol><li><span><span><span>select</span><span> * </span><span>from</span><span> information_schema. TRIGGERS </span><span>where</span><span> TRIGGER_NAME= </span><span>'trig_useracct_update'</span><span>; </span></span></span></li></ol>

③ 删除触发器

删除触发器语法如下：

<p><span>view plain   <span>copy</span></span></p><ol><li><span><span>DROP TRIGGER [schema_name.]trigger_name </span></span></li></ol>

二、MySQL触发器的trigger_time和trigger_event

现在，重新注意到trigger_time和trigger_event，上文说过， trigger_time可以用before和after替换，表示触发器程序的执行在sql执行的前还是后；trigger_event可以用 insert，update，delete替换，表示触发器程序在什么类型的sql下会被触发。

在一个表上最多建立6个触发器，即① before insert型；② before update型；③ before delete型；④ after insert型；⑤ after update型；⑥ after delete型。

触发器的一个限制是不能同时在一个表上建立2个相同类型的触发器。这个限制的一个来源是触发器程序体的“begin和end之间允许运行多个语句”（摘自MySQL使用手册）。

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