MySQL触发器

触发器

1.触发器是什么

MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程序。

触发器（TRIGGER）是MySQL的数据库对象之一，从5.0.2版本开始支持。该对象与编程语言中的函数非常类似，都需要声明、执行等。但是触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行。有点类似DOM中的事件。

简单的说，就是一张表发生了某件事（插入、删除、更新操作），然后自动触发了预先编写好的若干条SQL语句的执行。

利用MySQL触发器可以对表进行增、删、改操作同时响应另一张表做出相同的操作。

2.触发器的特点及作用

 特点：触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行。

 作用：保证数据的完整性，起到约束的作用。

3.为什么要使用触发器

在具体开发项目时，经常会遇到如下实例：

<1> 在学生表中拥有字段学生姓名，字段学生总数，每当添加一条学生信息时，学生的总数就必须同时更改。

<2> 在学生表中还会有学生姓名的缩写，学生住址等字段，添加学生信息时，往往需要检查电话、邮箱等格式是否正确。

上面的例子使用触发器完成时具有这样的特点，需要在表发生改变时，自动进行一些处理。MySQL在触发DELETE/UPDATE/INSERT语句时就会自动执行所设置的操作，其他SQL语句则不会激活触发器。

 

4.触发器的基本语法

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：触发器程序体，可以是一句SQL语句，或者用BEGIN 和END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

2> trigger_event 详解
MySQL 除了对 INSERT、UPDATE、DELETE基本操作进行定义外，还定义了 LOAD DATA和 REPLACE语句，这两种语句也能引起上述6中类型的触发器的触发。

LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT操作。

REPLACE 语句一般来说和 INSERT语句很像，只是在表中有 primary key或 unique索引时，如果插入的数据和原来 primary key或 unique索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE语句有时候等价于一条。

INSERT 语句，有时候等价于一条 DELETE语句加上一条 INSERT语句。

INSERT 型触发器：插入某一行时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE语句触发；
UPDATE 型触发器：更改某一行时激活触发器，可能通过 UPDATE语句触发；
DELETE 型触发器：删除某一行时激活触发器，可能通过 DELETE、REPLACE语句触发。

3> BEGIN … END 详解
在MySQL中，BEGIN… END语句的语法为：

BEGIN
[statement_list]
END
其中，statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。
而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN匹配的 END。

这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：
DELIMITER $
在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。

一个完整的创建触发器示例
假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数stuCount)
学生表 student(学号 stuID, 所属班级号classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

代码如下:下载地址 springmvc+mybatis+spring整合SSM   

DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;

4> 变量详解
MySQL 中使用 DECLARE来定义一局部变量，该变量只能在 BEGIN… END复合语句中使用，并且应该定义在复合语句的开头，

即其它语句之前，语法如下：

DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 为变量名称，同 SQL语句一样，变量名不区分大小写；type为 MySQL支持的任何数据类型；可以同时定义多个同类型的变量，用逗号隔开；变量初始值为 NULL，如果需要，可以使用DEFAULT 子句提供默认值，值可以被指定为一个表达式。

对变量赋值采用 SET 语句，语法为：

SET var_name = expr [,var_name = expr] ...

5> NEW 与 OLD 详解

上述示例中使用了NEW关键字，和MS SQL Server 中的INSERTED 和DELETED 类似，MySQL中定义了 NEW和 OLD，用来表示

触发器的所在表中，触发了触发器的那一行数据。
具体地：
在 INSERT 型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

6> 查看触发器

和查看数据库（show databases;）查看表格（show tables;）一样，查看触发器的语法如下：

SHOW TRIGGERS [FROM schema_name];
其中，schema_name 即Schema 的名称，在MySQL 中Schema 和Database 是一样的，也就是说，可以指定数据库名，这样就

不必先“USE database_name;”了。

7> 删除触发器

和删除数据库、删除表格一样，删除触发器的语法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

 

4.触发器的应用场景

场景一： 数据表自动备份（多个数据表字段同步等），使用触发器。如updatelog记录对资源的所有操作日志，reslastlog记录资源最后操作的日志信息。同步方式实现如下：

//创建表

DROP TABLE IF EXISTS updatelog;CREATE TABLE `updatelog` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `resourceid` int(11) DEFAULT NULL,

  `log` text,

  `createtime` datetime DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

//必须指定主键或unique，不然无法replace

DROP TABLE IF EXISTS reslastlog;CREATE TABLE `reslastlog` (

  `resourceid` int(11) NOT NULL DEFAULT '0',

  `log` text,

  `updatetime` datetime DEFAULT NULL,

  PRIMARY KEY (`resourceid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

//创建触发器

DROP TRIGGER IF EXISTS t_afterinsert_on_updatelog;delimiter //

CREATE TRIGGER t_afterinsert_on_updatelog

AFTER INSERT ON updatelog

FOR EACH ROW

BEGIN

     replace into reslastlog(resourceid,log,updatetime) values(new.resourceid, new.log, new.createtime);

END;

//

delimiter ;

DROP TRIGGER IF EXISTS t_afterdelete_on_updatelog;

delimiter //

CREATE TRIGGER t_afterdelete_on_updatelog

AFTER DELETE ON updatelog

FOR EACH ROW

BEGIN

     delete from reslastlog where resourceid=old.resourceid;

END;//

delimiter ;

//测试

insert into updatelog(resourceid, log, createtime) values(1, "version 1-0",now());

insert into updatelog(resourceid, log, createtime) values(1, "version 1-1",now());

insert into updatelog(resourceid, log, createtime) values(2, "version 2-2",now());

delete from updatelog where resourceid = 2;

//触发器相关操作

mysql> show triggers;

+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+

| Trigger                    | Event  | Table     | Statement                                                                                                          | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |

+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+

| t_afterinsert_on_updatelog | INSERT | updatelog | BEGIN

     replace into reslastlog(resourceid,log,updatetime) values(new.resourceid, new.log, new.createtime);

END | AFTER  | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |

| t_afterdelete_on_updatelog | DELETE | updatelog | BEGIN

     delete from reslastlog where resouceid=old.resourceid;

END                                           | AFTER  | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |

+----------------------------+--------+-----------+--------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+

2 rows in set (0.00 sec)

drop trigger t_afterinsert_on_updatelog;

场景二：用户定义函数或者存储过程实现简单的后台数据运算。示例如下：

//用户定义函数

//创建资源基本信息表

CREATE TABLE `baseinfo` (

  `id` int(11) DEFAULT NULL,

  `content` text

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into baseinfo values(1,"one");

insert into baseinfo values(2,"two");

insert into baseinfo values(3,"three");

//创建每日资源pv表

CREATE TABLE `dayinfo` (

  `id` int(11) DEFAULT NULL,

  `pv` int(11) DEFAULT NULL,

  `day` date DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into dayinfo values(1,10,"2014-10-01");

insert into dayinfo values(1,12,"2014-10-02");

insert into dayinfo values(1,16,"2014-10-03");

insert into dayinfo values(2, 30, "2014-10-02");

查询资源指定时段降序排列

mysql> select baseinfo.id as id, content, sum(pv) as totalpv from baseinfo,dayinfo where baseinfo.id=dayinfo.id and day>="2014-10-02" and day<="2014-10-03" group by id order by totalpv desc;

+------+---------+---------+

| id   | content | totalpv |

+------+---------+---------+

|    2 | two     |      30 |

|    1 | one     |      28 |

+------+---------+---------+

 

上面的sql语法非常复杂，如果用UDF会方便简洁很多。

1，查看用户定义函数功能是否开启，ON为开启

show variables like '%func%';

2，如果是OFF，则执行下面的操作

set global log_bin_trust_function_creators=1;

3，创建用户定义函数

delimiter $$

CREATE FUNCTION getTotalPV(targetid int,dayfrom date,dayto date) RETURNS int

begin

declare totalpv int default 0;

set totalpv=(select sum(pv) from dayinfo where id = targetid and day>=dayfrom and day<=dayto);

if totalpv is null then

    set totalpv = 0;end if;return totalpv;end$$

delimiter ;

mysql> select id, content, getTotalPV(id, "2014-10-02", "2014-10-03") as totalpv from baseinfo order by totalpv desc;

+------+---------+---------+

| id   | content | totalpv |

+------+---------+---------+

|    2 | two     |      30 |

|    1 | one     |      28 |

|    3 | three   |       0 |

+------+---------+---------+

4，查看udf定义show create function getTotalPV;

//存储过程

drop procedure if exists getjson;delimiter $$

create procedure getjson

(

   str1 varchar(1024),

   str2 varchar(1024),

   str3 varchar(1024),

   str4 varchar(1024)

)

begin

   if str1 is NULL then

       set str1="";

   end if;

   if str2 is NULL then

       set str2="";

   end if;

   if str3 is NULL then

       set str3="";

   end if;

   if str4 is NULL then

       set str4="";

   end if;

   select CONCAT("[",str1,",",str2,",",str3,",",str4,"]") as jsonstr;

end;$$

delimiter ;

mysql> call getjson("a","b","c","d");

+-----------+

| jsonstr   |

+-----------+

| [a,b,c,d] |

+-----------+

1 row in set (0.00 sec)

场景三：mysql调用外部应用程序（如表有数据更新后，通过触发器调用外部应用程序执行任务）

1.lib_mysqludf_sys简介

mysql中没有执行外部命令的函数，要调用外部的命令，可以通过开发MySQL UDF来实现，lib_mysqludf_sys 就是一个实现了此功能的UDF库。

下载地址：https://github.com/mysqludf/lib_mysqludf_sys

2.使用方法2.1 安装部署(需要安装mysql-devel)

a) lib_mysqludf_sys.so复制到mysql/lib/plugin目录下。

b) 在mysql中创建函数(根据需要选取)：

Drop FUNCTION IF EXISTS lib_mysqludf_sys_info;

Drop FUNCTION IF EXISTS sys_get;

Drop FUNCTION IF EXISTS sys_set;Drop FUNCTION IF EXISTS sys_exec;

Drop FUNCTION IF EXISTS sys_eval;

Create FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';

Create FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys.so';

Create FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys.so';

Create FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';

Create FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys.so';

2.2 使用此函数

例：在select语句调用mkdir命令

 Select sys_exec('mkdir -p /home/user1/aaa')

例：在触发器中调用外部的脚本(脚本需要可执行权限)

Create TRIGGER trig_test AFTER Insert ON <table1>

FOR EACH ROW

BEGIN

    DECLARE ret INT;    

Select sys_exec('/home/user1/test.sh') INTO ret;

END

5.触发器的执行顺序

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL会回滚事务，有：

①如果 BEFORE 触发器执行失败，SQL无法正确执行。
②SQL 执行失败时，AFTER型触发器不会触发。
③AFTER 类型的触发器执行失败，SQL会回滚

6.为什么要慎用触发器

触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作…… 同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

7.存储过程与触发器的区别

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

 

8.触发器的优点

 1> 触发器的"自动性"
    对程序员来说，触发器是看不到的，但是他的确做事情了，如果不用触发器的话，你更新了user表的name字段时，你还要写代码去更新其他表里面的冗余字段，我举例子，只是一张表，如果是几张表都有冗余字段呢，你的代码是不是要写很多呢，看上去是不是很不爽呢。
 2> 触发器的数据完整性
    触发器有回滚性.举个例子，我发现我很喜欢举子，就是你要更新五张表的数据，不会出现更新了二个张表，而另外三张表没有更新。
    但是如果是用php代码去写的话，就有可能出现这种情况的，比如你更新了二张表的数据，这个时候，数据库挂掉了。你就郁闷了，有的更新了，有的没更新。这样页面显示不一致了，变有bug了。

 

------整理自网络