数据库技术及应用第10章-触发器和游标(00002)课件.ppt

《数据库技术及应用第10章-触发器和游标(00002)课件.ppt》由会员分享，可在线阅读，更多相关《数据库技术及应用第10章-触发器和游标(00002)课件.ppt（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第10章 触发器和游标 3/29/20231SQL Server 2005 10.1 触发器概述触发器概述 10.1.1触发器的概念触发器的概念 触发器实际上就是一种特殊类型的存储过程，它在一定操作时自动触发执行。在SQL Server 2005之前的版本中，触发器是针对数据表的特殊的存储过程，当这个表发生了Insert、Update或Delete操作时，如果该表有对应操作的触发器，这个触发器就会自动激活执行。在SQL Server 2005中，触发器有了更进一步的功能，在数据表（库）发生Create、Alter和Drop操作时，也会自动激活执行。3/29/20232SQL Server 20

2、05 10.1.2触发器的功能触发器的功能完成更复杂的数据约束 检查所做的SQL所作的操作是否允许修改其它数据表里的数据 调用更多的存储过程 返回自定义的错误信息 更改原本要操作的SQL语句 防止数据表结构被更改或数据表被删除 3/29/20233SQL Server 2005 10.1.3触发器的类型触发器的类型DML触发器触发器 DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执行的存储过程DDL触发器触发器 DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。3/29/202

3、34SQL Server 2005 10.2.2 DML触发器的工作原理触发器的工作原理After触发器的工作原理触发器的工作原理 After触发器是在记录更变完之后才被激活执行。Instead Of触发器的工作原理触发器的工作原理 Instead Of触发器与After触发器不同。After触发器是在Insert、Update和Delete操作完成后才激活的，而Instead Of触发器，是在这些操作进行之前就激活了，并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。3/29/20236SQL Server 2005 10.2.3 创建创建DML触发器的注意事项触发器的注意事项

4、CREATE TRIGGER语句必须是批处理中的第一 个语句创建DML触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户 DML触发器为数据库对象，其名称必须遵循标识符的命名规则 只能在当前数据库中创建DML触发器 不能对临时表或系统表创建DML触发器WRITETEXT语句不会触发INSERT或UPDATE触发器3/29/20237SQL Server 2005 10.2.4 创建创建AFTER触发器触发器步骤如下：（1）启动“SQL Server Management Studio”，在“对象资源管理器”下选择“数据库”，定位到具体的数据库，展开其下的“表”树型目录，找到具体的表，

5、并选中其下的“触发器”项，如下图所示。3/29/20238SQL Server 2005 在图形界面下创建触发器在图形界面下创建触发器（2）右击“触发器”，在弹出的快捷菜单中选择“新建触发器”选项，弹出“查询编辑器”对话框，在“查询编辑器”的编辑区里SQL Server已经预写入了一些建立触发器相关的SQL语句，如下图所示。3/29/202310SQL Server 2005 在图形界面下创建触发器在图形界面下创建触发器（3）修改“查询编辑器”里的代码（4）单击工具栏中的“分析”按钮，检查一下是否语法有错，如果在“结果”对话框中出现“命令已成功完成”，则表示语法没有错误。（5）语法检查无误后，

6、单击“执行”按钮，生成触发器。3/29/202311SQL Server 2005 用用SQL语句创建触发器语句创建触发器主要参数说明：trigger_name：触发器的名称,必须遵循标识符规schema_name：触发器所属架构的名称。table|view：指定触发器所在的数据表或视图。WITH ENCRYPTION：对CREATE TRIGGER语句的文本进行加密。EXECUTE AS：用于执行该触发器的安全上下文。AFTER：指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被激发。DELETE,INSERT,UPDATE：指定数据修改语句，这些语句可在DML触发器对此表

7、或视图进行尝试时激活该触发器。必须至少指定一个选项。WITH APPEND：指定应该再添加一个现有类型的触发器。3/29/202313SQL Server 2005 创建创建INSTEAD OF触发器触发器 创建INSTEAD OF触发器的语法代码如下：CREATE TRIGGER ON.WITH ENCRYPTION|EXECUTE AS CALLER|SELF|INSTEAD OFINSERT,UPDATE,DELETEWITH APPENDNOT FOR REPLICATIONAS|EXTERNAL NAME 创建Instead Of触发器与创建After触发器的语法几乎一样，只是简单地

8、把After改为Instead Of。3/29/202315SQL Server 2005 INSTEAD OF触发器示例触发器示例例例10.2 当有人试图修改学生表中的数据，利用下述触发器可以跳过修改数据的SQL语句（防止数据被修改），并向客户端显示一条消息。CREATE TRIGGER 学生_updateON 学生表INSTEAD OF UPDATE ASBEGINRAISERROR(对不起，学生表的数据不允许修改,16,10)ENDGO3/29/202316SQL Server 2005 查看触发器2.通过系统存储过程（1）sp_help 系统存储过程sp_help可以了解如触发器名称、

9、类型、创建时间等基本信息，其语法格式为：sp_help 触发器名 例如：sp_help 学生_insert（2）sp_helptext 系统存储过程sp_helptext可以查看触发器的文本信息，其语法格式为：sp_helptext 触发器名 例如：sp_helptext 学生_insert3/29/202318SQL Server 2005 修改触发器修改触发器修改触发器的语法代码如下：ALTER TRIGGER ON table|viewWITH ENCRYPTION|EXECUTE AS CALLER|SELF|FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELE

10、TENOT FOR REPLICATIONAS|EXTERNAL NAME 分析上述语法代码可以发现，修改触发器语法中所涉及到主要参数和创建触发器的主要参数几乎一样，在此不再赘述。3/29/202319SQL Server 2005 删除删除DML触发器触发器（1）在图形界面方式下删除触发器 按照在SQL Server Management Studio中查看触发器的方法，找到“触发器列表”对话框。右击要删除的某个触发器，在弹出快捷菜单中选择“删除”选项。（2）用SQL语句删除触发器，删除触发器的语法代码如下所示：Drop Trigger 触发器名3/29/202320SQL Server 2

11、005 禁用禁用DML触发器触发器 1在图形界面方式下禁用在图形界面方式下禁用DML触发器触发器按照在SQL Server Management Studio中查看触发器的方法打开“触发器列表”对话框。右击其中一个触发器，在弹出快捷菜单中选择“禁用”选项，即可。2.使用使用T-SQL语句禁用语句禁用DML触发器触发器 语法如下：Alter table 数据表名 Disable trigger 触发器名或ALL 如果要禁用所有触发器，用“ALL”来代替触发器名。3/29/202321SQL Server 2005 启用启用DML触发器触发器1.图形界面方式下启用触发器 启用触发器与禁用触发器类似

12、，只是在弹出的快捷菜单中选择“启用”选项即可。2.使用T-SQL语句启用触发器 其语法如下：Alter table 数据表名 Enable trigger 触发器名或ALL 如果要启用所有触发器，用“ALL”来代替触发器名。3/29/202322SQL Server 2005 10.3.1 创建创建DDL触发器触发器创建DDL触发器的语法代码如下：CREATE TRIGGER ON ALL SERVER|DATABASEWITH,.nFOR|AFTERevent_type|event_group,.nASsql_statement;.n|EXTERNAL NAME;:=ENCRYPTIONEX

13、ECUTE AS Clause:=assembly_name.class_name.method_name3/29/202324SQL Server 2005 主要参数说明：trigger_name：触发器的名称,必须遵循标识符规则，但不能以#或#开头。DATABASE：将DDL触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现event_type或event_group，就会激发该触发器。ALL SERVER：将DDL触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置上出现event_type或event_group，就会激发该触发器。eve

14、nt_type：执行之后将导致激发DDL触发器的Transact-SQL语言事件的名称。event_group：预定义的 Transact-SQL 语言事件分组的名称。其他参数在前面章节中已经说明，在此不再赘述。3/29/202325SQL Server 2005 创建创建DDL触发器示例触发器示例例例10.3建立用于保护student数据库中的数据表不被删除的触发器。具体操作步骤如下：（1）启动“SQL Server Management Studio”，在“对象资源管理器”下选择“数据库”，定位到“student”数据库。（2）单击“新建查询”按钮，在弹出的“查询编辑器”的编辑区里输入以下

15、代码：CREATE TRIGGER disable_drop_tableON DATABASEFOR DROP_TABLE ASBEGINRAISERROR(对不起，student数据库中的表不能删除,16,10)ENDGO3/29/202326SQL Server 2005 测试结果测试结果 3/29/202328SQL Server 2005 查看和修改查看和修改DDL触发器触发器查看查看DDL触发器触发器1查看作用在当前SQL Server服务器上的DDL触发器 选择所在SQL Server服务器，定位到“服务器对象”，“触发器”，在“摘要”对话框里就可以看到所有的作用在当前SQL Se

16、rver服务器上的DDL触发器。2查看作用在当前数据库中的DDL触发器 选择所在SQL Server服务器，“数据库”，所在数据库，定位到“可编程性”，“数据库触发器”，在摘要对话框里就可以看到所有的当前数据库中的DDL触发器。3.右击触发器，在弹出的快捷菜单中选择“编写数据库触发器脚本为”，“CREATE到”，“新查询编辑器对话框”，然后在新打开的“查询编辑器”对话框里可以看到该触发器的内容。修改触发器修改触发器 在Management Studio如果要修改DDL触发器内容，就只能先删除该触发器，再重新建立一个DDL触发器。虽然在Management Studio中没有直接提供修改DDL触

17、发器的对话框，但在“查询编辑器”对话框里依然可以用SQL语句来进行修改。3/29/202329SQL Server 2005 游标的特点游标的特点 允许定位在结果集的特定行。从结果集的当前位置检索一行或一部分行。支持对结果集中当前位置的行进行数据修改。为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句 3/29/202331SQL Server 2005 游标分类游标分类（1）Transact-SQL游标 Transact-SQL游标是由SQL Server服务器实现的游标，主要用于存

18、储过程、触发器和Transact-SQL 脚本中，它们使结果集的内容可用于其他 Transact-SQL 语句。（2）API 服务器游标 API服务器游标在服务器上实现，并由 API 游标函数进行管理。当应用程序调用 API 游标函数时，游标操作由OLE DB访问接口或ODBC驱动程序传送给服务器。（3）客户端游标 客户端游标，即在客户端实现的游标。在客户端游标中，将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都针对此客户端高速缓存来执行。将不使用 Microsoft SQL Server 2005的任何服务器游标功能。客户端游标仅支持只进和静态游标。由于Transact-SQ

19、L游标和API 服务器游标用于服务器端，所以被称为服务器游标，也被称为后台游标。本章主要讲述服务器游标。3/29/202332SQL Server 2005 10.5游标的声明和应用游标的声明和应用1使用使用SOL-92语法来声明语法来声明CURSOR语法代码如下：DECLARE cursor_name INSENSITIVESCROLL CURSORFOR select_statementFOR READ ONLY|UPDATE OF column_name,.n 2T-SQL扩充语法来声明扩充语法来声明CURSOR语法代码如下：DECLARE cursor_name CURSORLOCAL

20、|GLOBALFORWARD_ONLY|SCROLLSTATIC|KEYSET|DYNAMIC|FAST_FORWARDREAD_ONLY|SCROLL_LOCKS|OPTIMISTICTYPE_WARNINGFOR select_statementFOR UPDATE OF column_name,.n 3/29/202333SQL Server 2005 声明游标示例例例10.4声明一个名称为学生_Cursor的游标DECLARE 学生_Cursor CURSOR FORSELECT sNo,sNameFROM 学生表;3/29/202334SQL Server 2005 打开游标打开游标

21、打开游标的语法代码如下：OPEN GLOBAL cursor_name|cursor_variable_name主要参数说明：GLOBAL：指定cursor_name是指全局游标。cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标；否则cursor_name指的是局部游标。cursor_variable_name：游标变量的名称，该变量引用一个游标。例例10.5打开一个名称为学生_Cursor的游标 OPEN 学生_Cursor;3/29/202335SQL Server

22、2005 从游标中提取记录从游标中提取记录 其语法代码格式如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBALcursor_name|cursor_variable_nameINTOvariable_name,.n 例例10.6 从一个已经被打开的游标（学生_Cursor）中逐行提取记录FETCH NEXT FROM 学生_CursorWHILE FETCH_STATUS=0BEGINFETCH NEXT FROM 学生_CursorEND3/29/202336SQL Server 2005 关闭游标关闭游

23、标 关闭游标的语法代码格式如下：CLOSE GLOBAL cursor_name|cursor_variable_name主要参数说明：GLOBAL：指定cursor_name是指全局游标。cursor_name：打开的游标的名称cursor_variable_name：与打开的游标关联的游标变量的名称。例例10.7 关闭一个已经打开的游标（学生_Cursor）CLOSE 学生_Cursor;3/29/202337SQL Server 2005 释放游标释放游标 释放游标的语法代码格式如下：DEALLOCATE GLOBAL cursor_name|cursor_variable_name主要参数说明：GLOBAL：指定cursor_name是指全局游标。cursor_name：声明的游标的名称cursor_variable_name：cursor变量的名称。例例10.8 释放一个名称为学生_Cursor的游标DEALLOCATE 学生_Cursor;3/29/202338SQL Server 2005 本章小结本章小结 本章首先介绍了触发器的特点、作用和类型。然后图文并茂的介绍了创建DML和DDL触发器的方法和步骤。接着介绍了游标的特点、作用。最后详细描述了游标的声明、打开、数据提取、关闭和释放以及游标的应用。3/29/202339SQL Server 2005