第7章存储过程和触发器.ppt

《第7章存储过程和触发器.ppt》由会员分享，可在线阅读，更多相关《第7章存储过程和触发器.ppt（52页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、2023年1月22日第1页第第7章存储过程和触发器章存储过程和触发器教学目标教学重点教学过程2023年1月22日第2页教学目标教学目标l了解存储过程的基本概念、作用、类型l掌握存储过程的使用l了解触发器的基本概念、作用、类型l掌握触发器的使用2023年1月22日第3页教学重点及难点教学重点及难点重点：l存储过程的基本概念、作用、类型l掌握存储过程的使用l触发器的基本概念、作用、类型l触发器的使用l难点：l存储过程的使用l触发器的使用2023年1月22日第4页教学过程教学过程l引例l7.1 存储过程l7.2 触发器l7.3本章小结2023年1月22日第5页引例引例小王作为技术培训中心的数据库开发

2、人员，负责小王作为技术培训中心的数据库开发人员，负责“学生选课学生选课”数据库设数据库设计。技术培训中心使用一个企业内部的应用程序来管理学生选课的信息；这计。技术培训中心使用一个企业内部的应用程序来管理学生选课的信息；这个应用程序在为个应用程序在为【选修选修】表输入数据时，是从下拉框中选择学生姓名与课程表输入数据时，是从下拉框中选择学生姓名与课程名称，在文本框中输入成绩；同时该系统还要求当一个学生毕业时，该学生名称，在文本框中输入成绩；同时该系统还要求当一个学生毕业时，该学生的基本信息必须被复制到一个的基本信息必须被复制到一个【毕业生毕业生】表中。表中。实现方法：创建一个实现方法：创建一个“增

3、加增加_选修选修”存储过程，在应用程序中调用该存存储过程，在应用程序中调用该存储过程，即可方便地在储过程，即可方便地在【选修选修】表中增加的数据记录。表中增加的数据记录。在在【学生学生】表上创建了一个触发器，当学生表的数据删除时，该数据被表上创建了一个触发器，当学生表的数据删除时，该数据被自动地增加到自动地增加到【毕业生毕业生】表中。表中。（1）创建存储过程“增加_选修”CREATE PROC 增加_选修 姓名CHAR(8),课程名CHAR(20),分数SMALLINT AS BEGIN DECLARE 学号CHAR(7),课程号CHAR(3)SELECT 学号=学号FROM 学生WHERE

4、姓名=姓名 SELECT 课程号=课程号FROM 课程WHERE 课程名=课程名 INSERT 选修VALUES(学号,课程号,分数)END（2）创建触发器“学生_毕业”CREATE TRIGGER 学生_毕业ON 学生 FOR DELETEAS INSERT INTO 毕业生 SELECT*FROM deleted2023年1月22日第6页7.1存储过程存储过程l7.1.1 存储过程的基本概念l7.1.2 存储过程的类型l7.1.3 存储过程的创建与执行l7.1.4 存储过程的查看、修改和删除2023年1月22日第7页7.1.1存储过程的基本概念存储过程的基本概念存储过程是一组编译在单个执行

5、计划中的存储过程是一组编译在单个执行计划中的T-SQL语句。语句。使用使用T-SQL进行编程有两种方法：一是在本地存储进行编程有两种方法：一是在本地存储T-SQL程序，并创建应用程序程序，并创建应用程序向向SQLServer发送命令来对结果进行处理；二是把部分用发送命令来对结果进行处理；二是把部分用T-SQL编写的程序作为存储编写的程序作为存储过程存储在过程存储在SQLServer中，然后创建应用程序来调用存储过程，对数据结果进行处理。中，然后创建应用程序来调用存储过程，对数据结果进行处理。SQLServer推荐使用第二种方法。推荐使用第二种方法。直接执行直接执行T-SQL语句，一般要经过以下

6、几个步骤：语句，一般要经过以下几个步骤：(1)T-SQL语句发送到服务器。语句发送到服务器。(2)服务器编译服务器编译T-SQL语句。语句。(3)优化产生查询执行计划。优化产生查询执行计划。(4)数据库引擎执行查询计划。数据库引擎执行查询计划。(5)执行结果发回客户程序。执行结果发回客户程序。2023年1月22日第8页7.1.1 存储过程的基本概念存储过程还具有以下突出的优点：(1)通过本地存储、代码预编译和缓存技术实现高性能的数据操作。(2)通过通用编程结构和过程重用实现编程框架。(3)通过隔离和加密的方法提高了数据库的安全性。存储过程与视图比较,它还存在以下的优点：(1)可以在单个存储过程

7、中执行一系列T-SQL语句。存储过程可包含程序流、逻辑以及对数据库查询的T-SQL语句，而视图中只能是SELECT语句。(2)视图不能接受参数，只能返回结果集，而存储过程可以接受参数，包括输入、输出参数，并能返回单个或多个结果集以及返回值，这样大大提高了应用的灵活性。2023年1月22日第9页7.1.2存储过程的类型存储过程的类型 在SQL Server 2005中，存储过程分为3类：系统存储过程、用户自定义的存储过程、扩展存储过程。1系统存储过程系统存储过程 系统存储过程是指用来完成SQL Server 2005中许多管理活动的特殊存储过程。展开“对象资源管理器”中的【数据库】|【Stude

8、ntCourse】|【可编程性】|【存储过程】，在【系统存储过程】下就可以看到所有系统存储过程的列表，如图7-1所示。系统存储过程在master数据库中，但在其他数据库中可以直接调用，并且在调用时不必在存储过程名称前加上数据库名称。图7-1 系统存储过程2023年1月22日第10页7.1.2存储过程的类型存储过程的类型2用户自定义存储过程用户自定义存储过程 用户自定义存储过程，是由用户创建并能完成某一特定功能(如查询用户所需数据信息)的存储过程，是封装了可重用代码的SQL语句模块。在SQL Server 2005系统中，用户自定义的存储过程包括T-SQL存储过程和CLR(公共语言运行时)存储过

9、程。T-SQL存储过程是指保存着T-SQL语句的集合，可以接受和返回用户提供的参数。CLR存储过程是指对MicrosoftNET Framework公共语言运行时方法(CLR)的引用，可以接受用户提供的参数并返回结果。3扩展存储过程扩展存储过程 扩展存储过程是指使用某种编程语言(例如C语言等)创建的外部例程，是可以在 Microsoft SQL Server实例中动态加载和运行的DLL2023年1月22日第11页7.1.3存储过程的创建与执行存储过程的创建与执行当创建存储过程时，需要确定存储过程的3个组成部分：(1)所有的输入参数以及传给调用者的输出参数。(2)被执行的针对数据库的操作语句，包

10、括调用其他存储过程的语句。(3)返回给调用者的状态值，以指明调用是成功还是失败。1在在SQLServerManagementStudio中创建存储过程中创建存储过程(1)展开【数据库】|【StudentCourse】|【可编程性】|【存储过程】。右击【存储过程】,在弹出的快捷菜单上执行【新建存储过程】，如图7-2所示。图7-2新建存储过程2023年1月22日第12页7.1.3存储过程的创建与执行存储过程的创建与执行(2)在新打开的SQL命令窗口中，给出了创建存储过程命令的模板(3)在模板中按照下面的代码修改建立存储过程的命令模板后CREATE PROC procGetAllCoursesAS

11、Select*FROM Courses(4)单击【执行】按钮，即可创建存储过程。(5)新建立的procGetAllCourses存储过程可通过以下代码执行，执行结果如图7-6所示，显示所有课程的信息。EXECUTE procGetAllCourses;图7-6执行存储过程2023年1月22日第13页7.1.3存储过程的创建与执行存储过程的创建与执行2用T-SQL命令窗口创建存储过程简单语法规则如下：简单语法规则如下：CREATEPROCEDURE架构名架构名.存储过程名称存储过程名称参数名称参数名称参数数据类型参数数据类型VARYING=defaultOUTPUT，nWITHRECOMPILE

12、|ENCRYPTION|RECOMPILE，ENCRYPTIONFORREPLICATIONAST-SQL语句集语句集2023年1月22日第14页7.1.3存储过程的创建与执行存储过程的创建与执行用用T-SQL命令创建存储过程的步骤如下：命令创建存储过程的步骤如下：(1)单击单击“新建查询新建查询”按钮打开按钮打开SQL命令窗口，输入建立存储过程的代码后执行。命令窗口，输入建立存储过程的代码后执行。CREATEPROCEDUREprocGetStudentnumberchar(7)ASSELECT*FROMStudentsWHERESno=number该存储过程接受与传递的参数精确匹配的值，从

13、学生表中返回指定学号的学生该存储过程接受与传递的参数精确匹配的值，从学生表中返回指定学号的学生的信息。的信息。(2)新建立的新建立的procGetStudent存储过程可通过以下代码执行，显示学号为存储过程可通过以下代码执行，显示学号为10003的的学生的信息。学生的信息。EXECUTEprocGetStudent0602001;2023年1月22日第15页7.1.3存储过程的创建与执行存储过程的创建与执行3存存储过储过程的程的执执行行 EXECUTE语语句句执执行存行存储过储过程的程的语语法如下：法如下：EXECUTE 返回状返回状态态=存存储过储过程名程名;编编号号|存存储过储过程名称程名

14、称变变量量 参数参数=值值|变变量量OUTPUT|DEFAULT ,.n WITH RECOMPILE2023年1月22日第16页7.1.3存储过程的创建与执行存储过程的创建与执行【例例7.1】使用使用T-SQL语句创建一个带多个参数的存储过程语句创建一个带多个参数的存储过程-创建存储过程增加创建存储过程增加Courses表数据的过程表数据的过程)的的T-SQL语句如下：语句如下：CREATEPROCprocAddCourseCnoCHAR(3),CnameVARCHAR(20),PreCnoCHAR(3),CreditTINYINTASINSERTINTOCourses(Cno,Cname,

15、PreCno,Credit)VALUES(Cno,Cname,PreCno,Credit)-执行存储过程的执行存储过程的T-SQL语句如下：语句如下：EXECprocAddCourseCno=C5,Cname=C#程序设计程序设计,PreCno=C2,Credit=4或或EXECprocAddCourseC5,C#程序设计程序设计,C2,42023年1月22日第17页7.1.3存储过程的创建与执行存储过程的创建与执行【例例7.2】创建带有通配符参数的存储过程。存储过程从学生表中返回指定些学创建带有通配符参数的存储过程。存储过程从学生表中返回指定些学生生(提供名字和姓氏提供名字和姓氏)的信息的信

16、息.CREATEPROCEDUREprocGetStudentByNamenamechar(8)=%ASSELECT*FROMStudentsWHERESnameLIKERTRIM(name);部分执行的方法如下：部分执行的方法如下：EXECUTEprocGetStudentByName;-返回所有学生的信息返回所有学生的信息或者或者EXECUTEprocGetStudentByName吴吴%;-返回所有姓吴的学生信息返回所有姓吴的学生信息或者或者EXECUTEprocGetStudentByName王冲瑞王冲瑞-返回王冲瑞同学的信息返回王冲瑞同学的信息2023年1月22日第18页7.1.3存

17、储过程的创建与执行存储过程的创建与执行【例例7.3】使用使用T-SQL语句创建带语句创建带Output参数输出的存储过程参数输出的存储过程,下面存储过程返回下面存储过程返回参数给定学号的学生的平均成绩。参数给定学号的学生的平均成绩。CREATEPROCEDUREprocGetAvgGradeByNumSnochar(7),avgGradeSMALLINTOUTPUTASSELECTavgGrade=AVG(Grade)FROMSCWHERESno=Sno;-通过以下代码执行，得到该学生的平均成绩。通过以下代码执行，得到该学生的平均成绩。DECLAREaverageGradeSMALLINTEX

18、ECprocGetAvgGradeByNum0602001,averageGradeOUTPUTPRINT0602001的平均成绩为的平均成绩为+STR(averageGrade)2023年1月22日第19页7.1.3存储过程的创建与执行存储过程的创建与执行4存储过程的设计规则存储过程定义中不能包括的语句如下：存储过程定义中不能包括的语句如下：CREATEAGGREGATE、CREATEDEFAULT、CREATEFUNCTION、CREATEPROCEDURE、CREATERULE、CREATESCHEMA、CREATETRIGGER、CREATEVIEW、SET_PARSEONLY、SET

19、SHOWPLAN_TEXT、SETSHOWPLAN_ALL、SETSHOWPLAN_XML、USEdatabase_name在创建存储过程时，应该考虑下列几个事项：在创建存储过程时，应该考虑下列几个事项：(1)不能将不能将CREATEPROCEDURE语句与其他语句与其他SQL语句组合到单个批处理中。语句组合到单个批处理中。(2)创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户.(3)存储过程是数据库对象，其名称必须遵守标识符规则；其参数的最大数目为存储过程是数据库对象，其名称必须遵守标识符规则；其参数

20、的最大数目为2100(4)存储过程是架构作用域中的对象，只能在本地数据库中创建存储过程。存储过程是架构作用域中的对象，只能在本地数据库中创建存储过程。2023年1月22日第20页7.1.3存储过程的创建与执行存储过程的创建与执行(5)数据库对象均可在存储过程中创建。可以引用在同一存储过程中创建的对象，数据库对象均可在存储过程中创建。可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。只要引用时已经创建了该对象即可。(6)可以在存储过程内引用临时表。如果在存储过程内创建本地临时表，则临时可以在存储过程内引用临时表。如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出

21、该存储过程后，临时表将消失。表仅为该存储过程而存在；退出该存储过程后，临时表将消失。(7)如果执行的存储过程调用另一个存储过程，则被调用的存储过程可以访问由如果执行的存储过程调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。第一个存储过程创建的所有对象，包括临时表在内。(8)如果执行对远程如果执行对远程SQLServer实例进行更改的远程存储过程，则不能回滚这些实例进行更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。更改。远程存储过程不参与事务处理。(9)存储过程中的局部变量的最大数目仅受可用内存的限制存储过程中的局部变量的最大数

22、目仅受可用内存的限制;根据可用内存的不同，根据可用内存的不同，存储过程最大可达存储过程最大可达128MB。2023年1月22日第21页7.1.3存储过程的创建与执行存储过程的创建与执行(10)在存储过程内，如果用于语句在存储过程内，如果用于语句(例如例如SELECT或或INSERT)的对象名称没有限定架构，的对象名称没有限定架构，该架构将默认为该存储过程所在的架构。在存储过程内，如果创建该存储过程的该架构将默认为该存储过程所在的架构。在存储过程内，如果创建该存储过程的用户没有限定用户没有限定SELECT、INSERT、UPDATE和和DELETE语句中引用的表名或视图语句中引用的表名或视图名，

23、在默认情况下，通过该存储过程对这些表或视图进行的访问将受到该过程创名，在默认情况下，通过该存储过程对这些表或视图进行的访问将受到该过程创建者的权限的限制。建者的权限的限制。(11)如果希望其他用户无法查看存储过程的定义，则可以使用如果希望其他用户无法查看存储过程的定义，则可以使用WITHENCRYPTION子子句创建存储过程。这样，过程定义将以不可读的形式存储。句创建存储过程。这样，过程定义将以不可读的形式存储。(12)不要以不要以sp_为前缀创建任何存储过程。为前缀创建任何存储过程。Sp_前缀是前缀是SQLServer用来命名系统存储用来命名系统存储过程的，使用这样的名称可能会与以后的某些系

24、统存储过程发生冲突。过程的，使用这样的名称可能会与以后的某些系统存储过程发生冲突。2023年1月22日第22页7.1.4存储过程的查看、修改和删除存储过程的查看、修改和删除1查看存储过程1)使用使用SQLServerManagementStudio查看用户创建的存储过程查看用户创建的存储过程展开展开【可程性可程性】|【存储过程存储过程】节点，右击要查看的存储过程名称，如图节点，右击要查看的存储过程名称，如图7-9所所示。从弹出的快捷菜单中执行示。从弹出的快捷菜单中执行【编写存储过程脚本为编写存储过程脚本为】|【CREATE到到】|【新新查询编辑器窗口查询编辑器窗口】命令，即可看到存储过程的源代

25、码。命令，即可看到存储过程的源代码。图7-9查看存储过程2023年1月22日第23页7.1.4存储过程的查看、修改和删除存储过程的查看、修改和删除2)使用系统存储过程来查看用户创建的存储过程使用系统存储过程来查看用户创建的存储过程（1）利用系统存储过程）利用系统存储过程sp_help查看存储过程的参数及其数据类型等信息。查看存储过程的参数及其数据类型等信息。EXECsp_helpprocGetStudent（2）利用系统存储过程）利用系统存储过程sp_helptext查看存储过程的定义，但是不能查看被加密的查看存储过程的定义，但是不能查看被加密的存储过程。存储过程。EXECsp_helptex

26、tprocGetStudent（3）利用系统存储过程）利用系统存储过程sp_depends查看依赖于存储过程的对象。查看依赖于存储过程的对象。EXECsp_dependsprocGetStudent（4）从系统对象表中查看指定的存储过程。）从系统对象表中查看指定的存储过程。Select*fromsysobjectswherename=procGetStudentandtype=p（5）利用系统存储过程）利用系统存储过程sp_stored_procedures返回当前数据库中的存储过程列返回当前数据库中的存储过程列表表sp_stored_proceduresprocGetStudent2023年

27、1月22日第24页7.1.4存储过程的查看、修改和删除存储过程的查看、修改和删除2修改存储过程修改存储过程 (1)T-SQL修改存储过程简单语法格式为：ALTER PROCEDURE存储过程名称 其语法过程类似创建存储过程的语法，只是将关键词“CREATE”改为“ALTER”。但要注意的是它保持存储过程的权限不发生变化。（2）使用SQL Server Management Studio也可以很方便地修改存储过程的定义。展开【可编程性】|【存储过程】节点，右击要修改的存储过程，在弹出的快捷菜单中执行【修改】命令，打开修改存储过程窗口。在该窗口中可以直接修改定义该存储过程的T-SQL语句，然后单击

28、【执行】按钮执行该存储过程的修改。2023年1月22日第25页7.1.4存储过程的查看、修改和删除存储过程的查看、修改和删除3删除存储过程删除存储过程(1)T-SQL删除存储过程的简单语法格式如下：DROP PROCEDURE procedure_namen 删除存储过程前，要先通过使用“sp_depends”存储过程来查看是否有对象依赖于该存储过程。如：EXEC sp_depends procGetStudent -查看procGetStudent所依赖的对象 DROP PROCEDURE procGetStudent -删除存储过程procGetStudent（2）通过SQL Server

29、 Management Studio也可以很方便地删除存储过程。右击要删除的存储过程，从弹出的快捷菜单中执行【删除】命令，打开“删除对象”对话框，选中该存储过程，然后执行【确定】命令即可。2023年1月22日第26页7.2触发器触发器7.2.1 触发器概述7.2.2 使用DML 触发器7.2.3 使用DDL 触发器7.2.4 查看、修改和删除触发器2023年1月22日第27页7.2.1触发器概述触发器概述触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程，主要触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程，主要是通过事件进行触发而被执打厅的，而存储过程可以通过存储过程名

30、字而被直接是通过事件进行触发而被执打厅的，而存储过程可以通过存储过程名字而被直接调用。调用。1触发器的作用触发器的主要作用是能实现由主键和外键所不能保证的复杂的参照完整性和触发器的主要作用是能实现由主键和外键所不能保证的复杂的参照完整性和数据一致性，除此之外，触发器还有其他许多不同的功能。数据一致性，除此之外，触发器还有其他许多不同的功能。(1)可以调用存储过程可以调用存储过程(2)跟踪变化跟踪变化(3)可以强化数据条件约束可以强化数据条件约束(4)级联和并行运行级联和并行运行2023年1月22日第28页7.2.1触发器概述触发器概述2触发器的类型触发器的类型SQLServer2005分成两大

31、类触发器：分成两大类触发器：DML触发器和触发器和DDL触发器。触发器。DML触发器可以在数据库中数据被修改时被执行。触发器可以在数据库中数据被修改时被执行。DML事件包括在指定表事件包括在指定表或视图中修改数据的或视图中修改数据的INSERT语句、语句、UPDATE语句或语句或DELETE语句。系统将触发语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误(例例如，磁盘空间不足如，磁盘空间不足)，则整个事务自动回滚。，则整个事务自动回滚。DDL触发器是触发器是MicrosoftSQLServer2

32、005的新增功能。当服务器或数据库的新增功能。当服务器或数据库中发生数据定义语言中发生数据定义语言(DDL)事件时将调用这些触发器。事件时将调用这些触发器。DDL触发器与触发器与DML触发器触发器的相同之处在于都需要触发事件进行触发。但是，它与的相同之处在于都需要触发事件进行触发。但是，它与DML触发器不同的是，它触发器不同的是，它响应多种数据定义语言（响应多种数据定义语言（DDL)语句而触发这些语句主要是以语句而触发这些语句主要是以CREATE,ALTER和和DROP等关键字开头的语句。等关键字开头的语句。在在MicrosoftSQLServer2005系统中，也可以创建系统中，也可以创建C

33、LR触发器。触发器。CLR触发触发器既可以是器既可以是DML触发器，也可以是触发器，也可以是DDL触发器。触发器。2023年1月22日第29页7.2.2使用使用DML触发器触发器DML触发器在以下方面非常有用：触发器在以下方面非常有用：(1)DML触发器可通过数据库中的相关表实现级联更改。不过，通过级联引用触发器可通过数据库中的相关表实现级联更改。不过，通过级联引用完整性约束可以更有效地进行这些更改。完整性约束可以更有效地进行这些更改。(2)DML触发器可以防止恶意或错误的触发器可以防止恶意或错误的INSERT、UPDATE以及以及DELETE操操作，并强制执行比作，并强制执行比CHECK约束

34、定义的限制更为复杂的其他限制。约束定义的限制更为复杂的其他限制。(3)DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施。触发器可以评估数据修改前后表的状态，并根据该差异采取措施。(4)一个表中的多个同类一个表中的多个同类DML触发器（触发器（INSERT、UPDATE或或DELETE）允许）允许采取多个不同的操作来响应同一个修改语句。采取多个不同的操作来响应同一个修改语句。DML触发器依据触发事件的操作时间分为触发器依据触发事件的操作时间分为AFTER触发器触发器和和INSTEADOF触发触发器器两种。两种。如果触发器表存在约束，则在如果触发器表存在约束，则在INSTEADOF触

35、发器执行之后和触发器执行之后和AFTER触发触发器执行之前检查这些约束。如果违反了约束，则将回滚器执行之前检查这些约束。如果违反了约束，则将回滚INSTEADOF触发器操触发器操作，并且不激活作，并且不激活AFTER触发器。触发器。2023年1月22日第30页7.2.2使用使用DML触发器触发器1在在SQLServerManagementStudio中创建中创建DML触发器触发器(1)展开要建立展开要建立DML触发器的的表，右击触发器的的表，右击【触发器触发器】,在弹出的快捷菜单上执行在弹出的快捷菜单上执行【新建触发器新建触发器】。(2)在新打开的在新打开的SQL命令窗口中，给出了创建存储过程

36、命令的模板，修改该模板，命令窗口中，给出了创建存储过程命令的模板，修改该模板，输入创建触发器的输入创建触发器的T-SQL语句。语句。CREATETRIGGERtriSCInsert-定义触发器所附着的表的名称定义触发器所附着的表的名称“SC”ONSC-定义触发器的类型定义触发器的类型FORINSERT-定义触发器的类型定义触发器的类型AS-下面是触发条件和触发器执行时要进行的操作下面是触发条件和触发器执行时要进行的操作BEGINDECLARESnochar(7)SELECTSno=inserted.SnoFROMinsertedIFNOTEXISTS(SELECTSnoFROMStudents

37、WHERESno=Sno)PRINT学生表中没有该学号的同学学生表中没有该学号的同学!ROLLBACKTRANSACTIONEND2023年1月22日第31页7.2.2使用使用DML触发器触发器(4)当向表当向表SC向插入数据向插入数据(FORINSERT)时，新建立的时，新建立的triStudentsInsert触触发器就会执行，如图发器就会执行，如图7-15所示。所示。InsertIntoSCValues(0703002,C1,84)图7-15 插入数据引发触发器2023年1月22日第32页7.2.2使用使用DML触发器触发器2用T-SQL命令窗口创建DML触发器简单语法规则如下：简单语法

38、规则如下：CREATETRIGGER架构名架构名.DML触发器名称触发器名称On表名表名|视图名视图名WITHENCRYPTION|EXECUTEASClause,.nFOR|AFTER|INSTEADOFINSERT，UPDATE，DELETEWITHAPPENDNOTFORREPLICATIONASSQL语句语句;n|EXTERNALNAMEassembly_name.class_name.method_name;2023年1月22日第33页7.2.2使用使用DML触发器触发器用用T-SQL命令创建命令创建DML触发器的步骤如下：触发器的步骤如下：(1)打开打开SQLServerManag

39、ementStudio，单击，单击“新建查询新建查询”按钮打开按钮打开SQL命令窗命令窗口，输入建立口，输入建立DML触发器的代码后执行。如图触发器的代码后执行。如图7-17所示。所示。CREATETRIGGERtriSCUpdate-定义名称为定义名称为triSCUpdate的触发器的触发器ONSC-定义触发器所附着的表的名称定义触发器所附着的表的名称“SC”FORUPDATE-定义触发器的类型定义触发器的类型ASIFUPDATE(Grade)-对分数列的更改对分数列的更改BEGINRAISERROR(不能修改课程分数不能修改课程分数,16,10)ROLLBACKTRANSACTIONEND

40、该触发器防止用户修改该触发器防止用户修改“SC”表的分数。表的分数。2023年1月22日第34页7.2.2使用使用DML触发器触发器(2)执行如下语句引发执行如下语句引发triSCUpdate的执行，如图的执行，如图7-18所示。所示。UPDATESCSETGrade=80WHERESno=06001ANDCno=c1图7-17 创建triSCUpdate触发器图7-18 执行triSCUpdate触发器2023年1月22日第35页7.2.2使用使用DML触发器触发器【例例7.4】创建一个创建一个DELETE触发器。当删除触发器。当删除“学生表学生表”中的记录时，自动删除中的记录时，自动删除“

41、成绩成绩”表中对应学号的记录。表中对应学号的记录。CREATETRIGGERtriStudentDelete-定义名称为定义名称为triStudentDelete的触发器的触发器ONStudents-定义触发器所附着的表的名称定义触发器所附着的表的名称StudentsFORDELETE-定义触发器的类型定义触发器的类型ASBEGINDECLARESnoCHAR(7)SELECTSno=deleted.SnoFROMdeletedDELETESCWHERESno=SnoEND-删除数据验证触发器删除数据验证触发器DELETEFROMStudentsWHERESno=07030012023年1月2

42、2日第36页7.2.2使用使用DML触发器触发器3.DML触发器的使用限制存储过程定义中的不能包括的特殊语句如下：存储过程定义中的不能包括的特殊语句如下：ALTERDATABASE,CREATEDATABASE,DROPDATABASE,RECONFIGURE,LOADLOG,LOADDATABASE,RESTORELOG,RESTOREDATABASE。此外，在使用此外，在使用DML创建触发器时，应该考虑下列几个事项：创建触发器时，应该考虑下列几个事项：(1)CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。必须是批处理中的第一条语句，并且只能应用到一个表中。(2

43、)触发器只能在当前的数据库中创建，但可以引用当前数据库的外部对象，如其他触发器只能在当前的数据库中创建，但可以引用当前数据库的外部对象，如其他表。不能在系统表上创建用户自定义的触发器。表。不能在系统表上创建用户自定义的触发器。(3)在同一条在同一条CREATETRIGGER语句中，可以为多个事件语句中，可以为多个事件(如如INSERT、UPDATE、DELETE)定义相同的触发器操作。定义相同的触发器操作。2023年1月22日第37页7.2.3使用使用DDL触发器触发器(4)如果一个表的外键在如果一个表的外键在DELETEUPDATE操作上定义了级联，则不能在该操作上定义了级联，则不能在该表上

44、定义表上定义INSTEADOFDELETEUPDATE触发器。触发器。(5)与使用存储过程一样，当触发器激发时，将向调用的应用程序返回结果。与使用存储过程一样，当触发器激发时，将向调用的应用程序返回结果。若要避免由于触发器激发而向应用程序返回结果，则不要包含返回结果的若要避免由于触发器激发而向应用程序返回结果，则不要包含返回结果的SELECT语句，也不要在触发器中包含对变量赋值的语句。语句，也不要在触发器中包含对变量赋值的语句。(6)在触发器内可以指定任意的在触发器内可以指定任意的SET语句。选择的语句。选择的SET选项在触发器执行选项在触发器执行期间保持有效，然后恢复为原来的设置。期间保持有

45、效，然后恢复为原来的设置。2023年1月22日第38页7.2.3使用使用DDL触发器触发器DDL触发器响应多种数据定义语言触发器响应多种数据定义语言(DDL)语句而被触发。语句而被触发。DDL触发器一般用于执行触发器一般用于执行以下操作：以下操作：(1)防止对数据库架构进行某些更改。防止对数据库架构进行某些更改。(2)希望数据库中发生某种情况以响应数据库架构中的更改。希望数据库中发生某种情况以响应数据库架构中的更改。(3)要记录数据库架构中的更改或事件。要记录数据库架构中的更改或事件。仅在运行触发仅在运行触发DDL触发器的触发器的DDL语句后，语句后，DDL触发器才会被触发。触发器才会被触发。

46、DDL触发器无法触发器无法作为作为INSTEADOF触发器使用。不支持执行类似触发器使用。不支持执行类似DDL操作的系统存储过程。操作的系统存储过程。用户可以设计在运行一个或多个特定用户可以设计在运行一个或多个特定T-SQL语句后触发的语句后触发的DDL触发器，也可以设计触发器，也可以设计在执行属于一组预定义的相似事件的任何在执行属于一组预定义的相似事件的任何T-SQL事件后触发的事件后触发的DDL触发器。触发器。CREATE DATABASECREATE DATABASE事件不能用于事件不能用于DDLDDL触发器中。触发器中。2023年1月22日第39页7.2.3使用使用DDL触发器触发器1

47、创建创建DDL触发器触发器DDL触发器只能使用触发器只能使用T-SQL命令来创建。创建命令来创建。创建DDL触发器的语法格触发器的语法格式如下：式如下：CREATETRIGGER触发器名触发器名ONALLSERVER|DATABASEWITHENCRYPTION|EXECUTEAS，nFOR|AFTERevent_type|event_group，nASSQL语句语句;n|EXTERNALNAMEassembly_name.class_name.method_name;2023年1月22日第40页7.2.3使用使用DDL触发器触发器【例例7.5】使用使用DDL触发器来防止触发器来防止Stude

48、ntCourse数据库中的任一表被删除数据库中的任一表被删除或修改或修改CREATETRIGGERsafetyDBONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINT禁止删除或修改数据库中的表禁止删除或修改数据库中的表ROLLBACK;2023年1月22日第41页7.2.3使用使用DDL触发器触发器创建该触发器成功后，展开创建该触发器成功后，展开“对象资源管理器对象资源管理器”中的中的【数据库数据库】|【StudentCourse】|【可编程性可编程性】|【数据库触发器数据库触发器】就可以看到新建立的触发器，如就可以看到新建立的触发器，如图图7-19所示。所示。

49、当任一用户试图在数据库中当任一用户试图在数据库中修改表的结构或删除表时，都会修改表的结构或删除表时，都会触发触发safetyDB触发器。该触发器触发器。该触发器显示提示信息，并且回滚用户试显示提示信息，并且回滚用户试图执行的操作。图执行的操作。图7-19 新创建的DDL触发器2023年1月22日第42页7.2.4查看、修改和删除触发器查看、修改和删除触发器1查看触发器(1)使用使用SQLServerManagementStudio中查看触发器信息中查看触发器信息展开展开“对象资源管理器对象资源管理器”中的中的【指定数据库指定数据库】|【表表】|【触发器触发器】选项，右击选项，右击要查看的触发器

50、名称，从弹出的快捷菜单中执行要查看的触发器名称，从弹出的快捷菜单中执行【编写触发器脚本为编写触发器脚本为】|【CREATE到到】|【新查询编辑器窗口新查询编辑器窗口】命令。命令。查看数据库范围的查看数据库范围的DDL触发器，则是从触发器，则是从“对象资源管理器对象资源管理器”中的中的【指定数据指定数据库库】|【可编程性可编程性】|【数据库触发器数据库触发器】选项展开。选项展开。查看服务器作用域的查看服务器作用域的DDL触发器触发器，则是展开，则是展开【服务器对象服务器对象】|【触发器触发器】选选项。项。2023年1月22日第43页7.2.4查看、修改和删除触发器查看、修改和删除触发器(2)可以