第八章 - 存储过程、游标和触发器
第八章 - 存储过程、游标和触发器
- 存储过程（Stored Procedure）是一组完成特定功能的MySQL语句的集合，即将一些固定的操作集中起来由MySQL服务器来完成，应用程序只需调用它就可以实现某个特定的任务。存储过程是可以通过用户、其他存储过程或触发器来调用执行。
- MySQL中的游标（Cursor）是一种实现对select结果集中的数据进行访问和处理的机制，允许用户访问单独的数据行。MySQL中的游标一般通过存储过程来实现其操作。
- 触发器（Trigger）是一种特殊的存储过程。触发器通常在特定的表上定义，当该表的相应事件发生时自动执行，用于实现强制业务规则和数据完整性等
- 事件（Event）又称事件调度器(Event Scheduler)，有时也可称为临时触发器(temporal triggers)，因为事件调度器是基于特定时刻或时间周期触发来执行某些任务，而触发器是基于对表进行操作所产生的事件触发的。
本章将介绍存储过程、游标、触发器和事件的基本概念，及其的创建和管理的基本操作。
8.1 存储过程
8.1.1 认识存储过程
在MySQL数据库中，利用存储过程可以保证数据的完整性，提高执行重复任务的性能和数据的一致性。
例如，银行经常核算用户的利息。不同类别的用户的存款利率是不一样的。这就可以将计算利率的SQL代码写成一个存储过程。只要将客户的某一笔存款的存款时间和存款额数输入，调用这个存储过程就可以核算出用户的利息。存储过程在被调用的过程中，参数可以被传递和返回，出错代码也可以被检验。
存储过程主要应用于控制访问权限、为数据库表中的活动创建审计追踪、将关系到数据库及其所有相关应用程序的数据定义语句和数据操作语句分隔开。
1. 存储过程的优势
利用存储过程可以让系统达到如下目的：
(1) 提高了处理复杂任务的能力。主要用于在数据库中执行操作的编程语句，通过接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。
(2) 增强了代码的复用率和共享性。存储过程一旦创建后即可在程序中调用任意多次，这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。
(3) 减少了网络中数据的流量。因为存储过程存储在服务器上，并在服务器上运行。一个需要数百行MySQL代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。
(4) 存储过程在服务器注册，加快了过程的运行速度。存储程序只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般MySQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。
(5) 加强了系统的安全性。存储过程具有安全特性（例如权限）和所有权链接，用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。可以强制应用程序的安全性，参数化存储过程有助于保护应用程序不受SQL注入式攻击。
2. 创建存储过程格式
创建存储过程可以使用create procedure语句。要创建存储过程，必须具有create routine的权限。
create procedure的语法格式如下：
create procedure sp_name([proc_parameter[,...]])
[characteristic ...] routine_body
说明：
(1) sp_name为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。
(2) parameter为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。
3. 存储过程的创建和执行
【例8.1】创建存储过程proc_stu，从数据库teaching的student表中检索出所有电话以131开头的学生的学号、姓名、出生日期和电话等信息。
mysql> use teaching;
mysql> delimiter //
mysql> create procedure proc_stu()
-> reads sql data
-> begin
-> select studentno,sname,birthdate ,phone
-> from student
-> where phone like '%131%' order by studentno ;
-> end//
mysql> delimiter ;
调用存储过程proc_stu()的代码和执行结果如下：
mysql> call proc_stu();
【例8.2】创建存储过程avg_score，输入课程号后，统计该课程的平均成绩。
mysql> delimiter //
mysql> create procedure avg_score(in c_no char(6))
-> begin
-> select courseno,avg(final)
-> from score
-> where courseno=c_no ;
-> end //
mysql> delimiter ;
调用存储过程avg_score ()的代码和执行结果如下：
mysql> call avg_score(‘c05109’);
【例8.3】创建存储过程select_score()，用指定的学号和课程号为参数查询学生成绩。分析：创建带多个输入参数的存储过程。
mysql> delimiter $$
mysql> create procedure select_score(in s_no char(11),c_no char(6))
-> begin
-> select * from score
-> where studentno=s_no and courseno= c_no ;
-> end $$
mysql> delimiter ;
调用存储过程select_score()的代码和执行结果如下：
mysql> call select_score('18125121107','c05109');
【例8.4】创建存储过程stu_score的，统计指定同学的考试门数
分析：在本存储过程中，输入参数为学号s_no，输出参数为count_num，select语句用count(*)计算指定学生的考试门数，最后将计算结果存入count_num中。 调用有输出参数的存储过程时，可以通过会话变量@c_num实现。
mysql> delimiter //
mysql> create procedure stu_scores(in s_no char(11), out count_num int)
-> reads SQL data
-> begin
-> select count(*) into count_num from score
-> where studentno=s_no;
-> end //
mysql> delimiter ;
调用存储过程stu_scores ()的代码和执行结果如下：
mysql> call stu_scores(‘18125121107’, @c_num );
mysql> select @c_num;
说明：
(1) 存储过程是已保存的MySQL语句集合。对于一般的select语句，如果查询的数据要来自于多个表，可以使用多表连接或子查询等方式。
(2) 当调用存储过程时，MySQL会根据提供的参数值，执行存储过程体中的SQL语句。
【例8.5】创建存储过程do_query，输入指定学号，查看该生的成绩高于85分的科目数，如果超过2科，则输出very good!，并输出该生的成绩单，否则输出come on!。
分析：存储过程do_query中，利用if语句实现较为复杂的功能。该存储过程用declare语句声明了局部变量AA。根据指定学号，进行统计该生高于85分的科目数，并使用select into语句为变量AA赋值，然后根据AA的值进行判断。
mysql> create procedure do_query(in s_no char(11), out str char(12))
-> begin
-> declare AA tinyint default 0;
-> select count(*) into AA from score
-> where studentno= s_no and final>85;
-> if AA>=2 then
-> begin
-> set str='very good! ';
-> select * from score where studentno = s_no;
-> end;
-> elseif AA<2 then
-> set str='come on! ';
-> end if;
-> end //
mysql> delimiter ;
调用存储过程do_query ()的代码和执行结果如下：
mysql> call do_query(‘18125111109’,@str);
mysql> select @str;
mysql> call do_query('18122210009',@str);
【例8.6】创建一个存储过程，向score表中插入一行记录，然后创建另一存储过程do_outer()，调用存储过程do_insert()，并查询输出score表中插入的记录。
分析：利用存储过程中调用其他存储过程。在调用存储过程do_outer()时，先执行第一个存储过程do_insert()，插入了一行记录，然后再执行后面的语句，输出查询结果。
--先创建第1个存储过程do_insert()
mysql> create procedure do_insert()
-> insert into score values(‘18125111109’, ‘c05109’ ,89,92);
--创建第2个存储过程do_outer()，调用do_insert()
mysql> delimiter $$
mysql> create procedure do_outer()
-> begin
-> call do_insert();
-> select * from score
-> where studentno='18125111109';
-> end $$
调用存储过程do_outer ()的代码和执行结果如下：
mysql> call do_outer();
8.1.2 存储过程的创建和管理
1. 查看存储过程的定义
存储过程和函数创建以后，用户可以查看存储过程和函数的状态和定义。用户可以通过show status语句来查看存储过程和函数的状态，也可以通过show create语句来查看存储过程和函数的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过程和函数的信息。
在前面学习存储函数的基础上，下面给出已经验证过的查看存储过程的状态和定义的方法的例子。
mysql> show procedure status like 'do_%';
mysql> show create procedure do_outer;
mysql> select *from information_schema.routines
-> where routine_name='do_outer ' ;
mysql> show create procedure do_outer;
2. 条件和处理程序的定义
默认情况下，MySQL存储程序运行过程中发生错误时，将自动终止程序的执行。此时，数据库开发人员有时希望自己控制程序的运行流程，并不希望MySQL将自动终止存储程序的执行，MySQL的错误处理机制可以帮助数据库开发人员自行控制程序流程。
定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过declare关键字来定义条件和处理程序。通过定义条件来对可能涉及错误以及子程序中的一般流程进行控制。
(1) 定义条件
在MySQL中定义条件的基本语法如下。
declare condition_name condition for condition_type;
condition_type:
sqlstate [value] sqlstate_value|mysql_error_code
说明：
1）condition_name：表示错误触发条件的名称。
2）condition_type：表示条件的类型，分为MySQL错误代码或者ANSI标准错误代码。sqlstate_value 为长度为5的字符串类型错误代码； mysql_error_code 为数值型错误代码。例如，ERROR 1147 (42S07)中，sqlstate_value的值为字符串“42S07”，mysql_error_code 为1147。
3）此语句指定需要特殊处理的条件，将指定的错误条件与一个名字联系起来。这个名字即错误名，可以在随后的定义处理程序中的declare handler语句中应用。
【例8.7】 定义“ERROR 1147 (42S07)”这个错误，名称为cannot_found。
可以用两种不同的方法来定义，代码如下。
--使用sqlstate_value方法定义
declare cannot_found condition for sqlstate'42S07';
--使用mysql_error_code方法定义
declare cannot_found condition for 1147;
(2) 定义处理程序
MySQL中定义处理程序的基本语法如下。
declare handler_type handler for condition_value[,…]
sp_statement
handler_type:
continue|exit
condition_value:
sqlstate [value]sqlstate_value|condition_name
|sqlwarning|not found|sqlexception|mysql_error_code
（1）handler_type：错误处理类型，取值包括continue和exit。continue表示遇到错误不处理， 继续执行其他MySQL语句；exit表示遇到错误马上退出其他MySQL语句的执行。
（2）condition_value：错误触发条件，表示满足什么条件时，自定义错误处理程序开始运行，错误触发条件定义了自定义错误处理程序运行的时机。
具体包括如下取值：
sqlstate [value] sqlstate_value ：长度为5的字符串类型错误代码；
condition_name ：表示declare condition定义的错误条件名称；
sqlwarning：匹配所有以01开头的sqlstate错误代码；
not found ：匹配所有以02开头的sqlstate错误代码；
sqlexception ：匹配其他非sqlwarning和not found捕获的错误代码；
mysql_error_code ：为数值型错误代码。
（3）sp_statement：自定义错误处理程序，即遇到定义的错误时，MySQL会立即执行自定义错误处理程序中的MySQL语句，自定义错误处理程序也可以是一个begin-end语句块。
【例8.8】 定义条件和处理程序实例
-- 首先建立测试表mytest
mysql> create table mytest(tf1 int,primary key(tf1));
mysql> delimiter //
mysql> create procedure handlermytest()
-> begin
-> declare continue handler for sqlstate '23000' set @x2=1;
-> set @x=1;
-> insert into mytest values(1);
-> set @x=2;
-> insert into mytest values(1);
-> set @x=3;
-> Select @x,@x2;
-> end;
-> //
mysql> delimiter ;
调用存储过程handlermytest()的代码和执行结果如下：
mysql> call handlermytest();
说明：
(1) 定义了异常处理程序后，此时MySQL遇到错误也会按照异常定义那样继续执行；但只有第1条数据被插入到表中，此时用户变量@x=3说明已经执行到了结尾。
(2) 自定义错误触发条件以及自定义错误处理程序可以在触发器、函数以及存储过程中使用。实际软件开发过程中，建议数据库开发人员建立清晰的错误处理规范，必要时可以将自定义错误触发条件、自定义错误处理程序封装在一个存储程序中。
3. 修改存储过程
有两种方法可以修改存储过程，一种方法是删除并重新创建存储过程，这种方法和创建存储过程一样。
另一种是使用alter procedure语句进行修改。使用alter procedure语句修改存储过程的某些参数。
修改存储过程语法格式如下。
alter procedure sp_name [characteristic ...]
【例8.9】修改存储过程do_insert()的定义。将读写权限改为modifies sql data，并指明调用者可以执行。
mysql> alter procedure do_insert
-> modifies sql data
-> sql security invoker;
4. 删除存储过程
删除存储过程可以使用drop procedure语句。
使用drop procedure删除已经存在的存储过程的语法格式如下：
drop procedure [if exists] sp_name
说明：
(1) sp_name是要删除的存储过程的名称。
(2) if exists子句是MySQL的扩展，如果程序或函数不存在，它防止发生错误。
例如，删除存储过程do_update()的代码。
MySQL>drop procedure if exists do_insert；
5. 存储过程与函数的比较
存储过程与函数之间的共同特点
-
存储过程或者函数可以重复使用，可以减少数据库开发人员，尤其是应用程序开发人员的工作量。
-
使用存储过程或者函数可以增强数据的安全访问控制。可以设定只有某些数据库用户才具有某些存储过程或者函数的执行权。
存储过程与函数之间的不同之处
-
函数必须有且仅有一个返回值，且必须指定返回值为字符串、数值2个数据类型。存储过程可以没有返回值，也可以有返回值，甚至可以有多个返回值，所有的返回值需要使用out或者inout参数定义。
-
函数体内可以使用select…into语句为某个变量赋值，但不能使用select语句返回结果集。存储过程则没有这方面的限制，存储过程甚至可以返回多个结果集。
-
函数可以直接嵌入到SQL语句或者MySQL表达式中，最重要的是函数可以用于扩展标准的SQL语句。存储过程一般需要单独调用，并不会嵌入到SQL语句中使用，调用时需要使用call关键字。
-
函数中的函数体限制比较多，比如函数体内不能使用以显式或隐式方式打开、开始或结束事务的语句，如start transaction、commit、rollback或者set autocommit=0等语句；不能在函数体内使用预处理SQL语句。存储过程的限制相对就比较少，基本上所有的SQL语句或MySQL命令都可以在存储过程中使用。
-
Java、PHP等应用程序调用函数时，通常将函数封装到SQL字符串中进行调用；而调用存储过程时，必须使用call关键字进行调用，如果应用程序希望获取存储过程的返回值，应用程序必须给存储过程的out参数或者inout参数传递MySQL会话变量，才能通过该会话变量获取存储过程的返回值。
8.2 游标
8.2.1 利用游标处理结果集
在MySQL数据库中的大部分数据管理操作都与select语句有关。select语句执行后一般会产生包含多条记录的、存放在客户机内存中的结果集。
数据库开发人员编写存储过程或函数等存储程序时，有时需要访问select结果集中的具体数据行，对结果集中的每条记录进行处理。
游标在MySQL中是一种对select语句结果集进行访问的机制。MySQL服务器会专门为游标开辟一定的内存空间，以存放游标操作的结果集数据，同时游标的使用也会根据具体情况对某些数据进行封锁。游标能够实现允许用户访问单独的数据行，而不是只能对整个结果集进行操作。
游标主要包括结果集和游标位置两部分，游标结果集是由定义游标的select语句的结果集，游标位置则是指向这个结果集中的某一行的指针。
游标的使用过程如图所示，可以概括为声明游标、打开游标、从游标中提取数据以及关闭游标。
5.2.2 声明游标
声明游标需要使用declare语句，声明游标的语法格式如下。
declare cursor_name cursor for select_statement;
例如，在teaching数据库中为了teacher表创建一个普通的游标，定义、声明游标teach_cursor的语句如下：
declare teach_cursor cursor for select teacherno,tname from teacher;
使用declare语句声明游标后，此时与该游标对应的select语句并没有执行，MySQL服务器内存中并不存在与select语句对应的结果集。
5.2.3 打开游标
打开游标需要使用open语句，使用游标之前必须首先打开游标，打开游标的语法如下所示。
open cursor_name;
例如，打开前面创建的teach_cursor游标，使用如下语句。
open teach_cursor;
使用open语句打开游标后，与该游标对应的select语句将被执行，MySQL服务器内存中将存放与select语句对应的结果集。
5.2.4 从游标中提取数据
在打开游标以后，就可以从游标中提取数据。从游标中提取数据需要使用fetch语句， fetch语句的功能是获取游标当前指针的记录，并传给指定变量列表。
如果需要提取多行数据，则需要使用循环语句去执行fetch语句，MySQL的游标是向前只读的，即只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。
fetch语句的语法结构如下。
fetch cursor_name into var1[,var2,…];
-
变量名的个数必须与声明游标时使用的select语句结果集中的字段个数保持一致。第一次执行fetch语句时，fetch语句从结果集中提取第一条记录，再次执行fetch语句时，fetch语句从结果集中提取第二条记录，…以此类推。
-
fetch语句每次从结果集中仅仅提取一条记录，因此fetch语句需要循环语句的配合，才能实现整个结果集的遍历。fetch离不开循环语句。一般使用Loop和while比较清楚，而且代码简单。
这里使用Loop为例，代码如下。
fetchLoop:Loop
fetch teach_cursor into v_tno,v_tname;
end Loop;
MySQL是通过一个Error handler的声明来进行判断的。该语句语法格式如下：
declare continue handler for not found …;
-
当使用fetch语句从游标中提取最后一条记录后，再次执行fetch语句时，将产生“ERROR 1329 (02000): No data to fetch”错误信息，数据库开发人员可以针对MySQL错误代码1329，自定义错误处理程序以便结束“结果集”的遍历。
-
游标错误处理程序应该放在声明游标语句之后。游标通常结合错误处理程序一起使用，用于结束结果集的访问。
5.5.5 关闭游标
关闭游标使用close语句，关闭游标的具体语法如下。
close cursor_name;
关闭游标的目的在于释放游标打开时产生的结果集，以通知服务器释放游标所占用的资源，节省MySQL服务器的内存空间。游标如果没有被明确地关闭，游标将在它被声明的begin-end语句块的末尾关闭。
使用声明过的游标不需要再次声明。如果不明确关闭游标，MySQL将会在到达end语句时自动关闭它。
在检索游标teach_cursor后可用如下语句来关闭它。
close teach_cursor;
【例8.10】创建存储过程，利用循环语句控制fetch语句来检索游标teach_cursor中可用的数据的示例。
代码和运行结果如下：
mysql> use teaching;
mysql> delimiter //
mysql> create procedure proc_cursor()
-> begin
-> declare v_tno varchar(6) default ' ';
-> declare v_tname varchar(8) default ' ';
-> declare teach_cursor cuosor for select teacherno, tname from teacher;
-> declare continue handler for not found set @dovar=1; --定义处理程序
-> set @dovar =0;
-> open teach_cursor;
-> fetch_Loop:LOOP
-> ferch teach_cursor into v_tno,v_tname;
-> if @dovar=1 then
-> leave fetch_Loop;
-> else
-> select v_tno,v_tname;
-> end IF;
-> end LOOP fetch_Loop;
-> close teach_cursor;
-> select @dovar;
-> end ;//
mysql> delimiter ;
调用存储过程proc_cursor()的代码和执行结果如下：
mysql> call proc_cursor();
利用declare定义一个句柄，当fetch抓取数据时会自动调用该句柄。如果找不到数据，会自动调用最后的SQL语句set @dovar=1。其中not found等价于 sqlstate '02000'。
本例中，存储过程proc_cursor()的变量@dovar保存的就是fetch操作的结束信息。如果其值为零，则表示有记录检索成功，输出相应的结果；如果值为1，则是fetch语句由于某种原因而操作失败。fetch语句获取数据到结果集最后时，已经没有数据，所以执行处理程序，使得@dovar的值为1。
8.3 触发器
8.3.1 认识触发器
触发器（Trigger）是一种特殊的存储过程，可以是表定义的一部分。触发器基于一个表创建，但可以针对多个表进行操作，所以触发器可以用来对表实施复杂的完整性约束。
当预定义的事件（如用户修改指定表或者视图中的数据时）发生时，触发器被自动激活，从而防止对数据进行不正确的修改。
触发器是一种特殊的存储过程，只要满足一定的条件，对数据进行insert、update和delete事件时，数据库系统就会自动执行触发器中定义的程序语句，以进行维护数据完整性或其他一些特殊的任务。
如图所示，触发器可以分为insert、update和delete等3类，每一类根据执行的先后顺序又可以分成before和arfter触发器。
触发器的优点
(1) 触发器自动执行，在表的数据做了任何修改（比如手工输入或者使用程序采集的操作）之后立即激活。
(2)触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。
(3)触发器可以强制限制，这些限制比用check约束所定义的更复杂。与check约束不同的是，触发器可以引用其他表中的列。
触发器的语法格式
因为触发器是一种特殊的存储过程，所以触发器的创建和存储过程的创建方式有很多相似之处。
创建触发器的语法格式如下：
create trigger trigger_name trigger_time trigger_event
on table_name for each row trigger_statement
说明
(1) create trigger：创建触发器的关键词。触发器程序是与表有关的数据库对象，当表上出现特定事件时，将激活该对象。
(2) table_name：触发程序的相关表。table_name必须引用永久性表。不能将触发程序与temporary表或视图关联起来。
(3) trigger_time：是触发程序的动作时间。它可以是before或after，以指明触发程序是在激活它的语句之前或之后触发。
(4) trigger_event：指明了激活触发程序的语句的类型。不支持在同一个表内同时存在两个有相同激活触发程序的类型。trigger_event可以是下述值之一。
insert：将新行插入表时激活触发程序。例如，通过insert、load data和replace语句。
update：更改某一行时激活触发程序。例如，通过update语句。
delete：从表中删除某一行时激活触发程序。例如，通过delete和replace语句。
(5) for each row：这个声明用来指定受触发事件影响的每一行，都要激活触发器的动作。目前MySQL仅支持行级触发器，不支持语句级别的触发器（例如create table等语句）。for each row表示更新（insert、update或者delete）操作影响的每一条记录都会执行一次触发程序。
(6) trigger_statement：当触发程序激活时执行的语句。如果打算执行多个语句，可使用begin ... end复合语句结构。这样，就能使用存储子程序中允许的相同语句。
(7) 使用触发器时，触发器执行的顺序是before触发器、表数据修改操作、after触发器。其中，before表示在触发事件发生之前执行触发程序，after表示在触发事件发生之后执行触发器。因此严格意义上讲一个数据库表最多可以设置6种类型的触发器。
触发程序中可以使用old关键字与new关键字
触发程序中可以使用的所谓old关键字与new关键字，实际上是在触发器事件发生时，MySQL针对要修改数据的表，创建了与本表结构完全一样2个的临时表old和new，old表用于存放在数据修改过程中既有数据，new表用于存放在数据修改过程中将要更新的数据。
当向表插入新记录时，在触发程序中可以利用new关键字访问新记录，当需要访问新记录的某个字段值时，可以使用“new.字段名”的方式访问。
当从表中删除旧记录时，在触发程序中可以利用old关键字访问旧记录，当需要访问旧记录的某个字段值时，可以使用“old.字段名”的方式访问。
当修改表的某条记录时，在触发程序中可以使用old关键字访问修改前的旧记录、使用new关键字访问修改后的新记录。当需要访问旧记录的某个字段值时，可以使用“old.字段名”的方式访问。当需要访问修改后的新记录的某个字段值时，可以使用“new.字段名”的方式访问。
old记录是只读的，只能引用，不能更改。在before触发程序中，可使用“set new.col_name = value”语句更改new记录的值。
对于insert语句，只有new是合法的；对于delete语句，只有old才合法；而update语句可以与new或old同时使用。
8.3.2 触发器的创建和管理
1. 触发器的创建和验证
触发器是由insert、update和delete等事件来触发某种特定操作。满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。
【例8.11】创建一个触发器，当更改表course中某门课的课程号时，同时将score表课程号全部更新。
mysql> use teaching;
mysql> delimiter $$
mysql> create trigger cno_update after update
-> on course for each row
-> begin
-> update score set courseno=new.courseno
-> where courseno=old.courseno;
-> end $$
mysql> delimiter ;
验证触发器cno_update的功能，代码和执行结果如下。
mysql> update course set courseno =‘c07123’ where courseno=‘c08123’;
mysql> select * from score where courseno ='c07123';
说明：
(1) 在本例中，update course是触发事件，after是触发程序的动作时间，激发触发器update score表相应记录。使用select语句查看score表中的情况，发现所有原c08123课程编号的记录已更新为c07123。
(2) 在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称标识，那会使系统混淆。
(3) 在本例中，new和old同时使用。当在course表更新courseno,原来的courseno变为old. courseno，把score表old. courseno的记录要更新为new. courseno。
【例8.12】在teacher表中，定义一个触发器，当一个教师的信息被删除时，把该教师的编号和姓名添加到de_teacher表中。
-- 创建一个空表de_teacher,表由tno和tname两列组成。
mysql> create table de_teacher select teacherno,tname
-> from teacher where 1=0;
-- 创建teacher表的触发器
mysql> create trigger trig_teacher
-> after delete on teacher for each row
-> insert into de_teacher(teacherno,tname)
-> values(old.teacherno, old.tname);
验证触发器trig_teacher 的功能，代码和执行结果如下。
mysql> delete from teacher where tname=‘时观’;
mysql> select * from de_teacher;
2. 查看触发器的定义
既然触发器是一类特殊的存储过程，那么查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等，也可以通过类似的命令来完成。用户可以通过show triggers语句来查看触发器的状态。用户也可以通过查询information_schema数据库下的triggers表来查看触发器的信息。下面给出已经验证过的查看触发器的状态和定义的方法的例子。
mysql> show triggers;
mysql> select * from information_schema.triggers;
mysql> select * from information_schema.triggers
-> where trigger_name='de_teacher';
5.3.3 使用触发器
MySQL中的触发器在程序设计的应用非常广泛，常见的有实现数据完成性的复杂约束、数据管理过程中的冗余数据处理以及外键约束的级联操作等，都可以利用触发器实现应用系统的自动维护。
1. 触发器应用举例
对于InnoDB存储引擎的表而言，由于支持外键约束，在定义外键约束时，通过设置外键的级联选项cascade、set null或者no action（restrict），外键约束关系可以交由InnoDB存储引擎自动维护。
【例8.13】创建一个触发器，当删除student表某个人的记录时，删除score表相应的成绩记录。
mysql> delimiter $$
mysql> create trigger stu_delete after delete
-> on student for each row
-> begin
-> delete from score where studentno=old.studentno;
-> end $$
mysql> delimiter ;
验证触发器stu_delete的功能，代码和执行结果如下。
mysql> delete from student where studentno=‘19112100072’;
mysql> select * from score where studentno='19112100072‘;
说明：
(1) 在本例中，使用select语句查看score表中的情况，可以看到已没有19112100072学生的成绩记录。
(2)本例中，在student执行delete事件之后，在触发器中引用的score表的studentno字段要用old. studentno表示。
【例8.14】在de_teacher表上创建before insert和after insert这两个触发器。在向department表中插入数据时，观察这两个触发器的触发顺序。
mysql> create table bef_after select teacherno,tname
-> from teacher where 1=0;
mysql> alter table bef_after
-> add tig_time timestamp not NULL DEFAULT NOW();
mysql> create trigger before_insert before insert
-> on de_teacher for each row
-> insert into bef_after
-> set teacherno =‘t11111’, tname =‘卫小林’;
mysql> create trigger after_insert after insert
-> on de_teacher for each row
-> insert into bef_after
-> set teacherno =‘t22222’, tname =‘泰小林’;
验证触发器before_insert和after_insert 的功能，代码和执行结果如下。
mysql> insert into de_teacher values(‘t12345’, ‘王含晨’);
mysql> select * from bef_after;
说明：
MySQL中，触发器执行的顺序是before触发器、表操作（insert、update 和delete）、after触发器。本例由于程序较短，运行速度快，虽然记录的时间在1秒之内完成，但记录的插入顺序可以说明before触发器的执行早于after触发器。
2. 使用触发器的注意事项
-
触发程序中如果包含select语句，该select语句不能返回结果集。
-
同一个表不能创建两个相同触发时间、触发事件的触发程序。
-
触发程序中不能使用以显式或隐式方式打开、开始或结束事务的语句，如start transaction、commit、rollback或者set autocommit=0等语句。
-
MySQL触发器针对记录进行操作，当批量更新数据时，引入触发器会导致更新操作性能降低。
-
在MyISAM存储引擎中，触发器不能保证原子性。InnoDB存储引擎支持事务，使用触发器可以保证更新操作与触发程序的原子性，此时触发程序和更新操作是在同一个事务中完成。
-
InnoDB存储引擎实现外键约束关系时，建议使用级联选项维护外键数据；MyISAM存储引擎虽然不支持外键约束关系时，但可以使用触发器实现级联修改和级联删除，进而维护“外键”数据，模拟实现外键约束关系。
-
使用触发器维护InnoDB外键约束的级联选项时，数据库开发人员究竟应该选择after触发器还是before触发器？答案是：应该首先维护子表的数据，然后再维护父表的数据，否则可能出现错误。
-
MySQL的触发程序不能对本表进行更新语句（例如update语句）。触发程序中的更新操作可以直接使用set命令替代，否则可能出现错误信息，甚至陷入死循环。
-
在before触发程序中，auto_increment字段的new值为0，不是实际插入新记录时自动生成的自增型字段值。
-
添加触发器后，建议对其进行详细的测试，测试通过后再决定是否使用触发器。
5.3.4 删除触发器
删除触发器指删除数据库中已经存在的触发器。
MySQL使用drop trigger语句来删除触发器。其基本形式如下。
drop trigger [schema_name.]trigger_name
例如，删除触发器stu_score的代码如下：
mysql> drop trigger stu_score;
8.4 事件及其应用
8.4.1 认识事件
MySQL中的事件（Event）又称事件调度器(Event Scheduler) 是一种定时任务机制，可以用于定时执行诸如删除记录、对数据进行汇总等某些特定任务，来取代原先只能由操作系统的计划任务来执行的工作。
MySQL的事件调度器可以精确到每秒钟执行一个任务，比操作系统的计划任务（如：Linux下的cron或Windows下的任务计划），只能精确到每分钟执行一次有实时优势。对于一些对数据实时性要求比较高的应用，如股票交易、火车购票、球赛技术统计等就非常适合。一些对数据管理的定时性操作不再依赖外部程序，直接使用数据库本身提供的功能即可。
1. 开启事件调度器
MySQL的事件调度器是MySQL数据库服务器的一部分，负责调用事件，并不断地监视一个事件是否需要调用。要创建事件，必须打开调度器。可以使用系统变量@@event_scheduler来打开事件调度器，true（或1或on）为打开，false（或0或off）为关闭。
要开启event_scheduler，可执行下面的语句。
set @@global.event_scheduler = true;
也可以在MySQL的配置文件my.ini中加上一行，然后重启MySQL服务器。
event_scheduler = 1
2. 查看事件调度器
要查看当前是否已开启事件调度器，可执行如下相关SQL语句。
mysql> set @@global.event_scheduler = true;
mysql> show variables like ‘event_scheduler’;
mysql> select @@ event_scheduler;
8.4.2 创建事件
创建事件可以创建在某一时刻发生的事件、指定区间周期性发生的事件，以及在事件中调用存储过程或存储函数的实际应用。
创建事件的一般格式。创建事件可以使用create event语句。语法格式如下。
create event [if not exists] event_name
on schedule schedule
[on completion [not] preserve]
[enable|disable|disable on slave] [comment'comment']
do sql_statement;
其中，
schedule：at timestamp [+interval interval]
|every interval[starts timestamp [+ interval interval]]
[ends timestamp[+ interval interval]]
interval：count { year|quarter|month|day|hour|minute
|week|second|year_month|day_hour|day_minute
|day_second|hour_minute|hour_second|minute_second}
说明：
(1) event_name：表示事件名。
(2) schedule：是时间调度，表示事件何时发生或者每隔多久发生一次。
at子句：表示事件在某个时刻发生。timestamp表示一个具体的时间点，后面还可以加上一个时间间隔，表示在这个时间间隔后事件发生。interval表示这个时间间隔，由一个数值和单位构成，count是间隔时间的数值。
every子句：表示在指定时间区间内每隔多长时间事件发生一次。starts子句指定开始时间，ends子句指定结束时间。
(3) do sql_statement：事件启动时执行的SQL代码。如果包含多条语句，可以使用begin…end复合结构。
1. 创建某个时刻发生的事件
【例8.14】创建现在立刻执行的事件direct1，创建一个表test1。
mysql> use mysqltest;
mysql> create event direct1
-> on schedule at now()
-> do
-> create table test1(timeline timestamp);
mysql> show tables;
mysql> select * from test1;
【例8.15】创建现在立刻执行的事件direct2，5秒后创建一个表test2。
mysql> create event direct2
-> on schedule at current_timestamp +interval 5 second
-> do
-> create table test2(timeline timestamp);
2. 创建在指定区间周期性发生的事件
【例8.16】创建事件test1_insert，每秒插入一条记录到数据表test1。
mysql> create event test1_insert
-> on schedule every 1 second
-> do
-> insert into test1 values (current_timestamp);
mysql> select * from test1; -- 5秒之后执行此语句
【例8.17】创建事件startweeks，要求从下周开始，每个周都清空test1表，并且在2017年的08月31日12:00时结束。
mysql> delimiter $$
mysql> create event startweeks
-> on schedule every 1 week
-> starts curdate()+interval 1 week
-> ends '2017-08-31 12:00:00'
-> do
-> begin
-> truncate table test1;
-> end $$
mysql> delimiter;
3. 在事件中调用存储过程或存储函数
【例8.18】存储过程proc_stu()用于来查询学生信息情况的，创建事件stu_week每周查看一次学生的情况。
mysql> delimiter $$
mysql> create event stu_week
-> on schedule every 1 week
-> do
-> begin
-> call teaching.proc_stu();
-> end $$
mysql> delimiter;
8.4.3 管理事件
1. 查看事件
(1) MySQL中查看所有事件event的语法如下。
show events [from schema_name]
[like 'pattern’|where expr]
可以直接利用命令“show events; ”查看数据库mysqltest 中的事件。为了直观一些，采用如下的方法查看。
【例8.19】格式化显示所有事件event。
mysql> show events\G
(2) MySQL中查看event的创建信息的语法如下。
mysql> show create event event_name;
例如，查看stu_week的创建信息的代码如下：
mysql> show create event stu_week;
2. 修改事件
MySQL中可以通过alter event语句来修改事件的定义和相关属性。
具体修改格式如下：
alter event event_name
[on schedule schedule]
[rename to new_event_name]on completion[not]preserve]
[comment'comment'][enable|disable][do sql_statement]
例如，可以临时关闭事件或再次让它活动，修改事件的名称并加上注释等。
【例8.20】 对事件test1_insert进行操作如下。临时关闭test1_insert事件；开启test1_insert事件，将每天清空test1表改为7天清空一次；重命名事件test1_insert并加上注释。
mysql> alter event test1_insert disable;
mysql> alter event test1_insert enable;
mysql> alter event test1_insert on schedule every 7 day;
mysql> alter event test1_insert
-> rename to insert_test1 comment '表test1的数据操作‘;
3. 删除事件
MySQL中用drop event删除事件。删除事件的语法格式如下。
drop event [if exists][database name.]event_name
例如，删除事件insert_test1的代码如下。
mysql>drop event insert_test1；
