第9章存储过程与触发器.ppt

学习目标：通过本章学习，你能够学会：1.了解存储过程与触发器的特性2.了解存储过程与触发器的优点3.应熟练掌握使用企业管理器创建、修改、删除和查看存储过程与与触发器的操作。4.应熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程与与触发器的操作。,第9章存储过程与触发器,存储过程与触发器是SQLServer中的两类数据库对象。它们都是由T-SQL语句编写而成的过程，所不同的是存储过程是由用户根据需要调用执行的，而触发器则是由某个动作(如删除或修改一条记录)引发执行的。另外，存储过程可以不依附于表而单独存在，而触发器则必须依附于一个特定的表。它们与函数也不同，函数可以直接通过函数名返回数值，其返回值可以直接在表达式中使用，而存储过程与触发器则不能直接通过其名称带回返回值，也不能直接在表达式中使用。,第9章存储过程与触发器,91存储过程概念实例9.1了解存储过程的执行过程，如图9.1所示。归纳分析：在SQLServer中定义的过程被称为存储过程。存储过程是一组预先编译好的T-SQL代码，作为一个整体用于执行特定的操作。存储过程属于数据库对象，它们存放在数据库中，需要时用户可以调用。,第9章存储过程与触发器,存储过程具有如下优点：1.存储过程将一系列复杂的T-SQL代码封装在一起作为数据库对象存放在数据库中，用户使用时不必接触T-SQL而仅需直接调用即可得到所需结果，简化了用户的操作。2.存储过程已经被编译，执行时省去了编译与优化的时间。另外，第一次从磁盘调用存储过程后，它将驻留在内存中，下一次使用时可以直接从内存中调用，因此对于需要多次调用的存储过程而言，速度明显加快。3.在分布式查询中，调用存储过程的语句将比直接使用T-SQL的语句少得多，这将大大减少网络流量。4.通过适当的权限设置，可以使系统的安全性得到更有效的保障。,第9章存储过程与触发器,92存储过程分类实例9.2查看系统存储过程。操作步骤：,第9章存储过程与触发器,归纳分析：SQLServer的存储过程分为三大类：1系统存储过程系统存储过程是SQLServer内置的存储过程，可以直接用于执行一些操作，用于进行系统管理、登录管理、权限设置、数据库对象管理、查看数据库信息、数据库复制等操作。系统存储过程存放在master数据库中，以sp_作为前缀。在任何数据库中无需用master限定就直接执行。,第9章存储过程与触发器,2临时存储过程临时存储过程与临时表类似。是对用户定义的存储过程。以#、或#开头的存储过程。无论用户在哪个数据库中创建，它们都存放在临时数据库tempdb中。以#开头的存储过程是局部临时存储过程，它仅能由其创建者本人在创建完后立即调用，一旦该创建者断开与数据库的连接，局部临时存储过程立即被删除。以#开头的存储过程是全局临时存储过程，它可以由创建者本人和其他用户在创建完后共同调用，一旦创建者断开与数据库的连接，则不再允许新的用户使用，而且等其他正在使用该存储过程的用户使用完毕后，全局临时存储过程也被删除。,第9章存储过程与触发器,3.扩展存储过程是利用高级语言(如C语言)编写的存储过程，是SQLServer可以动态装载并执行的动态链接库(DLL)。扩展存储过程只能添加到master数据库中。本章不介绍扩展存储过程有关内容，读者可以参考其他书籍有关内容。,第9章存储过程与触发器,93存储过程创建931使用企业管理器创建存储过程实例9.3在企业管理器中建立名为pro_new的存储过程，并执行。操作步骤：,第9章存储过程与触发器,归纳分析：如果是查看或修改已有存储过程的定义，则右击要查看或修改的存储过程，在弹出的快捷菜单中，选择“属性”命令，则调出“存储过程属性”窗口，在这里可以查看或修改存储过程的定义。,第9章存储过程与触发器,932使用T-SQL语句创建存储过程实例9.4在“学生”数据库中建立一个存储过程pro_new1，用于查询所有学生的学号和数学期中成绩、计算机期中成绩，要求数学期中成绩和计算机期中成绩分别列出。已知数学期中成绩的课程号是1002、计算机期中成绩的课程号是2005。操作步骤：,第9章存储过程与触发器,归纳分析：如果是查看或修改已有存储过程的定义，则右击要查看或修改的存储过程，在弹出的快捷菜单中，选择“属性”命令，则调出“存储过程属性”窗口，在这里可以查看或修改存储过程的定义。,第9章存储过程与触发器,932使用T-SQL语句创建存储过程实例9.4在“学生”数据库中建立一个存储过程pro_new1，用于查询所有学生的学号和数学期中成绩、计算机期中成绩，要求数学期中成绩和计算机期中成绩分别列出。已知数学期中成绩的课程号是1002、计算机期中成绩的课程号是2005。操作步骤：,第9章存储过程与触发器,归纳分析：在SQLServer中，使用CREATEPROCEDURE语句创建一个永久或临时存储过程。命令格式：CREATEPROCEDURE存储过程名WITHENCRYPTIONWITHRECOMPILEASSQL语句命令说明：（1）WITHENCRYPTION：对存储过程进行加密。（2）WITHRECOMPILE：对存储过程重新编译。,第9章存储过程与触发器,94执行存储过程实例9.5分别执行实例9.3、实例9.4建立存储过程。操作步骤：,第9章存储过程与触发器,归纳分析：注意：执行存储过程时，区分大小写。存储过程一旦编写好后，就可以调用执行。执行存储过程的语法如下。命令格式：EXECUTE存储过程名字符串变量|NSQL语句字符串+n,第9章存储过程与触发器,95修改存储过程951更改存储过程名称实例9.6将实例9.3所创建的名为pro_new存储过程，修改成名为pro_存储过程。操作步骤：（1）启动“查询分析器”，输入如下SQL语句：sp_renamepro_new,pro_存储过程（2）按“F5”键或单击工具栏“执行查询”图标。,第9章存储过程与触发器,归纳分析：只有数据库所有者和存储过程的所有者才有权修改存储过程名称。不宜轻易修改存储过程名称，因为系统中许多应用程序可能都要调用存储过程，如果修改了存储过程的名称，则这些应用程序都要随之改动。命令格式：sp_rename原存储过程名，新存储过程名修改存储过程名称的方法与修改表名称的方法类同，读者可以参照执行。使用企业管理器修改存储过程名称的方法也类似于修改表名称的方法，即展开指定的数据库节点，单击存储过程，在右边的窗口中选中一个需要修改名称的存储过程右击之，在弹出的快捷菜单中选择“重命名”。,第9章存储过程与触发器,952修改存储过程的参数与定义1.使用企业管理器修改存储过程实例9.7使用企业管理器修改存储过程实例9.4，增加显示课程号的列。操作步骤：,第9章存储过程与触发器,2.使用T-SQL语句修改存储过程实例9.8在实例9.4创建的存储过程，修改查询记录按名次排列。操作步骤：,第9章存储过程与触发器,归纳分析：修改已经创建的存储过程，可以不更改其用户的使用权限，也不更改其名称，因此不会破坏其他相关程序的引用关系。命令格式：ALTERPROCEDURE存储过程名WITHENCRYPTIONWITHRECOMPILEASSQL语句命令说明：其中各项语法含义与创建存储过程类似。,第9章存储过程与触发器,96删除存储过程961使用企业管理器删除存储过程实例9.9使用企业管理器删除存储过程。操作步骤：,第9章存储过程与触发器,存储过程与触发器是SQLServer中的两类数据库对象。它们都是由T-SQL语句编写而成的过程，所不同的是存储过程是由用户根据需要调用执行的，而触发器则是由某个动作(如删除或修改一条记录)引发执行的。另外，存储过程可以不依附于表而单独存在，而触发器则必须依附于一个特定的表。它们与函数也不同，函数可以直接通过函数名返回数值，其返回值可以直接在表达式中使用，而存储过程与触发器则不能直接通过其名称带回返回值，也不能直接在表达式中使用。,第9章存储过程与触发器,归纳分析：SQLServer的存储过程分为三大类：1系统存储过程系统存储过程是SQLServer内置的存储过程，可以直接用于执行一些操作，用于进行系统管理、登录管理、权限设置、数据库对象管理、查看数据库信息、数据库复制等操作。系统存储过程存放在master数据库中，以sp_作为前缀。在任何数据库中无需用master限定就直接执行。,第9章存储过程与触发器,归纳分析：如果是查看或修改已有存储过程的定义，则右击要查看或修改的存储过程，在弹出的快捷菜单中，选择“属性”命令，则调出“存储过程属性”窗口，在这里可以查看或修改存储过程的定义。,第9章存储过程与触发器,归纳分析：在SQLServer中，使用CREATEPROCEDURE语句创建一个永久或临时存储过程。命令格式：CREATEPROCEDURE存储过程名WITHENCRYPTIONWITHRECOMPILEASSQL语句命令说明：（1）WITHENCRYPTION：对存储过程进行加密。（2）WITHRECOMPILE：对存储过程重新编译。,第9章存储过程与触发器,归纳分析：只有数据库所有者和存储过程的所有者才有权修改存储过程名称。不宜轻易修改存储过程名称，因为系统中许多应用程序可能都要调用存储过程，如果修改了存储过程的名称，则这些应用程序都要随之改动。命令格式：sp_rename原存储过程名，新存储过程名修改存储过程名称的方法与修改表名称的方法类同，读者可以参照执行。使用企业管理器修改存储过程名称的方法也类似于修改表名称的方法，即展开指定的数据库节点，单击存储过程，在右边的窗口中选中一个需要修改名称的存储过程右击之，在弹出的快捷菜单中选择“重命名”。,第9章存储过程与触发器,96删除存储过程961使用企业管理器删除存储过程实例9.9使用企业管理器删除存储过程。操作步骤：,第9章存储过程与触发器,962使用T-SQL语言删除存储过程实例9.10删除Pro_new存储过程。操作步骤：（1）启动“查询分析器”，输入如下SQL语句：DROPPROCEDUREPro_new（2）按“F5”键或单击工具栏“执行查询”图标。归纳分析：不再需要存储过程时，可以利用企业管理器或T-SQL语言删除。命令格式：DROPPROCEDURE存储过程名称或存储过程组名称注意：删除存储过程组时不必写出下标，即可将组中所有成员全部删除。,第9章存储过程与触发器,97查看存储过程971使用企业管理器查看存储过程的信息实例9.11使用企业管理器查看存储过程的信息。操作步骤：在图9.4所示属性对话框中可以查询存储过程的名称、所有者、建立时间和文本信息。在图9.3所示窗口的右部选中一个存储过程并右击，在弹出的级联菜单中依次选择“所有任务”、“显示相关性”，系统会弹出“显示相关性”对话框，该对话框显示一个存储过程所引用的表及其列信息。,第9章存储过程与触发器,972使用T-SQL语言查看存储过程的信息实例9.12利用系统存储过程查询pro_存储过程的信息。操作步骤：,第9章存储过程与触发器,归纳分析：(1)查看定义存储过程的文本命令格式：sp_helptextobiname=存储过程名(2)查看存储过程的名称、所有者、建立时间命令格式：sp_helpobiname=存储过程名(3)查看存储过程所引用的表(或查看一个表被哪些存储过程所引用)命令格式：sp_dependsobiname=表名(4)查看一个存储过程引用了哪些表及其列：sp_dependsobiname=存储过程名,第9章存储过程与触发器,归纳分析：(1)查看定义存储过程的文本命令格式：sp_helptextobiname=存储过程名(2)查看存储过程的名称、所有者、建立时间命令格式：sp_helpobiname=存储过程名(3)查看存储过程所引用的表(或查看一个表被哪些存储过程所引用)命令格式：sp_dependsobiname=表名(4)查看一个存储过程引用了哪些表及其列：sp_dependsobiname=存储过程名,第9章存储过程与触发器,命令格式：CREATEPROCEDURE存储过程名参数名数据类型=默认值WITHENENCRYPTIONWITHRERECOMPILEASSQL语句命令说明：“参数名”和定义局部变量一样，必须以符号为前缀，要指定数据类型，多个参数定义要用逗号“，”隔开。在执行存储过程时该参数将由指定的参数值来代替，如果执行时未提供该参数的参数值，则如果这里定义了默认值则使用该值作为执行时的参数值，默认值可以是常量或空(NULL)。,第9章存储过程与触发器,99创建触发器SQLServer2000提供了两种主要机制用于维护数据的完整性：一种是第8章介绍的约束，另一种就是本章将要介绍的触发器。触发器虽然是一种特殊的存储过程，但是它与表却是紧密联系的，离开了表它将不复存在(这点与约束十分类似)。触发器又不像存储过程那样需要调用才能执行，而是在对表中数据进行增、删、改(INSERT、UPDATE、DELETE)操作时被触发而执行。触发器可以包含复杂的T-SQL语句。在触发器内设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。,第9章存储过程与触发器,992使用T-SQL语句创建触发器实例9.16建立一个触发器，防止用户在要删除表学生信息中的一条数据时，由于没有使用限制条件(误用SELECTFROM学生信息语句)而导致删除全部数据的情况发生。操作步骤：,第9章存储过程与触发器,(4)INSERT，UPDATE，DELETE：指定在表或视图上执行哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。(5)IFUPDATE(列名)(AND|OR)UPDATE(列名)n：判定某一列或几列，是否被修改。如果希望仅当某一列(或几列)的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器执行，则可以采用本选项。此时可以称之为列级触发器。,第9章存储过程与触发器,归纳分析：SQLServer2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句(原有语句没有执行，仅执行新语句)，称之为INSTEADOF触发器：另一类是在引起触发器执行的语句执行完毕后，再执行触发器中的语句，称之为AFTER类触发器。(1)INSTEADOF触发器既可以在表上定义，也可以在视图上定义。该类触发器依据其触发动作(UPDATE、DELETE和INSERT)的不同分为INSTEADOFINSERT、INSTEADOFUPDATE和INSTEADOFDELETE三种，被引发执行时将分别代替表或视图的INSERT、UPDATE、DELETE操作，亦即表中的增删改操作没有进行。每个触发动作仅可以定义一个INSTEADOF触发器。,第9章存储过程与触发器,9112修改触发器的定义实例9.21在企业管理器中修改实例9.17将删除表改为添加记录。操作步骤：（3）单击检查语法按钮，进行语法检查，成功后，可插入数据检验触发器的效果。,第9章存储过程与触发器,4.应熟练掌握使用企业管理器创建、修改、删除和查看存储过程的操作。5.应熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程的操作。6.应掌握使用T-SQL语句带参数和变量创建和修改存储过程，及如何执行存储过程。7.了解触发器在对表中数据进行增、删、改(INSERT、UPDATE、DELETE)操作时被触发而执行。触发器可以包含复杂的T-SQL语句。在触发器内设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。8.触发器的优点：(1)触发器可以实现外键约束的功能，实现对表的级连修改。,第9章存储过程与触发器,9.15.2实训1实训目的(1)熟练掌握用T-SQL语句存储过程与触发器所需的记录。(2)熟练掌握用T-SQL语句编写的操作过程。(3)熟练掌握存储过程与触发器的表示方法。(4)熟练掌握使用企业管理器创建、修改、删除和查看存储过程与与触发器的操作。(5)熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程与与触发器的操作。2实训环境SQLServer2000的运行、管理环境。,第9章存储过程与触发器,98存储过程编程981参数和变量实例9.13创建的存储过程，修改成当输入学生学号时可以查询某个学生的成绩：不输入学号时，则查询全部学生的成绩。操作步骤：,第9章存储过程与触发器,（3）本例定义了一个输入参数stuID用于传送学号，其默认值为空。执行存储过程时，如果指定了该输入参数的值，则按照该参数所给的学号查询指定学生的成绩，如果参数stuID的值为空，则查询所有学生成绩。输入如图9.8所示上部分程序，查看运行结果，如图9.8所示下部分。,第9章存储过程与触发器,归纳分析：1.建立带输入参数的存储过程向存储过程指定输入、输出参数的主要目的是通过参数向存储过程输入和输出信息来扩展存储过程的功能。通过使用参数，可以多次使用同一存储过程并按用户要求查找所需要的结果。如实训9.13所示，没有参数就缺少灵活性。一个存储过程可以带一个或多个输入参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。,第9章存储过程与触发器,命令格式：CREATEPROCEDURE存储过程名参数名数据类型=默认值WITHENENCRYPTIONWITHRERECOMPILEASSQL语句命令说明：“参数名”和定义局部变量一样，必须以符号为前缀，要指定数据类型，多个参数定义要用逗号“，”隔开。在执行存储过程时该参数将由指定的参数值来代替，如果执行时未提供该参数的参数值，则如果这里定义了默认值则使用该值作为执行时的参数值，默认值可以是常量或空(NULL)。,第9章存储过程与触发器,2执行带参数的存储过程在执行存储过程的语句中，有两种方式来传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。使用参数名传递参数值，是通过语句“参数名：参数值”给参数传递值。当存储过程含有多个输入参数时，参数值可以按任意顺序制定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。执行使用参数名传递参数值的存储过程的命令格式：EXECUTE存储过程名参数名=参数值按参数位置传递参数值，不显式地给出“参数名”，而是按照参数定义的顺序给出参数值。按位置传递参数时，也可以忽略允许空值和具有默认值的参数，但不能因此破坏输入参数的指定顺序。必要时，使用关键字“DEFAULT”作为默认值的占位。,第9章存储过程与触发器,982RETURN语句和错误处理实例9.14在学生成绩表基础上建立一个存储过程pro_new3，要求：未输入学生学号时打印一个字符串“请输入学号”，并返回数字1；输入的学号查询不到时打印一个字符串“没有您输入的学号，请重新输入”，并返回数字2：输入学生学号正确时，查询该学号对应的期中成绩(课程号为1002)考试成绩。操作步骤：,第9章存储过程与触发器,归纳分析：用户可以通过RETUEN语句返回状态值，RETURN语句只能返回整数，在存储过程中RETURN不能返回空值，默认返回值是0。也可以利用它返回整数输出参数值。,第9章存储过程与触发器,99创建触发器SQLServer2000提供了两种主要机制用于维护数据的完整性：一种是第8章介绍的约束，另一种就是本章将要介绍的触发器。触发器虽然是一种特殊的存储过程，但是它与表却是紧密联系的，离开了表它将不复存在(这点与约束十分类似)。触发器又不像存储过程那样需要调用才能执行，而是在对表中数据进行增、删、改(INSERT、UPDATE、DELETE)操作时被触发而执行。触发器可以包含复杂的T-SQL语句。在触发器内设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。,第9章存储过程与触发器,991使用企业管理器创建触发器实例9.15在表学生成绩中建立一个触发器，当向表中插入一条记录时，检查该学生的学号是否存在于表学生信息中，如果存在，则插入；否则，打印“学号不存在”，并回滚事务，使插入数据无效。操作步骤：,第9章存储过程与触发器,归纳分析：创建触发器之前要注意如下几点：(1)创建触发器所使用的语句CREATETRIGGER必须是批处理中的第一个语句。(2)只有表的所有者、sysadmin固定服务器角色成员以及db_owner和曲_ddladmin固定数据库角色成员有权在本表上创建触发器，且不能将该权限授予其他用户。(3)触发器为数据库对象，其命名规则必须与标识符命名规则一致。(4)只能在当前数据库中创建触发器，但是触发器可以引用其他数据库的对象。(5)不能在临时表或系统表上创建触发器。触发器可以引用临时表，但不能引用系统表。(6)尽管TRUNCATETABLE语句用于删除表中所有记录，但由于它不写入日志，故不能引发DELETE触发器。,第9章存储过程与触发器,(7)WRITETEXT语句不会引发INSERT或UPDATE触发器。(8)如果一个触发器中含有回滚事务语句RULLBACKTRANSACTION，且引发触发器的语句位于一个事务中，则该触发器触发时将回滚事务。(9)在触发器内不能使用的命令有：CREATE、ALTER、GRANT、REVOKE、TRUNCATETABLE、DROP等。(10)如果一个触发器中含有回滚事务语句RULLBACKTRANSACTION，且引发该触发器的语句位于一个批中，则一旦触发器执行，该批中引发触发器执行的语句之后的所有语句将不会执行。,第9章存储过程与触发器,992使用T-SQL语句创建触发器实例9.16建立一个触发器，防止用户在要删除表学生信息中的一条数据时，由于没有使用限制条件(误用SELECTFROM学生信息语句)而导致删除全部数据的情况发生。操作步骤：,第9章存储过程与触发器,（3）本例建立了一个AFIER触发器(仅给出FOR表示默认为AFTER型触发器)用于DELETE操作，首先声明一个局部变量用以存放Deleted表中数据行数。当对学生信息表执行删除操作时，检查删除的行数(Deleted表中数据行数)，如果行数1，则认为操作错误，打印提示信息并回滚事务，使删除无效。但是如果使用TRUNCATETABLE学生信息清除表中所有数据，则触发器不会触发，可以成功删除全部数据。,第9章存储过程与触发器,归纳分析：使用T-SQL语句创建触发器基本语法如下。命令格式：CREATETRIGGER触发器名ON表|视图WITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT，UPDATE，DELETENOTFORREPLICATIONASIFUPDATE(列名)(AND|OR)UPDATE(列名)nSQL语句,第9章存储过程与触发器,命令说明：(1)触发器的名称，必须符合标识符规则，并且必须在数据库中惟一。(2)WITHENCRYPTION用于加密CREATETRIGGER语句文本的条目。(3)FOR：用以指定触发器的类型，该关键字可以省略。后面为AFTER时表示触发器为AFTER类型，后面为INSTEADOF时表示触发器为INSTEADOF类型。如果仅指定FOR关键字，则默认为AFTER触发器。,第9章存储过程与触发器,(4)INSERT，UPDATE，DELETE：指定在表或视图上执行哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。(5)IFUPDATE(列名)(AND|OR)UPDATE(列名)n：判定某一列或几列，是否被修改。如果希望仅当某一列(或几列)的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器执行，则可以采用本选项。此时可以称之为列级触发器。,第9章存储过程与触发器,(4)INSERT，UPDATE，DELETE：指定在表或视图上执行哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。(5)IFUPDATE(列名)(AND|OR)UPDATE(列名)n：判定某一列或几列，是否被修改。如果希望仅当某一列(或几列)的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器执行，则可以采用本选项。此时可以称之为列级触发器。,第9章存储过程与触发器,UPDATE操作相当于先执行DELETE操作，然后再执行INSERT操作，因此要用到Deleted与Inserted两个表。,第9章存储过程与触发器,归纳分析：SQLServer2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句(原有语句没有执行，仅执行新语句)，称之为INSTEADOF触发器：另一类是在引起触发器执行的语句执行完毕后，再执行触发器中的语句，称之为AFTER类触发器。(1)INSTEADOF触发器既可以在表上定义，也可以在视图上定义。该类触发器依据其触发动作(UPDATE、DELETE和INSERT)的不同分为INSTEADOFINSERT、INSTEADOFUPDATE和INSTEADOFDELETE三种，被引发执行时将分别代替表或视图的INSERT、UPDATE、DELETE操作，亦即表中的增删改操作没有进行。每个触发动作仅可以定义一个INSTEADOF触发器。,第9章存储过程与触发器,9112修改触发器的定义实例9.21在企业管理器中修改实例9.17将删除表改为添加记录。操作步骤：（3）单击检查语法按钮，进行语法检查，成功后，可插入数据检验触发器的效果。,第9章存储过程与触发器,912删除触发器实例9.23删除不用的触发器名。操作步骤：,第9章存储过程与触发器,归纳分析：触发器创建以后，可以使用系统存储过程浏览触发器的有关信息。(1)显示触发器对象的类型。创建时间、所有者信息。sp_help触发器名(2)显示触发器的定义文本。如果触发器使用了加密(WITHENCRYPTION选项)，则无法看到触发器的代码文本。sp_helptext触发器名,第9章存储过程与触发器,4.应熟练掌握使用企业管理器创建、修改、删除和查看存储过程的操作。5.应熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程的操作。6.应掌握使用T-SQL语句带参数和变量创建和修改存储过程，及如何执行存储过程。7.了解触发器在对表中数据进行增、删、改(INSERT、UPDATE、DELETE)操作时被触发而执行。触发器可以包含复杂的T-SQL语句。在触发器内设置回滚，可以将触发它的语句所在的事务全部回滚，这样检测到错误时可以回滚事务以撤销错误。8.触发器的优点：(1)触发器可以实现外键约束的功能，实现对表的级连修改。,第9章存储过程与触发器,9.15.2实训1实训目的(1)熟练掌握用T-SQL语句存储过程与触发器所需的记录。(2)熟练掌握用T-SQL语句编写的操作过程。(3)熟练掌握存储过程与触发器的表示方法。(4)熟练掌握使用企业管理器创建、修改、删除和查看存储过程与与触发器的操作。(5)熟练掌握使用T-SQL语句创建、修改、删除和查看存储过程，及如何执行存储过程与与触发器的操作。2实训环境SQLServer2000的运行、管理环境。,第9章存储过程与触发器,910触发器介绍触发器具有如下优点：(1)触发器可以实现外键约束的功能，实现对表的级连修改。(2)触发器可以实现核查约束的功能，而且其功能更为强大。它可以使用另一个表的内容来约束触发器所在表。(3)如果对表中数据进行了增、删、改的操作，使用触发器可以根据修改前后的差异，采取相应的对策。(4)允许在一个表中针对不同的增删改操作设置多个触发器，以完成各类不同任务。,第9章存储过程与触发器,存储过程与触发器是SQLServer中的两类数据库对象。它们都是由T-SQL语句编写而成的过程，所不同的是存储过程是由用户根据需要调用执行的，而触发器则是由某个动作(如删除或修改一条记录)引发执行的。另外，存储过程可以不依附于表而单独存在，而触发器则必须依附于一个特定的表。它们与函数也不同，函数可以直接通过函数名返回数值，其返回值可以直接在表达式中使用，而存储过程与触发器则不能直接通过其名称带回返回值，也不能直接在表达式中使用。,第9章存储过程与触发器,归纳分析：SQLServer的存储过程分为三大类：1系统存储过程系统存储过程是SQLServer内置的存储过程，可以直接用于执行一些操作，用于进行系统管理、登录管理、权限设置、数据库对象管理、查看数据库信息、数据库复制等操作。系统存储过程存放在master数据库中，以sp_作为前缀。在任何数据库中无需用master限定就直接执行。,第9章存储过程与触发器,归纳分析：如果是查看或修改已有存储过程的定义，则右击要查看或修改的存储过程，在弹出的快捷菜单中，选择“属性”命令，则调出“存储过程属性”窗口，在这里可以查看或修改存储过程的定义。,第9章存储过程与触发器,归纳分析：在SQLServer中，使用CREATEPROCEDURE语句创建一个永久或临时存储过程。命令格式：CREATEPROCEDURE存储过程名WITHENCRYPTIONWITHRECOMPILEASSQL语句命令说明：（1）WITHENCRYPTION：对存储过程进行加密。（2）WITHRECOMPILE：对存储过程重新编译。,第9章存储过程与触发器,归纳分析：只有数据库所有者和存储过程的所有者才有权修改存储过程名称。不宜轻易修改存储过程名称，因为系统中许多应用程序可能都要调用存储过程，如果修改了存储过程的名称，则这些应用程序都要随之改动。命令格式：sp_rename原存储过程名，新存储过程名修改存储过程名称的方法与修改表名称的方法类同，读者可以参照执行。使用企业管理器修改存储过程名称的方法也类似于修改表名称的方法，即展开指定的数据库节点，单击存储过程，在右边的窗口中选中一个需要修改名称的存储过程右击之，在弹出的快捷菜单中选择“重命名”。,第9章存储过程与触发器,96删除存储过程961使用企业管理器删除存储过程实例9.9使用企业管理器删除存储过程。操作步骤：,第9章存储过程与触发器,归纳分析：在SQLServer中，使用CREATEPROCEDURE语句创建一个永久或临时存储过程。命令格式：CREATEPROCEDURE存储过程名WITHENCRYPTIONWITHRECOMPILEASSQL语句命令说明：（1）WITHENCRYPTION：对存储过程进行加密。（2）WITHRECOMPILE：对存储过程重新编译。,第9章存储过程与触发器,97查看存储过程971使用企业管理器查看存储过程的信息实例9.11使用企业管理器查看存储过程的信息。操作步骤：在图9.4所示属性对话框中可以查询存储过程的名称、所有者、建立时间和文本信息。在图9.3所示窗口的右部选中一个存储过程并右击，在弹出的级联菜单中依次选择“所有任务”、“显示相关性”，系统会弹出“显示相关性”对话框，该对话框显示一个存储过程所引用的表及其列信息。,第9章存储过程与触发器,命令格式：CREATEPROCEDURE存储过程名参数名数据类型=默认值WITHENENCRYPTIONWITHRERECOMPILEASSQL语句命令说明：“参数名”和定义局部变量一样，必须以符号为前缀，要指定数据类型，多个参数定义要用逗号“，”隔开。在执行存储过程时该参数将由指定的参数值来代替，如果执行时未提供该参数的参数值，则如果这里定义了默认值则使用该值作为执行时的参数值，默认值可以是常量或空(NULL)。,第9章存储过程与触发器,归纳分析：SQLServer2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句(原有语句没有执行，仅执行新语句)，称之为INSTEADOF触发器：另一类是在引起触发器执行的语句执行完毕后，再执行触发器中的语句，称之为AFTER类触发器。(1)INSTEADOF触发器既可以在表上定义，也可以在视图上定义。该类触发器依据其触发动作(UPDATE、DELETE和INSERT)的不同分为INSTEADOFINSERT、INSTEADOFUPDATE和INSTEADOFDELETE三种，被引发执行时将分别代替表或视图的INSERT、UPDATE、DELETE操作，亦即表中的增删改操作没有进行。每个触发动作仅可以定义一个INSTEADOF触发器。,第9章存储过程与触发器,98存储过程编程981参数和变量实例9.13创建的存储过程，修改成当输入学生学号时可以查询某个学生的成绩：不输入学号时，则查询全部学生的成绩。操作步骤：,第9章存储过程与触发器,2执行带参数的存储过程在执行存储过程的语句中，有两种方式来传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。使用参数名传递参数值，是通过语句“参数名：参数值”给参数传递值。当存储过程含有多个输入参数时，参数值可以按任意顺序制定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。执行使用参数名传递参数值的存储过程的命令格式：EXECUTE存储过程名参数名=参数值按参数位置传递参数值，不显式地给出“参数名”，而是按照参数定义的顺序给出参数值。按位置传递参数时，也可以忽略允许空值和具有默认值的参数，但不能因此破坏输入参数的指定顺序。必要时，使用关键字“DEFAULT”作为默认值的占位。,第9章存储过程与触发器,991使用企业管理器创建触发器实例9.15在表学生成绩中建立一个触发器，当向表中插入一条记录时，检查该学生的学号是否存在于表学生信息中，如果存在，则插入；否则，打印“学号不存在”，并回滚事务，使插入数据无效。操作步骤：,第9章存储过程与触发器,（3）本例建立了一个AFIER触发器(仅给出FOR表示默认为AFTER型触发器)用于DELETE操作，首先声明一个局部变量用以存放Deleted表中数据行数。当对学生信息表执行删除操作时，检查删除的行数(Deleted表中数据行数)，如果行数1，则认为操作错误，打印提示信息并回滚事务，使删除无效。但是如果使用TRUNCATETABLE学生信息清除表中所有数据，则触发器不会触发，可以成功删除全部数据。,第9章存储过程与触发器,(4)INSERT，UPDATE，DELETE：指定在表或视图上执行哪些数据修改语句时将激活触发器。必须至少指定一个选项，如果指定的选项多于一个，需用逗号分隔。(5)IFUPDATE(列名)(AND|OR)UPDATE(列名)n：判定某一列或几列，是否被修改。如果希望仅当某一列(或几列)的数据发生变化才导致触发器动作，而其他列的数据变化不会引发触发器执行，则可以采用本选项。此时可以称之为列级触发器。,第9章存储过程与触发器,912删除触发器实例9.23删除不用的触发器名。操作步骤：,第9章存储过程与触发器,910触发器介绍触发器具有如下优点：(1)触发器可以实现外键约束的功能，实现对表的级连修改。(2)触发器可以实现核查约束的功能，而且其功能更为强大。它可以使用另一个表的内容来约束触发器所在表。(3)如果对表中数据进行了增、删、改的操作，使用触发器可以根据修改前后的差异，采取相应的对策。(4)允许在一个表中针对不同的增删改操作设置多个触发器，以完成各类不同任务。,第9章存储过程与触发器,9101触发器执行过程中的两个表实例9.17触发器执行过程中的两个表操作过程。操作步骤：触发器是在表上建立的，我们将触发器所依附的表称为触发器表。当修改表的数据而引发了触发器时，触发器将执行一系列T-SQL命令，在执行这些动作之前系统首先自动建立两个表，Deleted表与Inserted表。这两个表临时驻留在内存当中，其结构与触发器表完全相同，一旦触发器执行完毕，两个表也随之消失。可以使用Deleted表与Inserted表测试某些数据修改的效果以及设置触发器操作的条件，但不能直接修改这两个表中的数据。,第9章存储过程与触发器,Deleted表存放由于执行DELETE或UPDATE操作时从触发器表(被删除数据的表)中删除的数据行，即触发器表中需要删除的数据将被移到Deleted表中。因此这两个表没有相同的数据行，如图9.15所示。Inserted表用于存放执行INSERT或UPDATE操作时向触发器表(被插入数据的表)中插入的数据行，即新的数据行被同时插入到两个表触发器表和Inserted表中，因此这两个表有相同的数据行，如图9.16所示。,第9章存储过程与触发器,UPDATE操作相当于先执行DELETE操作，然后再执行INSERT操作，因此要用到Deleted与Inserted两个表。,第9章存储过程与触发器,9102SQLServer2000的两类触发器实例9.18建立一个触发器，防止用户在要删除表学生信息中的一条数据时，由于没有使用限制条件(误用SELECT*FROM学生信息语句)而导致删除全部数据的情况发生。操作步骤：,第9章存储过程与触发器,（3）本例建立了一个AFIER触发器(仅给出FOR表示默认为AFTER型触发器)用于DELETE操作，首先声明一个局部变量用以存放Deleted表中数据行数。当对学生信息表执行删除操作时，检查删除的行数(Deleted表中数据行数)，如果行数1，则认为操作错误，打印提示信息并回滚事务，使删除无效。但是如果使用TRUNCATETABLE学生信息清除表中所有数据，则触发器不会触发，可以成功删除全部数据。,第9章存储过程与触发器,实例9.19在学生成绩表上建立一个列级触发器trigger_学生成绩，仅当修改班级时，触发器执行，其他情况触发器不执行。操作步骤：,第9章存储过程与触发器,归纳分析：SQLServer2000中可以建立两类触发器，一类是在触发后将执行一组新的T-SQL语句用以代替引起触发器执行的语句(原有语句没有执行，仅执行新语句)，称之为INSTEADOF触发器：另一类是在引起触发器执行的语句执行完毕后，再执行触发器中的语句，称之为AFTER类触发器。(1)INSTEADOF触发器既可以在表上定义，也可以在视图上定义。该类触发器依据其触发动作(UPDATE、DELETE和INSERT)的不同分为INSTEADOFINSERT、INSTEADOFUPDATE和INSTEADOFDELETE三种，被引发执行时将分别代替表或视图的INSERT、UPDATE、DELETE操作，亦即表中的增删改操作没有进行。每个触发动作仅可以定义一个INSTEADOF触发器。,第9章存储过程与触发器,(2)AFTER触发器只能在表上定义。该类触发器分为AFTERINSERT、AFTERUPDATE和AFTERDELETE三种，被引发时将分别在表的INSERT、UPDATE、DELETE操作之后执行，也就是说只有在表中数据的增删改操作、外键约束和核查约束均成功完成后，才能执行AFTER触发器。每个触发动作可以定义多个AFTER触发器，并可以指定这些触发器的执行顺序。,第9章存储过程与触发器,911修改触发器触发器建立后可以对其进行修改，既可以修改它的名称，也可以修改它的定义。9111修改触发器名称实例9.20将触发器trigger_student改为tri_学生。操作步骤：,第9章存储过程与触发器,（1）启动“查询分析器”，输入如下SQL语句：sp_renametrigger_student，tri_学生（2）按“F5”键或单击工具栏“执行查询”图标。归纳分析：命令格式：sp_rename旧触发器名，新触发器名,第9章存储过程与触发器,9112修改触发器的定义实例9.21在企业管理器中修改实例9.17将删除表改为添加记录。操作步骤：（3）单击检查语法按钮，进行语法检查，成功后，可插入数据检验触发器的效果。,第9章存储过程与触发器,归纳分析：命令格式：ALTERTRIGGERON表|视图WITHENCRYPTIONFOR|AFTER|INSTEADOFINSERT，UPDATE，DELETENOTFORREPLICATIONASIFUPDATE(列名)(AND|OR)UPDATE(列名)nSQL语句命令说明：其中各项语法含义与创建触发器相同。,第9章存储过程与触发器,9113修改触发器的有效性实例9.22在实例9.18中打算修改班级时，可以先使触发器失效，然后修改班级，修改完毕再使触发器生效。操作步骤：,第9章存储过程与触发器,归纳分析：对于暂时希望触发器失效的情况而言，这项功能很有用。一旦需要恢复触发器的功能，不必重新编写，只要触发器重新生效即可。命令格式：(1)表中触发器无效的语法为：ALTERTABLE表名DISABLETRIGGER触发器名触发器无效时，相当于表中没有该触发器。(2)使表中无效的触发器重新生效的语法为：ALTERTABLE表名ENABLETRIGGER触发器名,第9章存储过程与触发器,912删除触发器实例9.23删除不用的触发器名。操作步骤：,第9章存储过程与触发器,归纳分析：命令格式：DROPTRIGGERtrigger_name，n)上述语句可以删除多个触发器，每个触发器的名称之间用逗号隔开。,第9章存储过程与触发器,913查询触发器信息实例9.24利用企业管理器查看查看本章建立的触发器。操作步骤：,第9章存储过程与触发器,实例9.25利用系统存储过程查看本章建立的触发器。操作步骤：,第9章存储过程与触发器,归纳分析：触发器创建以后，可以使用系统存储过程浏览触发器的有关信息。(1)显示触发器对象的类型。创建时间、所有者信息。sp_help触发器名(2)显示触发器的定义文本。如果触发器使用了加密(WITHENCRYPTION选项)，则无法看到触发器的代码文本。sp_helptext触发器名,第9章存储过程与触发器,(3)显示表中触发器的信息。其中type必须是INSERT、UPDATE、DELETE三者之一，且方括号不能去掉。如果省略“type”，则列出所有触发器，否则，列出type指定类型的触发器。sp_helptrigger表名，INSERT|UPDATE|DELETE(4)利用查询语句从系统表中查询所有触发器的信息SELECT*FROMS