数据库技术及应用第10章 触发器和游标

1、数据库技术及应用第数据库技术及应用第1010章章 触发器和游标触发器和游标2021-12-16210.1 触发器概述触发器概述 触发器实际上就是一种特殊类型的存储过触发器实际上就是一种特殊类型的存储过程，它在一定操作时自动触发执行。在程，它在一定操作时自动触发执行。在SQL Server 2005之前的版本中，触发器是针对数之前的版本中，触发器是针对数据表的特殊的存储过程，当这个表发生了据表的特殊的存储过程，当这个表发生了Insert、Update或或Delete操作时，如果该表操作时，如果该表有对应操作的触发器，这个触发器就会自动激有对应操作的触发器，这个触发器就会自动激活执行。在活执行。在

2、SQL Server 2005中，触发器有了中，触发器有了更进一步的功能，在数据表库发生更进一步的功能，在数据表库发生Create、Alter和和Drop操作时，也会自动激活执行。操作时，也会自动激活执行。2021-12-163完成更复杂的数据约束 检查所做的SQL所作的操作是否允许修改其它数据表里的数据 调用更多的存储过程 返回自定义的错误信息 更改原本要操作的SQL语句 防止数据表结构被更改或数据表被删除 2021-12-164DML触发器触发器 DML触发器是当数据库效劳器中发生触发器是当数据库效劳器中发生数据操作语言数据操作语言Data Manipulation Language事件时

3、执行的存储过程事件时执行的存储过程DDL触发器触发器 DDL触发器是在响应数据定义语言触发器是在响应数据定义语言Data Definition Language事件时执行的事件时执行的存储过存储过程。程。 2021-12-16510.2 DML触发器触发器 After触发器：这类触发器是在记录已经改变完触发器：这类触发器是在记录已经改变完之后之后after，才会被激活执行，它主要是用，才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，于记录变更后的处理或检查，一旦发现错误，也可以用也可以用Rollback Transaction语句来回滚本语句来回滚本次的操作。次的操作。Ins

4、tead Of触发器：这类触发器一般是用来取代触发器：这类触发器一般是用来取代原本要进行的操作，在记录变更之前发生的，原本要进行的操作，在记录变更之前发生的，它并不去执行原来它并不去执行原来SQL语句里的操作语句里的操作Insert、Update、Delete，而去执行触发器本身所，而去执行触发器本身所定义的操作。定义的操作。 2021-12-166After触发器的工作原理触发器的工作原理 After触发器是在记录更变完之后才被激活执行。Instead Of触发器的工作原理触发器的工作原理 Instead Of触发器与After触发器不同。After触发器是在Insert、Update和De

5、lete操作完成后才激活的，而Instead Of触发器，是在这些操作进行之前就激活了，并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。 2021-12-167CREATE TRIGGER语句必须是批处理中的第一 个语句创立DML触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户 DML触发器为数据库对象，其名称必须遵循标识符的命名规那么 只能在当前数据库中创立DML触发器 不能对临时表或系统表创立DML触发器WRITETEXT语句不会触发INSERT或UPDATE触发器2021-12-168步骤如下：1启动“SQL Server Management Studio，

6、在“对象资源管理器下选择“数据库，定位到具体的数据库，展开其下的“表树型目录，找到具体的表，并选中其下的“触发器项，如以以以下图所示。2021-12-169在图形界面下创立触发器在图形界面下创立触发器2021-12-1610在图形界面下创立触发器在图形界面下创立触发器2右击“触发器，在弹出的快捷菜单中选择“新建触发器选项，弹出“查询编辑器对话框，在“查询编辑器的编辑区里SQL Server已经预写入了一些建立触发器相关的SQL语句，如以以以下图所示。2021-12-1611在图形界面下创立触发器在图形界面下创立触发器3修改“查询编辑器里的代码4单击工具栏中的“分析按钮，检查一下是否语法有错，如

7、果在“结果对话框中出现“命令已成功完成，那么表示语法没有错误。 5语法检查无误后，单击“执行按钮，生成触发器。2021-12-1612用用SQL语句创立触发器语句创立触发器创立AFTER触发器的语法代码如下：CREATE TRIGGER ON .WITH ENCRYPTION|EXECUTE AS CALLER|SELF|FOR|AFTERINSERT,UPDATE,DELETEWITH APPENDNOT FOR REPLICATIONAS|EXTERNAL NAME 2021-12-1613用用SQL语句创立触发器语句创立触发器主要参数说明：trigger_name：触发器的名称,必须遵循

8、标识符规schema_name：触发器所属架构的名称。table|view：指定触发器所在的数据表或视图。WITH ENCRYPTION：对CREATE TRIGGER语句的文本进行加密。EXECUTE AS：用于执行该触发器的平安上下文。AFTER：指定DML触发器仅在触发SQL语句中指定的所有操作都已成功执行时才被激发。DELETE,INSERT,UPDATE：指定数据修改语句，这些语句可在DML触发器对此表或视图进行尝试时激活该触发器。必须至少指定一个选项。WITH APPEND：指定应该再添加一个现有类型的触发器。 2021-12-1614AFTER触发器例如触发器例如 修改学生表中的

9、数据时，下述触发器将向客户显示一条消息。CREATE TRIGGER 学生_updateON 学生表AFTER UPDATE ASBEGINRAISERROR (注意：有人修改学生表的数据,16,10)ENDGO2021-12-1615创立创立INSTEAD OF触发器触发器 创立INSTEAD OF触发器的语法代码如下 ：CREATE TRIGGER ON .WITH ENCRYPTION|EXECUTE AS CALLER|SELF|INSTEAD OFINSERT,UPDATE,DELETEWITH APPENDNOT FOR REPLICATIONAS|EXTERNAL NAME 创立

10、Instead Of触发器与创立After触发器的语法几乎一样，只是简单地把After改为Instead Of。 2021-12-1616INSTEAD OF触发器例如触发器例如例例10.2 当有人试图修改学生表中的数据，利用下述触发器可以跳过当有人试图修改学生表中的数据，利用下述触发器可以跳过修改数据的修改数据的SQL语句防止数据被修改，并向客户端显示一条消语句防止数据被修改，并向客户端显示一条消息。息。CREATE TRIGGER 学生学生_updateON 学生表学生表INSTEAD OF UPDATE ASBEGINRAISERROR (对不起，学生表的数据不允许修改对不起，学生表的数

11、据不允许修改,16,10)ENDGO2021-12-1617查看触发器查看触发器1.通过 SQL Server Management Studio2021-12-1618查看触发器1sp_help 系统存储过程sp_help可以了解如触发器名称、类型、创立时间等根本信息，其语法格式为： sp_help 触发器名 例如：sp_help 学生_insert 2sp_helptext 系统存储过程sp_helptext可以查看触发器的文本信息，其语法格式为： sp_helptext 触发器名 例如：sp_helptext 学生_insert2021-12-1619修改触发器修改触发器修改触发器的语法

12、代码如下：ALTER TRIGGER ON table|viewWITH ENCRYPTION|EXECUTE AS CALLER|SELF|FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETENOT FOR REPLICATIONAS|EXTERNAL NAME 分析上述语法代码可以发现，修改触发器语法中所涉及到主要参数和创立触发器的主要参数几乎一样，在此不再赘述。2021-12-1620删除删除DML触发器触发器 1 在图形界面方式下删除触发器 按照在SQL Server Management Studio中查看触发器的方法，找到“触发器列表对话框。右击要删除的

13、某个触发器，在弹出快捷菜单中选择“删除选项。2用SQL语句删除触发器，删除触发器的语法代码如下所示： Drop Trigger 触发器名2021-12-1621 禁用禁用DML触发器触发器 1在图形界面方式下禁用在图形界面方式下禁用DML触发器触发器按照在按照在SQL Server Management Studio中查看触发器的方法翻开中查看触发器的方法翻开“触发触发器列表对话框。器列表对话框。右击其中一个触发器，在弹出快捷菜单中右击其中一个触发器，在弹出快捷菜单中选择选择“禁用选项，即可。禁用选项，即可。 语法如下：语法如下： Alter table 数据表名数据表名 Disable tr

14、igger 触发器名或触发器名或ALL 如果要禁用所有触发器，用如果要禁用所有触发器，用“ALL来来代替触发器名。代替触发器名。2021-12-1622启用启用DML触发器触发器 启用触发器与禁用触发器类似，只是在弹出的快捷菜单中选择“启用选项即可。 其语法如下： Alter table 数据表名 Enable trigger 触发器名或ALL 如果要启用所有触发器，用“ALL来代替触发器名。2021-12-162310.3 DDL触发器触发器DDL触发器是SQL Server 2005新增的一个触发器类型，像常规触发器一样，DDL 触发器将激发存储过程以响应事件。但与DML触发器不同的是，它

15、们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。相反，它们会为响应多种数据定义语言(DDL)语句而激发。这些语句主要是以 CREATE、ALTER和DROP开头的语句。DDL触发器可用于管理任务，例如审核和控制数据库操作。2021-12-1624创立DDL触发器的语法代码如下：CREATE TRIGGER ON ALL SERVER|DATABASEWITH ,.nFOR|AFTERevent_type|event_group,.nASsql_statement;.n|EXTERNAL NAME;:=ENCRYPTIONEXECUTE AS Clause:=202

16、1-12-1625主要参数说明：trigger_name：触发器的名称,必须遵循标识符规那么，但不能以#或#开头。DATABASE：将DDL触发器的作用域应用于当前数据库。如果指定了此参数，那么只要当前数据库中出现event_type或event_group，就会激发该触发器。ALL SERVER：将DDL触发器的作用域应用于当前效劳器。如果指定了此参数，那么只要当前效劳器中的任何位置上出现event_type或event_group，就会激发该触发器。event_type：执行之后将导致激发DDL触发器的Transact-SQL语言事件的名称。event_group：预定义的 Transac

17、t-SQL 语言事件分组的名称。其他参数在前面章节中已经说明，在此不再赘述。2021-12-1626创立创立DDL触发器例如触发器例如的触发器。的触发器。具体操作步骤如下：具体操作步骤如下：1启动启动“SQL Server Management Studio，在，在“对象资源对象资源管理器下选择管理器下选择“数据库，定位到数据库，定位到“student数据库。数据库。2单击单击“新建查询按钮，在弹出的新建查询按钮，在弹出的“查询编辑器的编辑区里查询编辑器的编辑区里输入以下代码：输入以下代码：CREATE TRIGGER disable_drop_tableON DATABASEFOR DROP

18、_TABLE ASBEGINRAISERROR (对不起，对不起，student数据库中的表不能删除数据库中的表不能删除,16,10)ENDGO2021-12-1627测试测试DDL触发器功能触发器功能测试DDL触发器的功能，具体操作步骤如下：启动“SQL Server Management Studio，在“对象资源管理器下选择“数据库，定位到“student数据库。单击“新建查询按钮，在弹出的“查询编辑器的编辑区里输入以下代码： DROP TABLE 成绩表单击“执行按钮，运行结果如以以以下图所示 2021-12-1628测试结果测试结果 2021-12-1629查看和修改查看和修改DDL

19、触发器触发器查看查看DDL触发器触发器1查看作用在当前查看作用在当前SQL Server效劳器上的效劳器上的DDL触发器触发器 选择所在选择所在SQL Server效劳器，定位到效劳器，定位到“效劳器对象，效劳器对象，“触发器触发器，在，在“摘要对话框里就可以看到所有的作用在当前摘要对话框里就可以看到所有的作用在当前SQL Server效劳器上的效劳器上的DDL触发器。触发器。2查看作用在当前数据库中的查看作用在当前数据库中的DDL触发器触发器 选择所在选择所在SQL Server效劳器，效劳器，“数据库，所在数据库，定位到数据库，所在数据库，定位到“可编程性，可编程性，“数据库触发器，在摘要

20、对话框里就可以看到数据库触发器，在摘要对话框里就可以看到所有的当前数据库中的所有的当前数据库中的DDL触发器。触发器。3. 右击触发器，在弹出的快捷菜单中选择右击触发器，在弹出的快捷菜单中选择“编写数据库触发器脚本编写数据库触发器脚本为，为，“CREATE到，到，“新查询编辑器对话框，然后在新翻新查询编辑器对话框，然后在新翻开的开的“查询编辑器对话框里可以看到该触发器的内容。查询编辑器对话框里可以看到该触发器的内容。修改触发器修改触发器 在在Management Studio如果要修改如果要修改DDL触发器内容，就只能先触发器内容，就只能先删除该删除该触发器，再重新建立一个触发器，再重新建立一

21、个DDL触发器。触发器。 虽然在虽然在Management Studio中没有直接提供修改中没有直接提供修改DDL触发器的触发器的对话对话框，但在框，但在“查询编辑器对话框里依然可以用查询编辑器对话框里依然可以用SQL语句来进行修改。语句来进行修改。2021-12-1630游标是一种处理数据的方法，它可对结果集中的记录进行逐行处理，可将游标视作一种指针，用于指向并处理结果集任意位置的数据。就本质而言游标提供了一种对从表中检索出的数据进行操作的灵活手段，由于游标由结果集和结果集中指向特定记录的游标位置组成，因此当决定对结果集进行处理时，必须声明定一个指向该结果集的游标。 2021-12-1631

22、游标的特点游标的特点 允许定位在结果集的特定行。从结果集的当前位置检索一行或一局部行。支持对结果集中当前位置的行进行数据修改。为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句 2021-12-1632游标分类游标分类 1Transact-SQL游标 Transact-SQL游标是由SQL Server效劳器实现的游标，主要用于存储过程、触发器和Transact-SQL 脚本中，它们使结果集的内容可用于其他 Transact-SQL 语句。2API 效劳器游标 API效劳器游标在效劳器

23、上实现，并由 API 游标函数进行管理。当应用程序调用 API 游标函数时，游标操作由OLE DB访问接口或ODBC驱动程序传送给效劳器。3客户端游标 客户端游标，即在客户端实现的游标。在客户端游标中，将使用默认结果集把整个结果集高速缓存在客户端上，所有的游标操作都针对此客户端高速缓存来执行。将不使用 Microsoft SQL Server 2005的任何效劳器游标功能。客户端游标仅支持只进和静态游标。 由于Transact-SQL游标和API 效劳器游标用于效劳器端，所以被称为效劳器游标，也被称为后台游标。本章主要讲述效劳器游标。2021-12-16331使用使用SOL-92语法来声明语法

24、来声明CURSOR语法代码如下：DECLARE cursor_name INSENSITIVESCROLL CURSORFOR select_statementFOR READ ONLY|UPDATE OF column_name,.n 2T-SQL扩充语法来声明扩充语法来声明CURSOR语法代码如下：DECLARE cursor_name CURSORLOCAL|GLOBALFORWARD_ONLY|SCROLLSTATIC|KEYSET|DYNAMIC|FAST_FORWARDREAD_ONLY|SCROLL_LOCKS|OPTIMISTICTYPE_WARNINGFOR select_s

25、tatementFOR UPDATE OF column_name ,.n 2021-12-1634声明游标例如声明一个名称为学生_Cursor的游标DECLARE 学生_Cursor CURSOR FORSELECT sNo, sNameFROM 学生表;2021-12-1635翻开游标翻开游标翻开游标的语法代码如下：OPEN GLOBAL cursor_name|cursor_variable_name主要参数说明：GLOBAL：指定cursor_name是指全局游标。cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，那么cursor_name指的是全局游标；否那么cursor_name指的是局部游标。cursor_variable_name：游标变量的名称，该变量引用一个