MySQL触发器（Trigger）_MySQL
- WBOY原創
- 2016-06-01 13:07:201229瀏覽
1 触发器简介
数据库服务器从本质上来说是被动的，我们使用一条sql语句显示的要求它，它才会执行一个操作。触发器就是要把一个被动的服务器编程一个主动的服务器。
触发器是存储在目录中的包含了过程式和声明式语句的一段代码，如果在数据库上执行了一个特定的操作，并且只有当某一条件成立的时候，数据库服务器才会激活它，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
触发器和存储过程在表现上有很多相似之处，即都是存储在目录中的一个过程式数据库对象，都是由声明式和过程式的sql语句组成。但它们两个概念之间有一个重要区别，即调用方式不同，触发器不能够从一个程序或者存储过程调用，没有call或execute trigger类似的语句。MySQL会自己透明的调用它，而不会被用户意识到。
2 触发器的优缺点
2.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