第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或者或

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

4、标的基本概念与特点 n掌握存储过程的基本类型和相关操作掌握存储过程的基本类型和相关操作 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. 可重用性：可重用性：存储过程只需创建并存储在数据库中，以后即存储过程只需创建并存储在数据库中，以后即 可任意在程序中调用该过程。存储过程可独立于程序源代码可任意在程序中调用该过程。存储过程可独立于程序源代码 而单独修改，减少数据库开发人员的工作量。而单独修改，减少数据

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

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

11、为法格式为： CREATE PROCEDURE procedure_name parameter data_type = defaultOUTPUT ,n AS sql_statement n 1 10.1.1 10.1.1 创建存储过程创建存储过程 其中：其中： qparameterparameter：过程中的参数。在该语句中可以声明过程中的参数。在该语句中可以声明 一个或多个参数。用户必须在执行过程时提供每个一个或多个参数。用户必须在执行过程时提供每个 所声明参数的值，使用所声明参数的值，使用 符号作为第一个字符来指符号作为第一个字符来指 定参数名称。定参数名称。 qdata_typeda

12、ta_type：参数的数据类型。参数的数据类型。 qD Defaultefault：参数的默认值。参数的默认值。 qOUTPUTOUTPUT：表明参数是返回参数。表明参数是返回参数。 【例例】 创建一个存储过程创建一个存储过程stud_degreestud_degree，用于检索所有学生的，用于检索所有学生的 成绩记录，包括学号、姓名、课程名和分数。成绩记录，包括学号、姓名、课程名和分数。 解：对应的程序如下：解：对应的程序如下： CREATE PROCEDURE CREATE PROCEDURE stud_degreestud_degree ASAS SELECT SELECT studen

13、t.student.学号学号, ,姓名姓名, ,课程名课程名, ,分数分数 FROM FROM student,course,scorestudent,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_stat

14、us = procedure_name parameter = value | variable OUTPUT | DEFAULT , n 10.1.2 10.1.2 执行存储过程执行存储过程 q各个参数的含义：各个参数的含义： l return_statusreturn_status 保存存储过程的返回状态保存存储过程的返回状态 lprocedure_nameprocedure_name 调用的存储过程的名称调用的存储过程的名称 lparameter parameter 过程参数过程参数 lValue Value 过程参数的值过程参数的值 lvariable variable 用来保存参数或

15、返回参数的变量用来保存参数或返回参数的变量 lOUTPUTOUTPUT 指定存储过程必须返回的一个参数指定存储过程必须返回的一个参数 lDEFAULT DEFAULT 默认参数值默认参数值 n在调用存储过程时的两种传递参数的方式：在调用存储过程时的两种传递参数的方式： q方式一：方式一： EXEC EXEC 存储过程名存储过程名 实参列表实参列表 q方式二：方式二： EXEC EXEC 存储过程名存储过程名 参数参数1=1=值值1,1,参数参数2=2=值值2,2, 【例例】 执行存储过程执行存储过程maxdegreemaxdegree并查看输出的结果。并查看输出的结果。 create proc

16、edure maxderee create procedure maxderee as as begin begin select max(degree) as select max(degree) as 最高分最高分 from score from score end end 执行执行maxdegreemaxdegree存储过程的程序如下：存储过程的程序如下： EXEC maxdegreeEXEC maxdegree 其执行结果如图所示：其执行结果如图所示： n1. 1. 使用参数使用参数 带参数的存储过程的一般格式如下：带参数的存储过程的一般格式如下： CREATE PROCEDURE C

17、REATE PROCEDURE 存储过程名存储过程名( ( 参数列表参数列表 ) ) AS SQLAS SQL语句语句 10.1.3 10.1.3 存储过程的参数存储过程的参数 【例例】 设计一个存储过程设计一个存储过程maxnomaxno，以学号为参数，输出指定，以学号为参数，输出指定 学号学生的所有课程中最高分和对应的课程名及成绩。学号学生的所有课程中最高分和对应的课程名及成绩。 解：解： CREATE PROCEDURE CREATE PROCEDURE maxnomaxno(no char(10) (no char(10) ASAS SELECT s. SELECT s.学号学号,s.

18、,s.姓名姓名,c.,c.课程名课程名,sc.,sc.分数分数 FROM student FROM 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) GOGO 采用方式一

19、执行存储过程采用方式一执行存储过程maxnomaxno的程序如下：的程序如下： EXEC EXEC maxnomaxno 103 103 采用方式二执行存储过程采用方式二执行存储过程maxnomaxno的程序如下：的程序如下： EXEC EXEC maxnomaxno no=103 no=103 n2. 2. 使用默认参数使用默认参数 q在设计存储过程时，可以为参数提供一个默认值，在设计存储过程时，可以为参数提供一个默认值， 默认值必须为常量或者默认值必须为常量或者NULLNULL。其一般格式如下：。其一般格式如下： CREATE PROCEDURE CREATE PROCEDURE 存储过程

20、名存储过程名( ( 参数参数1=1=默认值默认值1, 1, 参参 数数2=2=默认值默认值2, )2, ) AS SQLAS SQL语句语句 q在调用存储过程时，如果不指定对应的实参值，则在调用存储过程时，如果不指定对应的实参值，则 自动用对应的默认值代替。自动用对应的默认值代替。 【例例】 设计一个存储过程设计一个存储过程maxnomaxno，以学号为参数，输出指定，以学号为参数，输出指定 学号学生的所有课程中最高分和对应的课程名，学号默认值为学号学生的所有课程中最高分和对应的课程名，学号默认值为 101101。 解：解： CREATE PROCEDURE maxno1(no CREATE

21、PROCEDURE maxno1(no intint=101) =101) AS AS SELECT s. SELECT s.学号学号,s.,s.姓名姓名,c.,c.课程名课程名,sc.,sc.分数分数 FROM student FROM 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

22、 MAX(分数分数) FROM score WHERE ) FROM score WHERE 学号学号=no)=no) 当不指定实参调用当不指定实参调用maxno1maxno1存储过程时，其结果如图所示：存储过程时，其结果如图所示： 当指定实参为当指定实参为105105调用调用maxno1maxno1存储过程时，其结果如图所：存储过程时，其结果如图所： n3. 3. 使用返回参数使用返回参数 在创建存储过程时，可以定义返回参数。在执在创建存储过程时，可以定义返回参数。在执 行存储过程时，可以将结果给返回参数。返回参行存储过程时，可以将结果给返回参数。返回参 数用数用OUTPUTOUTPUT进行

23、说明。进行说明。 【例例】 创建一个存储过程创建一个存储过程averageaverage，它返回两个参数，它返回两个参数 st_namest_name和和st_avgst_avg，分别代表了姓名和平均分。并编写，分别代表了姓名和平均分。并编写T-T- SQLSQL语句执行该存储过程和查看输出的结果。语句执行该存储过程和查看输出的结果。 解：建立存储过程解：建立存储过程averageaverage的程序如下：的程序如下： CREATE PROCEDURE averageCREATE PROCEDURE average ( ( st_nost_no intint, , st_namest_name

24、 char(8) char(8) OUTPUTOUTPUT, , st_avgst_avg float float OUTPUTOUTPUT ) AS) AS SELECT SELECT st_namest_name=student.=student.姓名姓名,st_avgst_avg=AVG(score.=AVG(score.分数分数) ) FROM FROM student,scorestudent,score WHERE student. WHERE student.学号学号=score.=score.学号学号 GROUP BY student.GROUP BY student.学号学号

25、,student.,student.姓名姓名 HAVING student.HAVING student.学号学号=st_nost_no 执行该存储过程，来查询学号为执行该存储过程，来查询学号为“105”105”的学生姓名和平均分：的学生姓名和平均分： DECLARE DECLARE st_namest_name char(10) char(10) DECLARE DECLARE st_avgst_avg float float EXEC average 105,st_name EXEC average 105,st_name OUTPUTOUTPUT, , st_avgst_avg OUTP

26、UTOUTPUT SELECT SELECT 姓名姓名=st_namest_name,平均分平均分=st_avgst_avg n4. 4. 存储过程的返回值存储过程的返回值 q存储过程在执行后都会返回一个整型值（称为存储过程在执行后都会返回一个整型值（称为“返返 回代码回代码”），指示存储过程的执行状态。），指示存储过程的执行状态。 q如果执行成功，返回如果执行成功，返回0 0；否则返回；否则返回-1-1-99-99之间的数之间的数 值（例如值（例如-1-1表示找不到对象，表示找不到对象，-2-2表示数据类型错误表示数据类型错误 ，-5-5表示语法错误等）。表示语法错误等）。 q也可以使用也可

27、以使用RETURNRETURN语句指定一个返回值。语句指定一个返回值。 【例例】 编写一个程序，创建存储过程编写一个程序，创建存储过程test_rettest_ret，根据输入，根据输入 的参数来判断返回值。的参数来判断返回值。 解：建立存储过程解：建立存储过程test_rettest_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 RETUR

28、N -1000 RETURN -1000 执行以上存储过程：执行以上存储过程： declare r intdeclare r int exec r=ret 1exec r=ret 1 print rprint r 运行结果：运行结果： 10.1.4 10.1.4 查看、修改和删除存储过程查看、修改和删除存储过程 n使用使用SQL ServerSQL Server管理控制器查看或修改存储过管理控制器查看或修改存储过 程程 n使用使用sp_helptextsp_helptext存储过程来查看存储过程的定存储过程来查看存储过程的定 义信息义信息 n使用使用SQL ServerSQL Server管理

29、控制器删除存储过程管理控制器删除存储过程 n使用使用DROP PROCEDUREDROP PROCEDURE删除存储过程删除存储过程 【例例】 使用相关系统存储过程查看存储过程使用相关系统存储过程查看存储过程stud_degreestud_degree的的 相关内容。相关内容。 解：对应的程序如下：解：对应的程序如下： USE schoolUSE school GOGO EXEC sp_helptext stud_degreeEXEC sp_helptext stud_degree 10.2 10.2 触发器触发器 n触发器是一种特殊类型的存储过程。触发器可包含复触发器是一种特殊类型的存储过程

30、。触发器可包含复 杂的杂的T-SQLT-SQL语句。触发器不能通过名称被直接调用，语句。触发器不能通过名称被直接调用， 也不允许设置参数。它是在插入、删除和修改指定表也不允许设置参数。它是在插入、删除和修改指定表 中的数据时触发执行。中的数据时触发执行。 n触发器可以强制执行一定的业务规则，以保持数据完触发器可以强制执行一定的业务规则，以保持数据完 整性、检查数据有效性、实现数据库管理任务和一些整性、检查数据有效性、实现数据库管理任务和一些 附加功能。附加功能。 n对于数据库中约束所不能保证的复杂的参照完整性和对于数据库中约束所不能保证的复杂的参照完整性和 数据的一致性可使用触发器来实现。数据

31、的一致性可使用触发器来实现。 10.2.1 10.2.1 触发器概述触发器概述 1. 1. 触发器的功能触发器的功能 q在在SQL ServerSQL Server内部，触发器被看作是存储过程，它与存内部，触发器被看作是存储过程，它与存 储过程所经历的处理过程类似。但是触发器没有输入参储过程所经历的处理过程类似。但是触发器没有输入参 数和输出参数，因而不能被显示调用。它作为语句的执数和输出参数，因而不能被显示调用。它作为语句的执 行结果自动引发，而存储过程则是通过存储过程名称而行结果自动引发，而存储过程则是通过存储过程名称而 被直接调用。被直接调用。 q触发器与表紧密相连，当用户对表进行诸如触

32、发器与表紧密相连，当用户对表进行诸如UPDATEUPDATE、 INSERTINSERT和和DELETEDELETE这些操作时，系统会自动执行触发器所这些操作时，系统会自动执行触发器所 定义的定义的SQLSQL语句，从而确保对数据的处理符合由这些语句，从而确保对数据的处理符合由这些SQL SQL 语句所定义的规则。语句所定义的规则。 q强化约束：触发器能够实现比强化约束：触发器能够实现比CHECK CHECK 语句更为复杂的约束：语句更为复杂的约束： l触发器可以很方便地引用其他表的列，去进行逻辑上的触发器可以很方便地引用其他表的列，去进行逻辑上的 检查；检查； l触发器是在触发器是在CHEC

33、KCHECK之后执行的；之后执行的； l触发器可以插入，删除，更新多行。触发器可以插入，删除，更新多行。 q跟踪变化：触发器可以侦测数据库内的操作从而禁止数据跟踪变化：触发器可以侦测数据库内的操作从而禁止数据 库中未经许可的更新和变化，确保输入表中的数据的有效库中未经许可的更新和变化，确保输入表中的数据的有效 性。例如在库存系统中，触发器可以检测到当实际库存下性。例如在库存系统中，触发器可以检测到当实际库存下 降到了需要再进货的临界量，就给出管理员相应提示信息降到了需要再进货的临界量，就给出管理员相应提示信息 或自动生成给供应商的订单；或自动生成给供应商的订单； q级联运行：触发器可以侦测数据

34、库内的操作，并自动地级级联运行：触发器可以侦测数据库内的操作，并自动地级 联影响整个数据库的不同表中的各项内容。如：设置一个联影响整个数据库的不同表中的各项内容。如：设置一个 触发器，当触发器，当studentstudent表中删除一个学号信息时，对应的表中删除一个学号信息时，对应的 scorescore表中相应的学号信息也被删除；表中相应的学号信息也被删除； q调用存储过程：为了响应数据库更新，触发器可以调用一调用存储过程：为了响应数据库更新，触发器可以调用一 个或多个存储过程。个或多个存储过程。 2. 2. 触发器的种类触发器的种类 nSQL Server 2008SQL Server 2

35、008支持两种类型的触发器：支持两种类型的触发器：DMLDML触发器和触发器和 DDLDDL触发器。触发器。 qDMLDML触发器触发器：如果用户要通过数据操作语言：如果用户要通过数据操作语言 (DML)(DML)编辑编辑 数据，则执行数据，则执行 DML DML 触发器。触发器。DML DML 事件是针对表或视图事件是针对表或视图 的的 INSERTINSERT、UPDATEUPDATE和和DELETE DELETE 语句，即语句，即DMLDML触发器在数触发器在数 据修改时被执行。系统将触发器和触发它的语句作为可据修改时被执行。系统将触发器和触发它的语句作为可 在触发器内回滚的单个事务对待

36、。如果检测到错误（例在触发器内回滚的单个事务对待。如果检测到错误（例 如，磁盘空间不足），则整个事务自动回滚；如，磁盘空间不足），则整个事务自动回滚； qDDLDDL触发器：触发器：为了响应各种数据定义语言为了响应各种数据定义语言 (DDL) (DDL) 事件而事件而 激发。激发。DDLDDL事件主要与以关键字事件主要与以关键字 CREATECREATE、ALTER ALTER 和和 DROP DROP 开头的开头的 T-SQL T-SQL 语句对应。它们可以用于在数据库语句对应。它们可以用于在数据库 中执行管理任务，例如，审核以及规范数据库操作。中执行管理任务，例如，审核以及规范数据库操作。

37、 10.2.2 DML10.2.2 DML触发器的创建和应用触发器的创建和应用 n1. DML1. DML触发器的分类触发器的分类 qAFTERAFTER触发器：触发器：这类触发器是在记录已经被改变完，相这类触发器是在记录已经被改变完，相 关事务提交后，才会被触发执行。主要是用于记录变更关事务提交后，才会被触发执行。主要是用于记录变更 后的处理或检查，一旦发现错误，可以用后的处理或检查，一旦发现错误，可以用ROLLBACK ROLLBACK TRANSACTIONTRANSACTION语句来回滚本次的操作。对同一个表达操语句来回滚本次的操作。对同一个表达操 作，可定义多个作，可定义多个AFTE

38、RAFTER触发器，并定义各种触发器执行触发器，并定义各种触发器执行 的先后顺序。的先后顺序。 qINSTEAD OFINSTEAD OF触发器：触发器：这类触发器并不去执行其所定义的这类触发器并不去执行其所定义的 操作（操作（INSERTINSERT、UPDATEUPDATE、DELETEDELETE），而去执行触发器本），而去执行触发器本 身所定义的操作。这类触发器一般是用来取代原本的操身所定义的操作。这类触发器一般是用来取代原本的操 作，在记录变更之前被触发的。作，在记录变更之前被触发的。 n2. 2. 触发器中的逻辑（虚拟）表触发器中的逻辑（虚拟）表 q当表被修改，无论是插入、修改还是

39、删除，被操作的记当表被修改，无论是插入、修改还是删除，被操作的记 录会保存在两个系统的逻辑表中，这两个逻辑表是录会保存在两个系统的逻辑表中，这两个逻辑表是 insertedinserted（插入）表和（插入）表和deleteddeleted（删除）表。（删除）表。 q这两个表是建在数据库服务器的内存中的，是由系统管这两个表是建在数据库服务器的内存中的，是由系统管 理的逻辑表，而不是真正存储在数据库中的物理表。对理的逻辑表，而不是真正存储在数据库中的物理表。对 于这两个表，用户只有读取的权限，没有修改的权限。于这两个表，用户只有读取的权限，没有修改的权限。 当触发器的工作完成之后，这两个表将会从

40、内存中删除。当触发器的工作完成之后，这两个表将会从内存中删除。 qinsertedinserted表里存放的是新插入的记录：对于表里存放的是新插入的记录：对于INSERTINSERT操作来说，操作来说， INSERTINSERT触发器执行，新的记录插入到触发器表和触发器执行，新的记录插入到触发器表和insertedinserted表表 中。在进行中。在进行INSERTINSERT和和UPDATEUPDATE触发器时，触发器时，insertedinserted表中有数表中有数 据，而在据，而在DELETEDELETE触发器中触发器中insertedinserted表是空的。表是空的。 qDele

41、tedDeleted表里存放的是已从表中删除的记录：对于表里存放的是已从表中删除的记录：对于DELETEDELETE操操 作来说，作来说，DELETEDELETE触发器执行，被删除的旧记录存放到触发器执行，被删除的旧记录存放到 DeletedDeleted表中。表中。 qUPDATEUPDATE操作等价于插入一条新记录，同时删除旧记录。对于操作等价于插入一条新记录，同时删除旧记录。对于 UPDATEUPDATE操作来说，操作来说，UPDATEUPDATE触发器执行，表中原记录被移动到触发器执行，表中原记录被移动到 DeletedDeleted表中（更新完后即被删除），修改后的记录插入到表中（更

42、新完后即被删除），修改后的记录插入到 InsertedInserted表中。表中。 qinsertedinserted和和deleteddeleted表的结构与触发器所在数据表的结构是表的结构与触发器所在数据表的结构是 完全一致的。它们的操作和普通表的操作也一致。例如，若完全一致的。它们的操作和普通表的操作也一致。例如，若 要检索要检索 deleted deleted 表中的所有值，则使用语句：表中的所有值，则使用语句： SELECT SELECT * * FROM deleted FROM deleted n3. 3. 创建创建DMLDML触发器的语法规则触发器的语法规则 q创建创建DMLD

43、ML触发器的语法规则如下：触发器的语法规则如下： 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语句语句,nn u其中：其中： AFTERAFTER 指定触发器只有在触发指定触发器只有在触发 SQL SQL 语句中指定的所有语句中指定的所有 操作都已成功执行后才激发。所有的引用级

44、联操操作都已成功执行后才激发。所有的引用级联操 作和约束检查也必须成功完成后，才能执行此触作和约束检查也必须成功完成后，才能执行此触 发器。如果仅指定发器。如果仅指定 FOR FOR 关键字，则关键字，则 AFTER AFTER 是默是默 认设置。认设置。 INSTEAD OFINSTEAD OF 指定执行触发器而不是执行触发指定执行触发器而不是执行触发 SQL SQL 语句，从语句，从 而替代触发语句的操作。而替代触发语句的操作。 【例例】 使用使用SSMSSSMS在在studentstudent表上创建一个触发器表上创建一个触发器trigoptrigop，其，其 功能是在用户插入、修改或删

45、除该表中行中输出所有的行。功能是在用户插入、修改或删除该表中行中输出所有的行。 CREATE TRIGGER CREATE TRIGGER tr1tr1 ON student AFTER INSERT,DELETE,UPDATEON student AFTER INSERT,DELETE,UPDATE AS AS BEGINBEGIN SET NOCOUNT ONSET NOCOUNT ON SELECT SELECT * * FROM student FROM student ENDEND GOGO 在触发器在触发器tr1tr1创建创建完毕，当对完毕，当对studentstudent表进行记

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

47、nt表中了。表中了。 例：创建触发器例：创建触发器stu_deletestu_delete，实现如下功能：当按照学号删，实现如下功能：当按照学号删 除除studentstudent表中的某学生记录后，对应的该学生在表中的某学生记录后，对应的该学生在scorescore表表 中的记录也被自动删除中的记录也被自动删除。T-SQLT-SQL语句为：语句为： CREATE TRIGGER CREATE TRIGGER stu_Deletestu_Delete ON student ON student FOR DELETEFOR DELETE ASAS DELETE FROM score WHERE

48、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表中的相应数据表中的相应数据 被自动删除。可通过查询被自动删除。可通过查询studentstudent表和表和score

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

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

51、自动显示表中的所有记录。并用相关数据进行测试。录时，自动显示表中的所有记录。并用相关数据进行测试。 解：创建表和触发器的语句如下：解：创建表和触发器的语句如下： USE testUSE test GOGO CREATE TABLE table10CREATE TABLE table10 -创建表创建表table10table10 ( (c1 c1 intint, , c2 char(30)c2 char(30) GOGO CREATE TRIGGER CREATE TRIGGER trigtesttrigtest -创建触发器创建触发器trigtesttrigtest ON table10 A

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

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

54、 table10 AFTER INSERT,UPDATE,DELETE ASAS PRINT inserted PRINT inserted表表: SELECT SELECT * * FROM inserted FROM inserted PRINT deleted PRINT deleted表表: SELECT SELECT * * FROM deleted FROM deleted GOGO 如果此时执行下面的如果此时执行下面的INSERTINSERT语句：语句： INSERT table10 VALUES(2,Name3INSERT table10 VALUES(2,Name3) 其执行

55、结果如图所示：其执行结果如图所示： 如果此时接着执行下面的如果此时接着执行下面的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，当向，

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

57、名姓名 FROM insertedFROM inserted IF EXISTS(SELECT IF EXISTS(SELECT 姓名姓名 FROM student FROM student WHERE WHERE 姓名姓名=name)=name) BEGINBEGIN RAISERROR(RAISERROR(姓名重复姓名重复, ,不能插入不能插入,16,1),16,1) ROLLBACKROLLBACK ENDEND ENDEND 执行以下程序：执行以下程序： INSERT INTO student(INSERT INTO student(学号学号, ,姓名姓名, ,性别性别) ) VALU

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

59、。 解：创建触发器解：创建触发器trignsextrignsex的程序如下：的程序如下： CREATE TRIGGER CREATE TRIGGER tsts ON student AFTER INSERTON student AFTER INSERT ASAS DECLARE s1 char(1)DECLARE s1 char(1) SELECT s1=SELECT s1=性别性别 FROM INSERTEDFROM INSERTED IF s1IF s1男男 OR s1 OR s1女女 RAISERROR(RAISERROR(性别只能取男或女性别只能取男或女,16,1),16,1) GOG

60、O 当执行以下程序：当执行以下程序： INSERT student VALUES(2,INSERT student VALUES(2,许涛许涛,M,1992-10-16,1035),M,1992-10-16,1035) 出现如图所示的消息，提示插入的记录出错。出现如图所示的消息，提示插入的记录出错。 再打开再打开studentstudent表，从中看到，由于没有进行事务回滚，表，从中看到，由于没有进行事务回滚， 尽管要插入的记录不正确，但仍然插入到尽管要插入的记录不正确，但仍然插入到studentstudent表中了。表中了。 【例例】 建立一个修改触发器建立一个修改触发器trignotrig