当前位置:Gxlcms > mysql > MySQL触发器(Trigger)_MySQL

MySQL触发器(Trigger)_MySQL

时间:2021-07-01 10:21:17 帮助过:29人阅读

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<触发器SQL语句>

注意:由上述可见,可以建立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)

<关键字:trigger delimiter>

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

人气教程排行