第10章 存储过程和触发器

1、第第10章章 存储过程、触发器存储过程、触发器n存储过程、触发器和游标是存储过程、触发器和游标是SQL ServerSQL Server数据库的三个重要组成数据库的三个重要组成部分。部分。SQL Server 2008SQL Server 2008使用它们从不同方面提高数据处理能使用它们从不同方面提高数据处理能力。力。n在在SQL Server 2008SQL Server 2008中，可以像其他程序设计语言一样定义子中，可以像其他程序设计语言一样定义子程序，称为存储过程程序，称为存储过程。运。运用用它可它可以创建健壮、安全且具有良好以创建健壮、安全且具有良好性能的数据库，可以为用户实性能的数

2、据库，可以为用户实现复现复杂的商业事务。杂的商业事务。n触发器是一种特殊类型的存储过程：它通过事件触发而被自动触发器是一种特殊类型的存储过程：它通过事件触发而被自动执行。自动执行意味着更少的手工操作以及更小的出错机率。执行。自动执行意味着更少的手工操作以及更小的出错机率。触发器用于强制复杂的完整性检查，审核更改，维护不规范的触发器用于强制复杂的完整性检查，审核更改，维护不规范的数据等等。数据等等。SQL Server 2008SQL Server 2008允许允许DMLDML语句和语句和DDLDDL语句创建触发语句创建触发器，可以引发器，可以引发AFTERAFTER或者或者INSTEAD OF

3、INSTEAD OF触发事件。触发事件。n游标主要用于实现一些不能使用面向集合的语句实现的操作。游标主要用于实现一些不能使用面向集合的语句实现的操作。通过游标，通过游标，SQL ServerSQL Server提供了一个对结果集进行逐行处理的能提供了一个对结果集进行逐行处理的能力。可以把游标看为一种特殊的指针，它可以指向结果集中的力。可以把游标看为一种特殊的指针，它可以指向结果集中的任意位置，在查询数据的同时对数据进行处理。任意位置，在查询数据的同时对数据进行处理。本章学习目标：本章学习目标：n了解存储过程、触发器和游标的基本概念与特点了解存储过程、触发器和游标的基本概念与特点n掌握存储过程的

4、基本类型和相关操作掌握存储过程的基本类型和相关操作n掌握触发器的类型与相关操作掌握触发器的类型与相关操作10.1 10.1 存储过程存储过程n存储过程是一组完成特定功能的存储过程是一组完成特定功能的SQL SQL 语句集合，经编语句集合，经编译后存储在数据库中。译后存储在数据库中。n存储过程作为一个单元进行处理并以一个名称来标识存储过程作为一个单元进行处理并以一个名称来标识。它能向用户返回数据、向数据库表中写入或修改数。它能向用户返回数据、向数据库表中写入或修改数据、执行系统函数和进行管理操作。据、执行系统函数和进行管理操作。n用户通过指定存储过程的名字并给出参数（如果该存用户通过指定存储过程

5、的名字并给出参数（如果该存储过程带有参数）来执行它。储过程带有参数）来执行它。存储过程的优点：存储过程的优点：n1. 1. 减少网络流量：减少网络流量：存储过程在数据库服务器端执行，只向客存储过程在数据库服务器端执行，只向客户端返回执行结果。因此可以将在网络中要发送的数百行代码，户端返回执行结果。因此可以将在网络中要发送的数百行代码，编写为一条存储过程，这样客户端只需要提交存储过程的名称编写为一条存储过程，这样客户端只需要提交存储过程的名称和参数，即可实现相应功能，节省了网络流量，提高了执行的和参数，即可实现相应功能，节省了网络流量，提高了执行的效率。此外，由于所有的操作都在服务器端完成，避免

6、了在客效率。此外，由于所有的操作都在服务器端完成，避免了在客户端和服务器端之间的多次往返。存储过程只需要将最终结果户端和服务器端之间的多次往返。存储过程只需要将最终结果通过网络传输到客户端。通过网络传输到客户端。n2. 2. 提高系统性能提高系统性能：一般：一般T-SQLT-SQL语句每执行一次就需要编译一次，语句每执行一次就需要编译一次，而存储过程只在创建时进行编译，被编译后存放在数据库服务而存储过程只在创建时进行编译，被编译后存放在数据库服务器的过程高速缓存中，当使用时，服务器不必再重新分析和编器的过程高速缓存中，当使用时，服务器不必再重新分析和编译它们。因此，当对数据库进行复杂操作时（如

7、对多个表进行译它们。因此，当对数据库进行复杂操作时（如对多个表进行UPDATEUPDATE、INSERTINSERT或或DELETEDELETE操作时），可将这些复杂操作用存储操作时），可将这些复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用，节省了过程封装起来与数据库提供的事务处理结合一起使用，节省了分析、解析和优化代码所需的分析、解析和优化代码所需的CPUCPU资源和时间。资源和时间。存存储过程的优点：储过程的优点：n3. 3. 安全性高安全性高：使用存储过程可以完成所有数据库操作，并且：使用存储过程可以完成所有数据库操作，并且可授予没有直接执行存储过程中语句的权限的用户，也可

8、执可授予没有直接执行存储过程中语句的权限的用户，也可执行该存储过程的权限。另外可以防止用户直接访问表，强制行该存储过程的权限。另外可以防止用户直接访问表，强制用户使用存储过程执行特定的任务。用户使用存储过程执行特定的任务。n4. 4. 可重用性：可重用性：存储过程只需创建并存储在数据库中，以后即存储过程只需创建并存储在数据库中，以后即可任意在程序中调用该过程。存储过程可独立于程序源代码可任意在程序中调用该过程。存储过程可独立于程序源代码而单独修改，减少数据库开发人员的工作量。而单独修改，减少数据库开发人员的工作量。n5 5. . 可自动完成需要预先执行的任务可自动完成需要预先执行的任务：存储过

9、程可以在系统启：存储过程可以在系统启动时自动执行，完成一些需要预先执行的任务，而不必在系动时自动执行，完成一些需要预先执行的任务，而不必在系统启动后再进行人工操作。统启动后再进行人工操作。存储过程的类型存储过程的类型1. 1. 系统存储过程系统存储过程 系统存储过程是系统已定义好的完成特定功能的存储过系统存储过程是系统已定义好的完成特定功能的存储过程，用户可直接调用。系统存储过程以程，用户可直接调用。系统存储过程以sp_sp_为前缀。为前缀。2. 2. 用户自定义存储过程用户自定义存储过程 用户自定义存储过程在用户数据库中创建，通常与数据用户自定义存储过程在用户数据库中创建，通常与数据库对象进

10、行交互，用于完成特定数据库操作任务，可以库对象进行交互，用于完成特定数据库操作任务，可以接受和返回用户提供的参数，名称不能以接受和返回用户提供的参数，名称不能以sp_sp_为前缀。为前缀。n1. 1. 使用使用SQL Server Management StudioSQL Server Management Studio创建存储创建存储过程过程n2.2.使用使用CREATE PROCEDURECREATE PROCEDURE语句创建存储过程，语语句创建存储过程，语法格式为法格式为：CREATE PROCEDURE procedure_name parameter data_type = def

11、aultOUTPUT ,n AS sql_statement n 110.1.1 10.1.1 创建存储过程创建存储过程 其中：其中：qparameterparameter：过程中的参数。在该语句中可以声明过程中的参数。在该语句中可以声明一个或多个参数。用户必须在执行过程时提供每个一个或多个参数。用户必须在执行过程时提供每个所声明参数的值，使用所声明参数的值，使用 符号作为第一个字符来指符号作为第一个字符来指定参数名称。定参数名称。 qdata_typedata_type：参数的数据类型。参数的数据类型。qD Defaultefault：参数的默认值。参数的默认值。qOUTPUTOUTPUT：

12、表明参数是返回参数。表明参数是返回参数。【例例】 创建一个存储过程创建一个存储过程stud_degreestud_degree，用于检索所有学生的，用于检索所有学生的成绩记录，包括学号、姓名、课程名和分数。成绩记录，包括学号、姓名、课程名和分数。解：对应的程序如下：解：对应的程序如下：CREATE PROCEDURE CREATE PROCEDURE stud_degreestud_degree ASAS SELECT SELECT student.student.学号学号, ,姓名姓名, ,课程名课程名, ,分数分数 FROM FROM student,course,scorestudent

13、,course,score WHERE student. WHERE student.学号学号=score.=score.学号学号 AND course. AND course.课程号课程号=score.=score.课程号课程号 ORDER BY student.ORDER BY student.学号学号n执行存储过程使用执行存储过程使用EXECUTEEXECUTE语句，其格式如下：语句，其格式如下： EXEC UTE return_status = procedure_name parameter = value | variable OUTPUT | DEFAULT , n 10.1.2

14、 10.1.2 执行存储过程执行存储过程 q各个参数的含义：各个参数的含义：l return_statusreturn_status 保存存储过程的返回状态保存存储过程的返回状态lprocedure_nameprocedure_name 调用的存储过程的名称调用的存储过程的名称lparameter parameter 过程参数过程参数lValue Value 过程参数的值过程参数的值lvariable variable 用来保存参数或返回参数的变量用来保存参数或返回参数的变量lOUTPUTOUTPUT 指定存储过程必须返回的一个参数指定存储过程必须返回的一个参数lDEFAULT DEFAULT

15、 默认参数值默认参数值n在调用存储过程时的两种传递参数的方式：在调用存储过程时的两种传递参数的方式：q方式一：方式一： EXEC EXEC 存储过程名存储过程名 实参列表实参列表q方式二：方式二： EXEC EXEC 存储过程名存储过程名 参数参数1=1=值值1,1,参数参数2=2=值值2,2,【例例】 执行存储过程执行存储过程maxdegreemaxdegree并查看输出的结果。并查看输出的结果。 create procedure maxderee create procedure maxderee as as begin begin select max(degree) as select

16、 max(degree) as 最高分最高分 from score from score end end执行执行maxdegreemaxdegree存储过程的程序如下：存储过程的程序如下：EXEC maxdegreeEXEC maxdegree其执行结果如图所示：其执行结果如图所示： n1. 1. 使用参数使用参数带参数的存储过程的一般格式如下：带参数的存储过程的一般格式如下：CREATE PROCEDURE CREATE PROCEDURE 存储过程名存储过程名( ( 参数列表参数列表 ) )AS SQLAS SQL语句语句10.1.3 10.1.3 存储过程的参数存储过程的参数【例例】 设

17、计一个存储过程设计一个存储过程maxnomaxno，以学号为参数，输出指定，以学号为参数，输出指定学号学生的所有课程中最高分和对应的课程名及成绩。学号学生的所有课程中最高分和对应的课程名及成绩。解：解：CREATE PROCEDURE CREATE PROCEDURE maxnomaxno(no char(10) (no char(10) ASAS SELECT s. SELECT s.学号学号,s.,s.姓名姓名,c.,c.课程名课程名,sc.,sc.分数分数 FROM student FROM student s,courses,course c,scorec,score scsc WHE

18、RE s. WHERE s.学号学号=no AND s.=no AND s.学号学号=sc.=sc.学号学号 AND c. AND c.课程号课程号=sc.=sc.课程号课程号 AND sc. AND sc.分数分数= = (SELECT MAX( (SELECT MAX(分数分数) FROM score WHERE ) FROM score WHERE 学号学号=no)=no)GOGO采用方式一执行存储过程采用方式一执行存储过程maxnomaxno的程序如下：的程序如下：EXEC EXEC maxnomaxno 103 103采用方式二执行存储过程采用方式二执行存储过程maxnomaxno的

19、程序如下：的程序如下：EXEC EXEC maxnomaxno no=103 no=103n2. 2. 使用默认参数使用默认参数q在设计存储过程时，可以为参数提供一个默认值，在设计存储过程时，可以为参数提供一个默认值，默认值必须为常量或者默认值必须为常量或者NULLNULL。其一般格式如下：。其一般格式如下：CREATE PROCEDURE CREATE PROCEDURE 存储过程名存储过程名( ( 参数参数1=1=默认值默认值1, 1, 参参数数2=2=默认值默认值2, )2, )AS SQLAS SQL语句语句q在调用存储过程时，如果不指定对应的实参值，则在调用存储过程时，如果不指定对应

20、的实参值，则自动用对应的默认值代替。自动用对应的默认值代替。【例例】 设计一个存储过程设计一个存储过程maxnomaxno，以学号为参数，输出指定，以学号为参数，输出指定学号学生的所有课程中最高分和对应的课程名，学号默认值为学号学生的所有课程中最高分和对应的课程名，学号默认值为101101。解：解：CREATE PROCEDURE maxno1(no CREATE PROCEDURE maxno1(no intint=101) =101) AS AS SELECT s. SELECT s.学号学号,s.,s.姓名姓名,c.,c.课程名课程名,sc.,sc.分数分数 FROM student F

21、ROM student s,courses,course c,scorec,score scsc WHERE s. WHERE s.学号学号=no AND s.=no AND s.学号学号=sc.=sc.学号学号 AND c. AND c.课程号课程号=sc.=sc.课程号课程号 AND sc. AND sc.分数分数= = (SELECT MAX( (SELECT MAX(分数分数) FROM score WHERE ) FROM score WHERE 学号学号=no)=no)当不指定实参调用当不指定实参调用maxno1maxno1存储过程时，其结果如图所示：存储过程时，其结果如图所示：

22、当指定实参为当指定实参为105105调用调用maxno1maxno1存储过程时，其结果如图所：存储过程时，其结果如图所：n3. 3. 使用返回参数使用返回参数 在创建存储过程时，可以定义返回参数。在执在创建存储过程时，可以定义返回参数。在执行存储过程时，可以将结果给返回参数。返回参行存储过程时，可以将结果给返回参数。返回参数用数用OUTPUTOUTPUT进行说明。进行说明。【例例】 创建一个存储过程创建一个存储过程averageaverage，它返回两个参数，它返回两个参数st_namest_name和和st_avgst_avg，分别代表了姓名和平均分。并编写，分别代表了姓名和平均分。并编写T

23、-T-SQLSQL语句执行该存储过程和查看输出的结果。语句执行该存储过程和查看输出的结果。解：建立存储过程解：建立存储过程averageaverage的程序如下：的程序如下：CREATE PROCEDURE averageCREATE PROCEDURE average( ( st_nost_no intint, , st_namest_name char(8) char(8) OUTPUTOUTPUT, , st_avgst_avg float float OUTPUTOUTPUT) AS) AS SELECT SELECT st_namest_name=student.=student.姓

24、名姓名,st_avgst_avg=AVG(score.=AVG(score.分数分数) ) FROM FROM student,scorestudent,score WHERE student. WHERE student.学号学号=score.=score.学号学号 GROUP BY student.GROUP BY student.学号学号,student.,student.姓名姓名 HAVING student.HAVING student.学号学号=st_nost_no执行该存储过程，来查询学号为执行该存储过程，来查询学号为“105”105”的学生姓名和平均分：的学生姓名和平均分：DE

25、CLARE DECLARE st_namest_name char(10) char(10)DECLARE DECLARE st_avgst_avg float floatEXEC average 105,st_name EXEC average 105,st_name OUTPUTOUTPUT, , st_avgst_avg OUTPUTOUTPUTSELECT SELECT 姓名姓名=st_namest_name,平均分平均分=st_avgst_avgn4. 4. 存储过程的返回值存储过程的返回值q存储过程在执行后都会返回一个整型值（称为存储过程在执行后都会返回一个整型值（称为“返返回代码

26、回代码”），指示存储过程的执行状态。），指示存储过程的执行状态。q如果执行成功，返回如果执行成功，返回0 0；否则返回；否则返回-1-1-99-99之间的数之间的数值（例如值（例如-1-1表示找不到对象，表示找不到对象，-2-2表示数据类型错误表示数据类型错误，-5-5表示语法错误等）。表示语法错误等）。q也可以使用也可以使用RETURNRETURN语句指定一个返回值。语句指定一个返回值。【例例】 编写一个程序，创建存储过程编写一个程序，创建存储过程test_rettest_ret，根据输入，根据输入的参数来判断返回值。的参数来判断返回值。解：建立存储过程解：建立存储过程test_rettes

27、t_ret如下：如下：CREATE PROC ret(id int = 0)CREATE PROC ret(id int = 0) IF id=0 IF id=0 RETURN 0 RETURN 0 IF id0 IF id0 RETURN 1000 RETURN 1000 IF id0 IF id0 RETURN -1000 RETURN -1000 执行以上存储过程：执行以上存储过程：declare r intdeclare r intexec r=ret 1exec r=ret 1print rprint r运行结果：运行结果：10.1.4 10.1.4 查看、修改和删除存储过程查看、修

28、改和删除存储过程n使用使用SQL ServerSQL Server管理控制器查看或修改存储过管理控制器查看或修改存储过程程n使用使用sp_helptextsp_helptext存储过程来查看存储过程的定存储过程来查看存储过程的定义信息义信息n使用使用SQL ServerSQL Server管理控制器删除存储过程管理控制器删除存储过程n使用使用DROP PROCEDUREDROP PROCEDURE删除存储过程删除存储过程【例例】 使用相关系统存储过程查看存储过程使用相关系统存储过程查看存储过程stud_degreestud_degree的的相关内容。相关内容。解：对应的程序如下：解：对应的程序

29、如下：USE schoolUSE schoolGOGOEXEC sp_helptext stud_degreeEXEC sp_helptext stud_degree10.2 10.2 触发器触发器n触发器是一种特殊类型的存储过程。触发器可包含复触发器是一种特殊类型的存储过程。触发器可包含复杂的杂的T-SQLT-SQL语句。触发器不能通过名称被直接调用，语句。触发器不能通过名称被直接调用，也不允许设置参数。它是在插入、删除和修改指定表也不允许设置参数。它是在插入、删除和修改指定表中的数据时触发执行。中的数据时触发执行。n触发器可以强制执行一定的业务规则，以保持数据完触发器可以强制执行一定的业务

30、规则，以保持数据完整性、检查数据有效性、实现数据库管理任务和一些整性、检查数据有效性、实现数据库管理任务和一些附加功能。附加功能。n对于数据库中约束所不能保证的复杂的参照完整性和对于数据库中约束所不能保证的复杂的参照完整性和数据的一致性可使用触发器来实现。数据的一致性可使用触发器来实现。10.2.1 10.2.1 触发器概述触发器概述1. 1. 触发器的功能触发器的功能q在在SQL ServerSQL Server内部，触发器被看作是存储过程，它与存内部，触发器被看作是存储过程，它与存储过程所经历的处理过程类似。但是触发器没有输入参储过程所经历的处理过程类似。但是触发器没有输入参数和输出参数，

31、因而不能被显示调用。它作为语句的执数和输出参数，因而不能被显示调用。它作为语句的执行结果自动引发，而存储过程则是通过存储过程名称而行结果自动引发，而存储过程则是通过存储过程名称而被直接调用。被直接调用。q触发器与表紧密相连，当用户对表进行诸如触发器与表紧密相连，当用户对表进行诸如UPDATEUPDATE、INSERTINSERT和和DELETEDELETE这些操作时，系统会自动执行触发器所这些操作时，系统会自动执行触发器所定义的定义的SQLSQL语句，从而确保对数据的处理符合由这些语句，从而确保对数据的处理符合由这些SQL SQL 语句所定义的规则。语句所定义的规则。 q强化约束：触发器能够实

32、现比强化约束：触发器能够实现比CHECK CHECK 语句更为复杂的约束：语句更为复杂的约束：l触发器可以很方便地引用其他表的列，去进行逻辑上的触发器可以很方便地引用其他表的列，去进行逻辑上的检查；检查；l触发器是在触发器是在CHECKCHECK之后执行的；之后执行的；l触发器可以插入，删除，更新多行。触发器可以插入，删除，更新多行。q跟踪变化：触发器可以侦测数据库内的操作从而禁止数据跟踪变化：触发器可以侦测数据库内的操作从而禁止数据库中未经许可的更新和变化，确保输入表中的数据的有效库中未经许可的更新和变化，确保输入表中的数据的有效性。例如在库存系统中，触发器可以检测到当实际库存下性。例如在库

33、存系统中，触发器可以检测到当实际库存下降到了需要再进货的临界量，就给出管理员相应提示信息降到了需要再进货的临界量，就给出管理员相应提示信息或自动生成给供应商的订单；或自动生成给供应商的订单；q级联运行：触发器可以侦测数据库内的操作，并自动地级级联运行：触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的不同表中的各项内容。如：设置一个联影响整个数据库的不同表中的各项内容。如：设置一个触发器，当触发器，当studentstudent表中删除一个学号信息时，对应的表中删除一个学号信息时，对应的scorescore表中相应的学号信息也被删除；表中相应的学号信息也被删除；q调用存储过程：为了响应

34、数据库更新，触发器可以调用一调用存储过程：为了响应数据库更新，触发器可以调用一个或多个存储过程。个或多个存储过程。2. 2. 触发器的种类触发器的种类nSQL Server 2008SQL Server 2008支持两种类型的触发器：支持两种类型的触发器：DMLDML触发器和触发器和DDLDDL触发器。触发器。qDMLDML触发器触发器：如果用户要通过数据操作语言：如果用户要通过数据操作语言 (DML)(DML)编辑编辑数据，则执行数据，则执行 DML DML 触发器。触发器。DML DML 事件是针对表或视图事件是针对表或视图的的 INSERTINSERT、UPDATEUPDATE和和DEL

35、ETE DELETE 语句，即语句，即DMLDML触发器在数触发器在数据修改时被执行。系统将触发器和触发它的语句作为可据修改时被执行。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务自动回滚；如，磁盘空间不足），则整个事务自动回滚；qDDLDDL触发器：触发器：为了响应各种数据定义语言为了响应各种数据定义语言 (DDL) (DDL) 事件而事件而激发。激发。DDLDDL事件主要与以关键字事件主要与以关键字 CREATECREATE、ALTER ALTER 和和 DROP DROP 开头

36、的开头的 T-SQL T-SQL 语句对应。它们可以用于在数据库语句对应。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。中执行管理任务，例如，审核以及规范数据库操作。10.2.2 DML10.2.2 DML触发器的创建和应用触发器的创建和应用n1. DML1. DML触发器的分类触发器的分类qAFTERAFTER触发器：触发器：这类触发器是在记录已经被改变完，相这类触发器是在记录已经被改变完，相关事务提交后，才会被触发执行。主要是用于记录变更关事务提交后，才会被触发执行。主要是用于记录变更后的处理或检查，一旦发现错误，可以用后的处理或检查，一旦发现错误，可以用ROLLBAC

37、K ROLLBACK TRANSACTIONTRANSACTION语句来回滚本次的操作。对同一个表达操语句来回滚本次的操作。对同一个表达操作，可定义多个作，可定义多个AFTERAFTER触发器，并定义各种触发器执行触发器，并定义各种触发器执行的先后顺序。的先后顺序。qINSTEAD OFINSTEAD OF触发器：触发器：这类触发器并不去执行其所定义的这类触发器并不去执行其所定义的操作（操作（INSERTINSERT、UPDATEUPDATE、DELETEDELETE），而去执行触发器本），而去执行触发器本身所定义的操作。这类触发器一般是用来取代原本的操身所定义的操作。这类触发器一般是用来取代

38、原本的操作，在记录变更之前被触发的。作，在记录变更之前被触发的。n2. 2. 触发器中的逻辑（虚拟）表触发器中的逻辑（虚拟）表q当表被修改，无论是插入、修改还是删除，被操作的记当表被修改，无论是插入、修改还是删除，被操作的记录会保存在两个系统的逻辑表中，这两个逻辑表是录会保存在两个系统的逻辑表中，这两个逻辑表是insertedinserted（插入）表和（插入）表和deleteddeleted（删除）表。（删除）表。q这两个表是建在数据库服务器的内存中的，是由系统管这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对理的逻辑表，而不是真正存储在数据库

39、中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。于这两个表，用户只有读取的权限，没有修改的权限。当触发器的工作完成之后，这两个表将会从内存中删除。当触发器的工作完成之后，这两个表将会从内存中删除。qinsertedinserted表里存放的是新插入的记录：对于表里存放的是新插入的记录：对于INSERTINSERT操作来说，操作来说，INSERTINSERT触发器执行，新的记录插入到触发器表和触发器执行，新的记录插入到触发器表和insertedinserted表表中。在进行中。在进行INSERTINSERT和和UPDATEUPDATE触发器时，触发器时，insertedinsert

40、ed表中有数表中有数据，而在据，而在DELETEDELETE触发器中触发器中insertedinserted表是空的。表是空的。qDeletedDeleted表里存放的是已从表中删除的记录：对于表里存放的是已从表中删除的记录：对于DELETEDELETE操操作来说，作来说，DELETEDELETE触发器执行，被删除的旧记录存放到触发器执行，被删除的旧记录存放到DeletedDeleted表中。表中。qUPDATEUPDATE操作等价于插入一条新记录，同时删除旧记录。对于操作等价于插入一条新记录，同时删除旧记录。对于UPDATEUPDATE操作来说，操作来说，UPDATEUPDATE触发器执行，

41、表中原记录被移动到触发器执行，表中原记录被移动到DeletedDeleted表中（更新完后即被删除），修改后的记录插入到表中（更新完后即被删除），修改后的记录插入到InsertedInserted表中。表中。qinsertedinserted和和deleteddeleted表的结构与触发器所在数据表的结构是表的结构与触发器所在数据表的结构是完全一致的。它们的操作和普通表的操作也一致。例如，若完全一致的。它们的操作和普通表的操作也一致。例如，若要检索要检索 deleted deleted 表中的所有值，则使用语句：表中的所有值，则使用语句： SELECT SELECT * * FROM dele

42、ted FROM deletedn3. 3. 创建创建DMLDML触发器的语法规则触发器的语法规则q创建创建DMLDML触发器的语法规则如下：触发器的语法规则如下：CREATE TRIGGER CREATE TRIGGER 触发器名称触发器名称ON table | view ON table | view FOR |AFTER | INSTEAD OF FOR |AFTER | INSTEAD OF INSERT , UPDATE , INSERT , UPDATE , DELETE DELETE AS AS SQLSQL语句语句,nnu其中：其中： AFTERAFTER 指定触发器只有在触发

43、指定触发器只有在触发 SQL SQL 语句中指定的所有语句中指定的所有操作都已成功执行后才激发。所有的引用级联操操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定发器。如果仅指定 FOR FOR 关键字，则关键字，则 AFTER AFTER 是默是默认设置。认设置。 INSTEAD OFINSTEAD OF 指定执行触发器而不是执行触发指定执行触发器而不是执行触发 SQL SQL 语句，从语句，从而替代触发语句的操作。而替代触发语句的操作。【例例】 使用使用SSMSSSMS在在studentstuden

44、t表上创建一个触发器表上创建一个触发器trigoptrigop，其，其功能是在用户插入、修改或删除该表中行中输出所有的行。功能是在用户插入、修改或删除该表中行中输出所有的行。 CREATE TRIGGER CREATE TRIGGER tr1tr1ON student AFTER INSERT,DELETE,UPDATEON student AFTER INSERT,DELETE,UPDATEAS AS BEGINBEGINSET NOCOUNT ONSET NOCOUNT ONSELECT SELECT * * FROM student FROM studentENDENDGOGO在触发器在

45、触发器tr1tr1创建创建完毕，当对完毕，当对studentstudent表进行记录插入、修改表进行记录插入、修改或删除操作时，触发器或删除操作时，触发器tr1tr1都会都会被自动执行。被自动执行。例如，执行以下语句：例如，执行以下语句：INSERT student INSERT student VALUES(1, VALUES(1,刘明刘明,男男,1991-12-12,1035),1991-12-12,1035)当当向向studentstudent表中插入一个记录时自动执行触发器表中插入一个记录时自动执行触发器tr1tr1输出输出其其所有记录，输出结果如图所示，从中看到新记录已经插入到所有记

46、录，输出结果如图所示，从中看到新记录已经插入到studentstudent表中了。表中了。 例：创建触发器例：创建触发器stu_deletestu_delete，实现如下功能：当按照学号删，实现如下功能：当按照学号删除除studentstudent表中的某学生记录后，对应的该学生在表中的某学生记录后，对应的该学生在scorescore表表中的记录也被自动删除中的记录也被自动删除。T-SQLT-SQL语句为：语句为：CREATE TRIGGER CREATE TRIGGER stu_Deletestu_Delete ON student ON studentFOR DELETEFOR DELET

47、EASASDELETE FROM score WHERE DELETE FROM score WHERE snosno=(SELECT =(SELECT snosno FROM FROM deleted)deleted)在在studentstudent表中执行数据删除语句：表中执行数据删除语句：DELETE FROM student WHERE DELETE FROM student WHERE snosno=20070102=20070102 执行上述语句设定的触发器被触发，执行上述语句设定的触发器被触发，scorescore表中的相应数据表中的相应数据被自动删除。可通过查询被自动删除。可通

48、过查询studentstudent表和表和scorescore表删除前后的该表删除前后的该生记录进行触发器的验证。生记录进行触发器的验证。n上面例题的执行过程如下：上面例题的执行过程如下：（1 1）当系统接收到一个要执行）当系统接收到一个要执行studentstudent表删除操作表删除操作的的T-SQLT-SQL语句时，系统将要删除的记录存放在删除表语句时，系统将要删除的记录存放在删除表DeletedDeleted中；中；（2 2）把数据表）把数据表studentstudent中的相应记录删除；中的相应记录删除；（3 3）删除操作激活了事先编制的）删除操作激活了事先编制的AFTERAFTER

49、触发器，系触发器，系统执行统执行AFTERAFTER触发器中触发器中ASAS后的后的T-SQLT-SQL语句；语句；（4 4）触发器执行完毕之后，删除内存中的）触发器执行完毕之后，删除内存中的DeletedDeleted表，结束整个操作。若触发器语句执行失败，则整表，结束整个操作。若触发器语句执行失败，则整个过程回滚，恢复到初始状态。个过程回滚，恢复到初始状态。【例例】 在数据库在数据库testtest中建立一个表中建立一个表table10table10，在该表上创，在该表上创建一个触发器建一个触发器trigtesttrigtest。在。在table10table10表中插入、修改和删除记表中

50、插入、修改和删除记录时，自动显示表中的所有记录。并用相关数据进行测试。录时，自动显示表中的所有记录。并用相关数据进行测试。解：创建表和触发器的语句如下：解：创建表和触发器的语句如下：USE testUSE testGOGOCREATE TABLE table10CREATE TABLE table10 -创建表创建表table10table10( (c1 c1 intint, ,c2 char(30)c2 char(30)GOGOCREATE TRIGGER CREATE TRIGGER trigtesttrigtest -创建触发器创建触发器trigtesttrigtestON table1

51、0 AFTER INSERT,UPDATE,DELETE ON table10 AFTER INSERT,UPDATE,DELETE ASASSELECT SELECT * * FROM table10 FROM table10GOGO在执行下面的语句时：在执行下面的语句时：USE testUSE testINSERT Table10 VALUES(1,Name1)INSERT Table10 VALUES(1,Name1)GOGO结果会显示出结果会显示出table10table10表中的行如图所示：表中的行如图所示： 在执行下面的语句时：在执行下面的语句时：USE testUSE testU

52、PDATE Table10 SET c2=Name2 WHERE c1=1UPDATE Table10 SET c2=Name2 WHERE c1=1GOGO结果会显示出结果会显示出table10table10表中的记录行如图所示：表中的记录行如图所示：【例例】 下面一段下面一段T-SQLT-SQL语句说明语句说明insertedinserted表和表和deleteddeleted表表的作用。的作用。CREATE TRIGGER CREATE TRIGGER trigtesttrigtest ON table10 AFTER INSERT,UPDATE,DELETE ON table10 AF

53、TER INSERT,UPDATE,DELETEASAS PRINT inserted PRINT inserted表表: SELECT SELECT * * FROM inserted FROM inserted PRINT deleted PRINT deleted表表: SELECT SELECT * * FROM deleted FROM deletedGOGO如果此时执行下面的如果此时执行下面的INSERTINSERT语句：语句：INSERT table10 VALUES(2,Name3INSERT table10 VALUES(2,Name3)其执行结果如图所示：其执行结果如图所示

54、：如果此时接着执行下面的如果此时接着执行下面的UPDATEUPDATE语句：语句：UPDATE table10 SET c2=Name4 WHERE c1=2UPDATE table10 SET c2=Name4 WHERE c1=2其执行结果如图所示其执行结果如图所示: : 如果此时接着执行下面的如果此时接着执行下面的DELETEDELETE语句：语句：DELETE table10 WHERE c1=2DELETE table10 WHERE c1=2其执行结果如如图所示：其执行结果如如图所示：【例例】 建立一个触发器建立一个触发器tntn，当向，当向studentstudent表中插入数据

55、时，表中插入数据时，如果出现姓名重复的情况，则回滚该事务。如果出现姓名重复的情况，则回滚该事务。解：创建触发器解：创建触发器trignametrigname的程序如下：的程序如下：CREATE TRIGGER tnCREATE TRIGGER tn ON student AFTER INSERT ON student AFTER INSERT ASASBEGINBEGINDECLARE name char(10)DECLARE name char(10)SELECT name=inserted.SELECT name=inserted.姓名姓名 FROM insertedFROM insert

56、edIF EXISTS(SELECT IF EXISTS(SELECT 姓名姓名 FROM student FROM student WHERE WHERE 姓名姓名=name)=name)BEGINBEGINRAISERROR(RAISERROR(姓名重复姓名重复, ,不能插入不能插入,16,1),16,1)ROLLBACKROLLBACKENDENDENDEND执行以下程序：执行以下程序：INSERT INTO student(INSERT INTO student(学号学号, ,姓名姓名, ,性别性别) ) VALUES(102, VALUES(102,王丽王丽,女女)出现如图所示的消息

57、，提示插入的记录出错。出现如图所示的消息，提示插入的记录出错。 再打开再打开studentstudent表，从中看到，由于进行了事务回滚，表，从中看到，由于进行了事务回滚，所以并不会真正向所以并不会真正向studentstudent表中插入学号为表中插入学号为102102的新记录。的新记录。 【例例】 建立一个触发器建立一个触发器tsts，当向，当向studentstudent表中插入数据时，表中插入数据时，如果出现性别不正确的情况，不回滚该事务，只提示错误消息。如果出现性别不正确的情况，不回滚该事务，只提示错误消息。解：创建触发器解：创建触发器trignsextrignsex的程序如下：的程

58、序如下：CREATE TRIGGER CREATE TRIGGER tstsON student AFTER INSERTON student AFTER INSERTASASDECLARE s1 char(1)DECLARE s1 char(1)SELECT s1=SELECT s1=性别性别 FROM INSERTEDFROM INSERTEDIF s1IF s1男男 OR s1 OR s1女女 RAISERROR(RAISERROR(性别只能取男或女性别只能取男或女,16,1),16,1)GOGO当执行以下程序：当执行以下程序：INSERT student VALUES(2,INSERT

59、 student VALUES(2,许涛许涛,M,1992-10-16,1035),M,1992-10-16,1035)出现如图所示的消息，提示插入的记录出错。出现如图所示的消息，提示插入的记录出错。 再打开再打开studentstudent表，从中看到，由于没有进行事务回滚，表，从中看到，由于没有进行事务回滚，尽管要插入的记录不正确，但仍然插入到尽管要插入的记录不正确，但仍然插入到studentstudent表中了。表中了。 【例例】 建立一个修改触发器建立一个修改触发器trignotrigno，该触发器防止用户，该触发器防止用户修改表修改表studentstudent的学号。的学号。解：创

60、建触发器解：创建触发器trignnotrignno的程序如下：的程序如下：CREATE TRIGGER CREATE TRIGGER trignotrigno ON student ON student AFTER UPDATE AFTER UPDATEASAS IF UPDATE( IF UPDATE(学号学号) )BEGINBEGIN RAISERROR( RAISERROR(不能修改学号不能修改学号,16,2),16,2) ROLLBACK ROLLBACKENDENDGOGO当执行以下程序：当执行以下程序：UPDATE student SET UPDATE student SET 学号