MySQL触发器（Trigger）_MySQL

1 触发器简介

    数据库服务器从本质上来说是被动的，我们使用一条sql语句显示的要求它，它才会执行一个操作。触发器就是要把一个被动的服务器编程一个主动的服务器。

    触发器是存储在目录中的包含了过程式和声明式语句的一段代码，如果在数据库上执行了一个特定的操作，并且只有当某一条件成立的时候，数据库服务器才会激活它，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

    触发器和存储过程在表现上有很多相似之处，即都是存储在目录中的一个过程式数据库对象，都是由声明式和过程式的sql语句组成。但它们两个概念之间有一个重要区别，即调用方式不同，触发器不能够从一个程序或者存储过程调用，没有call或execute trigger类似的语句。MySQL会自己透明的调用它，而不会被用户意识到。

2 触发器的优缺点
    2.1 优点

2.1.1 自动。以事件方式来处理，当数据发生变化的时候, 自动作处理，对程序员来说，触发器是看不到的，但是它的确做事情了。
2.1.2 安全。不会有SQL语句注入问题存在，可以基于数据库的值限制用户操作数据库的某种权利。
    如：可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
    可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。
2.1.3 审计。可以跟踪用户对数据库的操作。
    如：审计用户操作数据库的语句。
    把用户对数据库的更新写入审计表。
2.1.4 业务逻辑封装性好，修改方便。
2.1.5 数据完整性。
    2.1.5.1实现复杂的数据完整性规则
        实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
        提供可变的缺省值。
    2.1.5.2实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。
        触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。如对5张表进行更新，更新到两张表时数据库服务器宕机，重启后会自动回退。

    2.2 缺点
        2.2.1 可移植性差。
        2.2.2 定时触发，不可以调用。
        2.2.3 当数据库之间数据导出导入的时候, 可能会引起不必要的触发逻辑。
        2.2.4 精通SQL的新手越来越少。

3 创建触发器

    3.1 创建语法如下

CREATE TRIGGER trigger_name    trigger_time trigger_event ON tbl_nameFOR EACH ROWtrigger_stmt

    其中：trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
                trigger_event指明了激活触发程序的语句的类型。它可以是INSERT，UPDATE或DELETE
                trigger_stmt是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN ... END复
合语句结构。

create trigger { before | after} {insert | update | delete} on for each row

    注意：由上述可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
                另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

3.2 例如：两张表联合更新

    创建测试表1

drop table if exists tab_first;create table tab_first(tab_first_id varchar(11));

    创建测试表2

drop table if exists tab_sec;create table tab_sec(tab_sec_id varchar(11));

    创建触发器

drop trigger if exists tri_afterinsert_tab_first;delimiter //create trigger tri_afterinsert_tab_firstafter insert on tab_firstfor each rowbegin    insert into tab_sec(tab_sec_id) values (new.tab_first_id);end//delimiter ;

    3.3 注意：    触发器可以调用存储过程。

4 查看触发器

    查看数据库中所有存储的触发器基本信息，包括所属数据库，触发器名称，创建时间，创建语句等。

show triggers /G

    或者

SELECT * FROM information_schema.triggers  WHERE trigger_name='tri_afterinsert_tab_first' /G

5 触发 触发器

    5.1 触发方式

    满足触发器所预定的触发条件，触发器即可被触发。

    5.2 例如，增加tab_first表记录后自动将相应记录增加到tab_sec表中

mysql> insert into tab_first(tab_first_id) values ("test1");mysql> select * from tab_first;+--------------+| tab_first_id |+--------------+| test1        |+--------------+mysql> select * from tab_sec;+------------+| tab_sec_id |+------------+| test1      |+------------+

注意：

6 修改存储过程

    6.1 基本语法

ALTER PROCEDURE 存储过程名 [characteristic ...]

    注意：characteristic是存储过程创建时的特征，在CREATE PROCEDURE语句中已经介绍过。只要设定了其中的值，存储过程的特征就随之变化。
    如果要修改存储过程的内容，可以使用先删除再重新定义存储过程的方法。存储过程某些的特征如下
    characteristic:
    COMMENT 'string'
    | LANGUAGE SQL
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

    | SQL SECURITY { DEFINER | INVOKER }

    6.2 例如：修改特征

    查看默认特征值

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='procedureAdd';+---------------+-----------------+-----------------+| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |+---------------+-----------------+-----------------+| procedureAdd  | CONTAINS SQL    |                 |+---------------+-----------------+-----------------+

    将读写权限改为READS SQL DATA，并加上注释信息'This is a test!',代码执行如下：

mysql> ALTER  PROCEDURE  procedureAdd READS SQL DATA COMMENT 'This is a test!';

    查看修改后的特征值

mysql> SELECT SPECIFIC_NAME,SQL_DATA_ACCESS, ROUTINE_COMMENT FROM information_schema.Routines WHERE ROUTINE_NAME='procedureAdd';+---------------+-----------------+-----------------+| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |+---------------+-----------------+-----------------+| procedureAdd  | READS SQL DATA  | This is a test! |+---------------+-----------------+-----------------+

    说明：从查询的结果可以看出，访问数据的权限（SQL_DATA_ACCESS）已经变成READS SQL DATA，函数注释（ROUTINE_COMMENT）已经变成了"This is a test!"。

7 删除存储过程

    7.1 基本语法

DROP PROCEDURE  IF  EXISTS 存储过程名

    7.2 例如

DROP PROCEDURE  IF  EXISTS procedureAdd;

    注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。

***************************************************************************************************************************************************************

8 存储过程局部变量

    8.1 介绍

    在一个存储过程内部，可以声明局部变量。他们可以用来存储中间临时结果。如果我们在一个存储过程中需要一个局部变量，必须使用DECLARE VARIABLE语句引入它。通过声明，就确定了变量的数据类型，并且也可以指定初始值。（如果使用了DECLARE VARIABLE语句，他们必须作为BEGIN-END语句块的第一条语句包含其中）

    8.2 例如：

delimiter //create procedure test(out num1 integer) begin     declare num2 integer default 100;     set num1 = num2; end//delimiter ;

    调用存储过程

call test(@num);select @num;

9 存储过程和用户变量

    9.1 介绍

    用户变量总有一个全局特性，即便它在一个存储过程内部创建，在存储过程结束后他们依然保留。在存储过程之外创建的用户变量，仍然可以在存储过程中保留他们自己的值。

    9.2 例如

delimiter //create procedure user_variable() begin     set @varTest = 1; end//delimiter ;

    调用存储过程后查看varTest值为1

call user_variable();select @varTest;

    说明：set语句是sql本身的一部分，它可以讲一个值赋给用户变量和局部变量，也可使用任何随机表达式。

10 存储过程与游标

    10.1 介绍

    常规的select语句可能返回多行，使用游标（cursor）可以处理这一点，把数据一行一行的取入到存储过程中。使用游标需要用到四个特殊语句：declare sursor，open sursor，fetch cursor，和close cursor。

    如果使用declare cursor语句声明一个游标，我们就把它连接到了一个表表达式。接下来就可以使用fetch cursor语句来把产生的结果一行一行的获取到存储过程中。在某个时刻，结果中只有一行可见，也就是当前行。它就好像是指向结果中一行的一个箭头，这也是游标这个名字的来历。使用fetch cursor这条语句，我们可以把游标移动到下一行，当处理完所有的行，可以使用close cursor语句来删除结果。

    10.2 游标作用及属性

    作用:
        就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作；
    属性：
        游标是只读的，也就是不能更新它；
        游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；  
        避免在已经打开游标的表上更新数据。

    10.3 如何使用游标

    声明游标

DECLARE cursor_name CURSOR FOR SELECT语句;  

    打开游标

OPEN cursor_name; 

    移动游标

FETCH cursor_name INTO variable list;  

    关闭游标

CLOSE cursor_name;  

    10.4 游标实例

    创建测试表及数据

CREATE TABLE test.users (    ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,    user_name varchar(60) NOT NULL DEFAULT '',    user_pass varchar(64) NOT NULL DEFAULT '',    PRIMARY KEY (ID)   )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into test.users values(1,'name1', 'pass1');insert into test.users values(2,'name2', 'pass2');insert into test.users values(3,'name3', 'pass3');insert into test.users values(4,'name4', 'pass4');insert into test.users values(5,'name5', 'pass5');

    创建游标存储过程

delimiter |create procedure test_cursor (in param int(10),out result varchar(90))begin    declare name varchar(20);    declare pass varchar(20);    declare done int;    declare cur_test CURSOR for select user_name,user_pass from test.users;    declare continue handler FOR SQLSTATE '02000' SET done = 1;    if param then        select concat_ws(',',user_name,user_pass) into result from test.users where id=param;    else        open cur_test;        repeat            fetch cur_test into name, pass;            select concat_ws(',',result,name,pass) into result;            until done end repeat;        close cur_test;    end if;end;|delimiter ;

    各行命令详解

    1行，告诉MySQL解释器，输入结束命令改为|，默认为;(命令本身与存储过程无关)    2行，创建一个存储过程，注意：如果我把out result varchar(90)改成out result varchar，返回的结果中只有一个字符。    3行，开始    4行，定义一个变量name    5行，定义变量pass    6行，定义一个结束标识    7行，定义一个光标，指向select user_name,user_pass from test.users;语句    8行，如果sqlstate等于02000时，把done设置成1,也就是找不到数据时    9，11,18行，if判断    10行，根据参数，把数据取出来，放到result中，concat_ws函数表示concat with separator,即有分隔符的字符串连接，如连接后以逗号分隔    12行，打开光标    13，16行，repeat循环，根php的do while原理一样    14行，从光标中取出数据。    15行，将数据合并起来    17行，关闭光标    18,19行，标签闭合。    20行，

    结果反馈

mysql> call test_cursor(3,@test);                                               Query OK, 1 row affected (0.00 sec)mysql> select @test;+-------------+| @test       |+-------------+| name3,pass3 |+-------------+1 row in set (0.00 sec)mysql> call test_cursor('',@test);Query OK, 1 row affected, 2 warnings (0.00 sec)mysql> select @test;+-------------------------------------------------------------------------+| @test                                                                   |+-------------------------------------------------------------------------+| name1,pass1,name2,pass2,name3,pass3,name4,pass4,name5,pass5,name5,pass5 |+-------------------------------------------------------------------------+1 row in set (0.00 sec)

转载请注明出处：http://blog.csdn.net/jesseyoung/article/details/34826721