第10章存储过程与触发器

1、第第10章章 存储过程与触发器存储过程与触发器本章内容本章内容10.1 存储过程概述存储过程概述10.2 存储过程的创建与使用存储过程的创建与使用10.3 触发器概述触发器概述10.4 触发器的创建与使用触发器的创建与使用10.5 事务处理事务处理10.6 SQL Server的锁机制的锁机制10.1 存储过程概述存储过程概述 n存储过程是存储过程是SQL Server服务器上一组预编服务器上一组预编译的译的Transact-SQL语句，用于完成某项任语句，用于完成某项任务，它可以接受参数、返回状态值和参数务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。值，并且可以嵌套调用。10.1

2、 10.1 存储过程概述存储过程概述nSQL Server存储过程的类型包括：存储过程的类型包括：系统存储过程系统存储过程用户定义存储过程用户定义存储过程临时存储过程临时存储过程扩展存储过程。扩展存储过程。1. 存储过程的类型存储过程的类型10.1 10.1 存储过程概述存储过程概述(1)系统存储过程系统存储过程 n是指由系统提供的存储过程，主要存储在是指由系统提供的存储过程，主要存储在master数据库中数据库中并以并以sp_为前缀，它从系统表中获取信息，从而为系统管为前缀，它从系统表中获取信息，从而为系统管理员管理理员管理SQL Server提供支持。提供支持。n通过系统存储过程，通过系统

3、存储过程，SQL Server中的许多管理性或信息性中的许多管理性或信息性的活动的活动(例如使用例如使用sp_depends、sp_helptexts可以了解数据可以了解数据数据库对象、数据库信息数据库对象、数据库信息)都可以顺利有效地完成。尽管都可以顺利有效地完成。尽管系统存储过程被放在系统存储过程被放在master数据库中，仍可以在其他数据数据库中，仍可以在其他数据库中对其进行调用库中对其进行调用(调用时，不必在存储过程名前加上数调用时，不必在存储过程名前加上数据库名据库名)。当创建一个新数据库时，一些系统存储过程会。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。在新数据

4、库中被自动创建。10.1 10.1 存储过程概述存储过程概述(2)用户定义存储过程用户定义存储过程n是由用户创建并能完成某一特定功能是由用户创建并能完成某一特定功能(例如例如查询用户所需数据信息查询用户所需数据信息)的存储过程。它处的存储过程。它处于用户创建的数据库中，存储过程名前没于用户创建的数据库中，存储过程名前没有前缀有前缀sp_。10.1 10.1 存储过程概述存储过程概述(3)临时存储过程临时存储过程n临时存储过程与临时表类似，分为局部临时存储过程和全临时存储过程与临时表类似，分为局部临时存储过程和全局临时存储过程，且可以分别向该过程名称前面添加局临时存储过程，且可以分别向该过程名称

5、前面添加“#”或或“#”前缀表示。前缀表示。“#”表示本地临时存储过程，表示本地临时存储过程，“#”表表示全局临时存储过程。使用临时存储过程必须创建本地连示全局临时存储过程。使用临时存储过程必须创建本地连接，当接，当SQL Server关闭后，这些临时存储过程将自动被删关闭后，这些临时存储过程将自动被删除。除。n由于由于SQL Server支持重新使用执行计划，所以连接到支持重新使用执行计划，所以连接到SQL Server 2000的应用程序应使用的应用程序应使用sp_executesql系统存储过程，系统存储过程，而不使用临时存储过程。而不使用临时存储过程。10.1 10.1 存储过程概述存

6、储过程概述(4)扩展存储过程扩展存储过程n扩展存储过程是扩展存储过程是SQL Server可以动态装载可以动态装载和执行的动态链接库和执行的动态链接库(DLL)。当扩展存储过。当扩展存储过程加载到程加载到SQL Server中，它的使用方法与中，它的使用方法与系统存储过程一样。扩展存储过程只能添系统存储过程一样。扩展存储过程只能添加到加到master数据库中，其前缀是数据库中，其前缀是xp_。10.1 10.1 存储过程概述存储过程概述2. 存储过程的功能特点存储过程的功能特点nSQL Server的存储过程可实现以下功能：的存储过程可实现以下功能：(1)接收输入参数并以输出参数的形式为调用过

7、程接收输入参数并以输出参数的形式为调用过程或批处理返回多个值。或批处理返回多个值。(2)包含执行数据库操作的编程语句，包括调用其包含执行数据库操作的编程语句，包括调用其他过程。他过程。(3)为调用过程或批处理返回一个状态值，以表示为调用过程或批处理返回一个状态值，以表示成功或失败成功或失败(及失败原因及失败原因)。10.1 10.1 存储过程概述存储过程概述存储过程具有以下优点存储过程具有以下优点(1)模块化编程。模块化编程。(2)快速执行。快速执行。 (3)减少网络通信量。减少网络通信量。(4)提供安全机制。提供安全机制。(5)保证操作一致性。保证操作一致性。10.2.1 创建存储过程创建存

8、储过程10.2.2 执行存储过程执行存储过程10.2.3 修改存储过程修改存储过程10.2.4 删除存储过程删除存储过程10.2.5 存储过程参数与状态值存储过程参数与状态值10.2 存储过程的创建与使用存储过程的创建与使用10.2.1 创建存储过程创建存储过程1. 使用企业管理器创使用企业管理器创建存储过程建存储过程(1)启动企业管理器，启动企业管理器，登录到要使用的服登录到要使用的服务器。务器。(2)选择要创建存储过选择要创建存储过程的数据库，在左程的数据库，在左窗格中单击窗格中单击“存储存储过程过程”文件夹。文件夹。10.2 10.2 存储过程的创建与使用存储过程的创建与使用(3)右击右

9、击“存储过程存储过程”文件文件夹，在弹出菜单中选择夹，在弹出菜单中选择“新建存储过程新建存储过程”选项，选项，此时打开创建存储过程此时打开创建存储过程对话框。对话框。10.2.1 10.2.1 创建存储过程创建存储过程(4)在在“文本文本”编辑框中输编辑框中输入存储过程正文。入存储过程正文。(5)单击单击“检查语法检查语法”按钮，按钮，检查语法是否正确。检查语法是否正确。(6)单击单击“确定确定”按钮，保按钮，保存存储过程。存存储过程。(7)在图在图10-1的右窗格中，的右窗格中，右击该存储过程，在弹右击该存储过程，在弹出菜单中选择出菜单中选择“所有任所有任务务”，选择，选择“管理权管理权限限

10、”，在，在“对象属性对象属性”对话框中设置权限（如对话框中设置权限（如设置设置PUBLIC用户有用户有EXEC权限）。权限）。10.2.1 10.2.1 创建存储过程创建存储过程2. 使用向导创建存储过程使用向导创建存储过程(1)在企业管理器中选择当前在企业管理器中选择当前服务器，然后执行服务器，然后执行“工具工具向导向导”菜单命令，弹出菜单命令，弹出“选择向导选择向导”对话框。对话框。(2)在在“选择向导选择向导”对话框中对话框中展开展开“数据库数据库”项，双击项，双击“创建存储过程向导创建存储过程向导”项，项，弹出弹出“创建存储过程创建存储过程”对对话框。话框。10.2.1 10.2.1

11、创建存储过程创建存储过程(3)单击单击“下一步下一步”按钮，按钮，进入进入“选择数据库选择数据库”对话框对话框 10.2.1 10.2.1 创建存储过程创建存储过程(4)单击单击“下一步下一步”按钮，按钮，进入进入“选择存储过程选择存储过程”对话框。在该对话框中，对话框。在该对话框中，设置该存储过程中的表设置该存储过程中的表要执行的操作，可同时要执行的操作，可同时设置一个或多个操作设置一个或多个操作(用用于插入、删除和更新于插入、删除和更新)。如果选择多个操作，每如果选择多个操作，每个操作都将创建一个存个操作都将创建一个存储过程。这里为储过程。这里为customer表创建插入存表创建插入存储过

12、程。储过程。10.2.1 10.2.1 创建存储过程创建存储过程(5)单击单击“下一步下一步”按钮，按钮，进入进入“完成存储过程创完成存储过程创建建”对话框。在该对话对话框。在该对话框中显示将要创建的存框中显示将要创建的存储过程的名称及其操作，储过程的名称及其操作，这里的存储过程名称是这里的存储过程名称是insert_customer_1。10.2.1 10.2.1 创建存储过程创建存储过程(6)在图在图10-7中选择一个中选择一个存储过程，单击存储过程，单击“编编辑辑”按钮，进入按钮，进入“编编辑存储过程属性辑存储过程属性”对对话框。在此可以重新话框。在此可以重新设置存储过程的名称设置存储过

13、程的名称和要操作的字段。和要操作的字段。10.2.1 10.2.1 创建存储过程创建存储过程(7)如果要通过如果要通过SQL语句对语句对正在创建的存储过程进正在创建的存储过程进行修改，单击行修改，单击“编辑编辑SQL”按钮，弹出按钮，弹出“编辑编辑存储过程存储过程SQL”对话框。对话框。(8)SQL语句编辑完成后，语句编辑完成后，单击单击“分析分析”按钮，将按钮，将对存储过程进行分析。对存储过程进行分析。分析成功后，单击分析成功后，单击“确确定定”按钮，返回图按钮，返回图10-7“完成存储过程完成存储过程”对话对话框，再单击框，再单击“完成完成”按按钮，开始创建存储过程，钮，开始创建存储过程，

14、并弹出创建成功对话框。并弹出创建成功对话框。10.2.1 10.2.1 创建存储过程创建存储过程3. 使用使用CREATE PROCEDURE语句创建存储语句创建存储过程过程n使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程应该考虑以下几个方面：应该考虑以下几个方面：(1)在一个批处理中，在一个批处理中，CREATE PROCEDURE语句不能与语句不能与其他其他SQL语句合并在一起。语句合并在一起。(2)数据库所有者具有默认的创建存储过程的权限，它可数据库所有者具有默认的创建存储过程的权限，它可把该权限传递给其他的用户。把该权限传递给其他的用户。(3)存储过程作为数据

15、库对象其命名必须符合标识符的命存储过程作为数据库对象其命名必须符合标识符的命名规则。名规则。(4)只能在当前数据库中创建属于当前数据库的存储过程。只能在当前数据库中创建属于当前数据库的存储过程。10.2.1 10.2.1 创建存储过程创建存储过程创建存储过程语句的语法格式如下：创建存储过程语句的语法格式如下：CREATE PROCEDURE procedure_name ; number parameter data_type VARYING =default OUTPUT , .n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR

16、 REPLICATION AS sql_statement ,.n 10.2.1 10.2.1 创建存储过程创建存储过程例例10-1 创建存储过程，从表创建存储过程，从表goods和表和表goods_classification的联接中返回商品名、商的联接中返回商品名、商品类别、单价。品类别、单价。10.2.1 10.2.1 创建存储过程创建存储过程CREATE PROCEDURE goods_info ASSELECT goods_name, classification_name, unit_priceFROM goods g INNER JOIN goods_classification

17、 gcON g.classification_id = gc.classification_idn存储过程创建后，存储过程的名称存放在存储过程创建后，存储过程的名称存放在sysobject表中，表中，文本存放在文本存放在syscomments表中。表中。10.2.2 执行存储过程执行存储过程n执行存储过程的语法格式：执行存储过程的语法格式：EXECUTE return_status= procedure_name ;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE 10.2

18、10.2 存储过程的创建与使用存储过程的创建与使用例如，执行例例如，执行例10-1的存储过程的存储过程goods_infon在在SQL查询分析器中输入命令：查询分析器中输入命令：EXEC goods_infon运行的结果：运行的结果：10.2.2 10.2.2 执行存储过程执行存储过程10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.3 修改存储过程修改存储过程n修改存储过程可以通过企业管理器和修改存储过程可以通过企业管理器和Transact SQL语语句实现。句实现。1. 使用企业管理器修改存储过程使用企业管理器修改存储过程(1)在企业管理器中展开服务器组，再展开服务器。

19、在企业管理器中展开服务器组，再展开服务器。(2)展开展开“数据库数据库”文件夹，再展开要修改存储过程的数文件夹，再展开要修改存储过程的数据库。据库。(3)在要修改的存储过程上右击，并在弹出的快捷菜单中在要修改的存储过程上右击，并在弹出的快捷菜单中选择选择“属性属性”项，或者双击要修改的存储过程，弹出项，或者双击要修改的存储过程，弹出“存储过程属性存储过程属性”窗口。窗口。10.2.3 10.2.3 修改存储过程修改存储过程(4)在在“文本文本”框中直接框中直接对其代码进行修改，对其代码进行修改，修改完成后，先检查修改完成后，先检查语法，正确后单击语法，正确后单击“确定确定”按钮。按钮。10.2

20、.3 10.2.3 修改存储过程修改存储过程2. 使用使用ALTER PROCEDURE语句修改存储语句修改存储过程过程nALTER PROCEDURE的语法规则是：的语法规则是：ALTER PROCEDURE procedure_name ; number parameter data_typeVARYING=default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATION AS sql_statement .n 10.2.3 10.2.3 修改存储过程修改存储过程例例10-2 使用使用

21、ALTER PROCEDURE语句更改语句更改存储过程。存储过程。(1)创建存储过程创建存储过程employee_dep，以获取总经理办的，以获取总经理办的男员工。男员工。CREATE PROCEDURE employee_dep AS SELECT employee_name, sex, address, department_nameFROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男男 AND e.department_id=D001GO执行存储过程执行存储过程empl

22、oyee_dep，结果如图，结果如图 10.2.3 10.2.3 修改存储过程修改存储过程(2) 查看查看employee_dep存储过程的文本信息存储过程的文本信息SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = P AND = employee_depGO10.2.3 10.2.3 修改存储过程修改存储过程(3)使用使用ALTER PROCEDURE语句对语句对employee_dep过程进行修改，使其能够显示出过程进行修改，使其能够显示出所有男

23、员工，并使所有男员工，并使employee_dep过程以加密方过程以加密方式存储在表式存储在表syscomments中中ALTER PROCEDURE employee_depWITH ENCRYPTION AS SELECT employee_name, sex, address, department_nameFROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男男 GO10.2.3 10.2.3 修改存储过程修改存储过程执行修改后的存储过程执行修改后的存储过程employ

24、ee_dep，结果，结果如图：如图：10.2.3 10.2.3 修改存储过程修改存储过程(4)从系统表从系统表sysobjects和和syscomments提取修提取修改后的存储过程改后的存储过程employee_dep的文本信息可的文本信息可以运行步骤（以运行步骤（2）中的代码，结果如图）中的代码，结果如图 n这是由于在这是由于在ALTER PROCEDURE语句中使用语句中使用WITH ENCRYPTION关键字对存储过程关键字对存储过程employee_dep的文本进行了加密，其文本信息显的文本进行了加密，其文本信息显示为乱码。示为乱码。10.2 10.2 存储过程的创建与使用存储过程的

25、创建与使用10.2.4 删除存储过程删除存储过程 n存储过程可以被快速删除和重建，因为它没有存存储过程可以被快速删除和重建，因为它没有存储数据。储数据。1. 使用企业管理器删除存储过程使用企业管理器删除存储过程(1)在企业管理器中展开服务器组，再展开相应的服在企业管理器中展开服务器组，再展开相应的服务器。务器。(2)展开展开“数据库数据库”文件夹，再展开要删除存储过程文件夹，再展开要删除存储过程的数据库。的数据库。10.2.4 10.2.4 删除存储过程删除存储过程(3)单击单击“存储过程存储过程”项，在项，在右窗格中右击要删除的存右窗格中右击要删除的存储过程，选择的同时按下储过程，选择的同时

26、按下Ctrl键可以同时选择多个键可以同时选择多个存储过程，在弹击的快捷存储过程，在弹击的快捷菜单中选择菜单中选择“删除删除”项，项，弹出弹出“除去对象除去对象”对话框。对话框。(4)在在“除去对象除去对象”对话框中对话框中单击单击“显示相关性显示相关性”按钮，按钮，可以显示依附该存储过程可以显示依附该存储过程的对象，在确定不影响其的对象，在确定不影响其他对象后单击他对象后单击“全部除去全部除去”按钮，删除所有选择的存按钮，删除所有选择的存储过程。储过程。10.2.4 10.2.4 删除存储过程删除存储过程2. 使用使用DROP PROCEDURE删除存储过程删除存储过程nDROP PROCED

27、URE的语法如下：的语法如下：DROP PROCEDURE procedure_name ,.n n例如删除例例如删除例10-2创建的存储过程创建的存储过程employee_dep：DROP PROCEDURE employee_depGO10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.5 存储过程参数与状态值存储过程参数与状态值n存储过程和调用者之间通过参数交换数据，可以存储过程和调用者之间通过参数交换数据，可以按输入的参数执行，也可由参数输出执行结果。按输入的参数执行，也可由参数输出执行结果。调用者通过存储过程返回的状态值对存储过程进调用者通过存储过程返回的状态值对存

28、储过程进行管理。行管理。1. 参数参数n存储过程的参数在创建过程时声明。存储过程的参数在创建过程时声明。nSQL Server支持两类参数：输入参数和输出参数。支持两类参数：输入参数和输出参数。10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(1)输入参数输入参数n输入参数允许调用程序为存储过程传送数输入参数允许调用程序为存储过程传送数据值。要定义存储过程的输入参数，必须据值。要定义存储过程的输入参数，必须在在CREATE PROCEDURE语句中声明一个语句中声明一个或多个变量及类型。或多个变量及类型。10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例

29、例10-3 创建带参数的存储过程，从表创建带参数的存储过程，从表employee、sell_order、goods、goods_classification的连接中返的连接中返回输入的员工名、该员工销售的商品名、商品类别、回输入的员工名、该员工销售的商品名、商品类别、销售量等信息。销售量等信息。CREATE PROC sell_info employee_name varchar(20)AS SELECT employee_name, goods_name,classification_name, order_numFROM employee e INNER JOIN sell_order s

30、 ON e.employee_id=s.employee_id JOIN goods g ON g.goods_id=s.goods_id JOIN goods_classification gc ON gc.classification_id=g.classification_idWHERE employee_name LIKE employee_name10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值n存储过程存储过程sell_info以以employee_name变量作为输变量作为输入参数，执行时，可以省略参数名，直接给参数入参数，执行时，可以省略参数名，直接给参数值

31、。在值。在SQL查询分析器中输入命令：查询分析器中输入命令：EXEC sell_info 东方牧东方牧n运行结果如图。运行结果如图。n参数值可以包含通配符参数值可以包含通配符“%”，例如，查找所有姓，例如，查找所有姓“钱钱”的员工的销售情况可以使用以下命令：的员工的销售情况可以使用以下命令：nEXEC sell_info 钱钱%10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值执行时，参数可以由位置标识，也可以由名执行时，参数可以由位置标识，也可以由名字标识。字标识。n例如，定义一个具有例如，定义一个具有3个参数的存储过程：个参数的存储过程：CREATE PROC mypro

32、c val1 int, val2 int, val3 intAS .n参数以位置传递：参数以位置传递：EXEC myproc 10,20,15n参数以名字传递，每个值由对应的参数名引导：参数以名字传递，每个值由对应的参数名引导：EXEC myproc val2=20,val1=10,val3=15n按名字传递参数比按位置传递参数具有更大的灵活性。但按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。是，按位置传递参数却具有更快的速度。10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(2)输出参数输出参数n输出参数允许存储过程将数据值或游标变

33、输出参数允许存储过程将数据值或游标变量传回调用程序。量传回调用程序。nOUTPUT关键字用以指出能返回到调用它关键字用以指出能返回到调用它的批处理或过程中的参数。为了使用输出的批处理或过程中的参数。为了使用输出参数，在参数，在CREATE PROCEDURE和和EXECUTE语句中都必须使用语句中都必须使用OUTPUT关关键字。键字。10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例例10-4 创建存储过程创建存储过程price_goods，通过输入，通过输入参数在参数在goods表中查找商品，以输出参数获取表中查找商品，以输出参数获取商品单价。商品单价。CREATE PR

34、OC price_goods goods_name varchar(80)=NULL, price_goods real OUTPUTASSELECT price_goods=unit_priceFROM goodsWHERE goods_name=goods_name10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值n执行执行price_goods存储过程的代码如下：存储过程的代码如下：DECLARE price realEXEC price_goods Canon LBP2900,price OUTPUTSELECT pricen运行结果是商品名为运行结果是商品名为Can

35、on LBP2900的商的商品单价：品单价：1380.0EXECUTE语句还需要关键字语句还需要关键字OUTPUT以允以允许参数值返回给变量。许参数值返回给变量。10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(1)用用RETURN语句定义返回值语句定义返回值n存储过程可以返回整型状态值，表示过程是否成存储过程可以返回整型状态值，表示过程是否成功执行，或者过程失败的原因。功执行，或者过程失败的原因。n如果存储过程没有显式设置返回代码的值，则如果存储过程没有显式设置返回代码的值，则SQL Server返回代码为返回代码为 0，表示成功执行；若返，表示成功执行；若返回回-1-9

36、9之间的整数，表示没有成功执行。也可之间的整数，表示没有成功执行。也可以使用以使用RETURN语句，用大于语句，用大于0或小于或小于-99的整数的整数来定义自己的返回状态值，以表示不同的执行结来定义自己的返回状态值，以表示不同的执行结果。果。2. 返回存储过程的状态返回存储过程的状态10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例例10-5 创建存储过程，输入商品类别，返回各种商创建存储过程，输入商品类别，返回各种商品名称。在存储过程中，用值品名称。在存储过程中，用值15表示用户没有提供表示用户没有提供参数；值参数；值-l01表示没有输入商品类别；值表示没有输入商品类别；

37、值0表示过程表示过程运行没有出错。运行没有出错。CREATE PROC cl_goods cl_name varchar(40)=NULLASIF cl_name=NULL RETURN 15IF NOT EXISTS (SELECT * FROM goods_classification WHERE classification_name=cl_name) RETURN -101SELECT g.goods_name FROM goods_classification gc,goods gWHERE gc.classification_id =g.classification_id AND

38、gc.classification_name=cl_nameRETURN 010.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值n在执行过程时，要正确接收返回的状态值，在执行过程时，要正确接收返回的状态值，必须使用以下语句；必须使用以下语句；EXECUTE status_var=procedure_name(2)捕获返回状态值捕获返回状态值10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值DECLARE return_status intEXEC return_status=cl_goods 笔记本计算机笔记本计算机IF return_status=15 SE

39、LECT 语法错误语法错误ELSE IF return_status=-101 SELECT 没有找到该商品类别没有找到该商品类别n执行时，将对不同的输入值返回不同的状态值及执行时，将对不同的输入值返回不同的状态值及处理结果。处理结果。例例10-5的存储过程的存储过程cl_goods执行时使用以下语执行时使用以下语句：句：n触发器是一种特殊类型的存储过程。触发器是一种特殊类型的存储过程。n触发器主要是通过事件进行触发而被执行的，而触发器主要是通过事件进行触发而被执行的，而存储过程可以通过过程名字直接调用。当对某一存储过程可以通过过程名字直接调用。当对某一表进行表进行UPDATE、INSERT、

40、DELETE操作时，操作时，SQL Server就会自动执行触发器所定义的就会自动执行触发器所定义的SQL语语句，从而确保对数据的处理必须符合由这些句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。语句所定义的规则。n触发器的主要作用就是能够实现由主键和外键所触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和数据的一致性。不能保证的参照完整性和数据的一致性。 10.3 触发器概述触发器概述10.4.1 创建触发器创建触发器10.4.2 删除触发器删除触发器10.4.3 修改触发器修改触发器10.4 触发器的创建与使用触发器的创建与使用10.4 10.4 触发器的创建与

41、使用触发器的创建与使用1. 使用企业管理器创建触发器使用企业管理器创建触发器(1)启动企业管理器，登录到指定的服务器上。启动企业管理器，登录到指定的服务器上。(2)展开数据库，选择要在其上创建触发器的表所在展开数据库，选择要在其上创建触发器的表所在的数据库，单击该表（如的数据库，单击该表（如customer）。）。10.4.1 创建触发器创建触发器10.4.1 10.4.1 创建触发器创建触发器(3)在选择的数据表上在选择的数据表上右击鼠标，在弹出右击鼠标，在弹出菜单上选择菜单上选择“所有所有任务任务”选项。单击选项。单击“管理触发器管理触发器”，弹出弹出“触发器属性触发器属性”对话框。对话框

42、。10.4.1 10.4.1 创建触发器创建触发器(4)在在“触发器属性触发器属性”对对话框的名称列表框中话框的名称列表框中选择选择“新建新建”项，在项，在文本框中输入触发器文本框中输入触发器文本。单击文本。单击“检查语检查语法法”检查语句是否正检查语句是否正确。确。(5)在在“触发器属性触发器属性”对对话框中单击话框中单击“应用应用”按钮，在名称下拉列按钮，在名称下拉列表中会有新创建的触表中会有新创建的触发器名字。单击发器名字。单击“确确定定”按钮，关闭窗口，按钮，关闭窗口，触发器创建成功。触发器创建成功。10.4.1 10.4.1 创建触发器创建触发器2. 使用使用CREATE TRIGG

43、ER语句创建触发器语句创建触发器nCREATE TRIGGER语句的语法格式如下：语句的语法格式如下：CREATE TRIGGER trigger_name ON table_name | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statement . n 10.4.1 10.4.1 创建触发器创建触发器例例10-6 在在employee表上创建一个表上创建一个DELETE类类型的触发器，该触发器的名称为型的触发器，该触发器的名称为tr_employee。(1)创建触发器创建触发

44、器tr_employeeCREATE TRIGGER tr_employee ON employeeFOR DELETEAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+个员工被删除个员工被删除SELECT msgRETURN10.4.1 10.4.1 创建触发器创建触发器(2)执行触发器执行触发器tr_employeen触发器不能通过名字来执行，而是在相应的触发器不能通过名字来执行，而是在相应的SQL语句被执行时自动触发的。例如执行以下语句被执行时自动触发的。例如执行以下DELETE语句：语句：DELETE FROM employeeWH

45、ERE employee_name = 张三张三n该语句要删除员工姓名为该语句要删除员工姓名为“张三张三”记录，由此激记录，由此激活了表活了表employee 的的DELETE类型的触发器类型的触发器tr_employee，系统执行，系统执行tr_employee触发器中触发器中AS之后的语句，并显示以下信息：之后的语句，并显示以下信息：1个员工被删除个员工被删除10.4.1 10.4.1 创建触发器创建触发器n在触发器的执行过程中，在触发器的执行过程中，SQL Server建立和管理两个临时建立和管理两个临时的虚拟表：的虚拟表：Deleted表和表和Inserted表。这两个表包含了在激表。

46、这两个表包含了在激发触发器的操作中插入或删除的所有记录。在执行发触发器的操作中插入或删除的所有记录。在执行INSERT或或UPDATE语句之后所有被添加或被更新的记录语句之后所有被添加或被更新的记录都会存储在都会存储在Inserted表中。表中。n在执行在执行DELETE或或UPDATE语句时，从触发程序表中被删语句时，从触发程序表中被删除的行会发送到除的行会发送到Deleted表。表。n对于更新操作，对于更新操作，SQL Server先将要进行修改的记录存储到先将要进行修改的记录存储到Deleted表中，然后再将修改后的数据复制到表中，然后再将修改后的数据复制到Inserted表以表以及触发

47、程序表。及触发程序表。3. Deleted表和表和Inserted表表10.4.1 10.4.1 创建触发器创建触发器例例10-7 为表为表customer创建一个名为创建一个名为test_tr的的触发器，当执行添加、更新或删除时，激活触发器，当执行添加、更新或删除时，激活该触发器。该触发器。n创建创建test_tr触发器：触发器：CREATE TRIGGER test_trON customer FOR INSERT,UPDATE,DELETEAS SELECT * FROM inserted SELECT * FROM deletedncustomer表执行以下插入操作：表执行以下插入操作

48、：INSERT INTO customer(customer_id,customer_name,telphone)VALUES(12346,张三张三,1234567)10.4.1 10.4.1 创建触发器创建触发器nINSERT操作激活触发器操作激活触发器test_tr，输出如图，输出如图10-19所示的表格。所示的表格。10.4 10.4 触发器的创建与使用触发器的创建与使用10.4.2 删除触发器删除触发器1. 使用企业管理器删除触发器使用企业管理器删除触发器(1)启动企业管理器，登录到指定的服务器。启动企业管理器，登录到指定的服务器。(2)选择数据库和表，其操作过程与创建触发选择数据库和

49、表，其操作过程与创建触发器相似，参见图器相似，参见图10-17。10.4.2 10.4.2 删除触发器删除触发器(3)从快捷菜单项中选从快捷菜单项中选择择“所有任务所有任务管管理触发器理触发器”选项，选项，打开打开“触发器属性触发器属性”对话框。对话框。(4)在在“触发器属性触发器属性”对话框的对话框的“名称名称”列表中选择要删除列表中选择要删除的触发器名称，再的触发器名称，再选择选择“删除删除”按钮按钮完成删除操作。完成删除操作。10.4.2 10.4.2 删除触发器删除触发器2. 使用使用DROP TRIGGER删除指定触发器删除指定触发器n删除触发器语句的语法格式如下：删除触发器语句的语

50、法格式如下：DROP TRIGGER trigger_name , .n n例如，删除例例如，删除例10-6的触发器的触发器tr_employee：DROP TRIGGER tr_employee10.4.3 修改触发器修改触发器n通过企业管理器、系统存储过程或通过企业管理器、系统存储过程或Transact_SQL语句，可以修改触发器的名语句，可以修改触发器的名字和正文。字和正文。1. 使用使用sp_rename系统存储过程修改触发器的系统存储过程修改触发器的名字：名字：sp_rename oldname, newname10.4 10.4 触发器的创建与使用触发器的创建与使用10.4.3 1

51、0.4.3 修改触发器修改触发器2. 使用企业管理器修改触发器的正文使用企业管理器修改触发器的正文操作步骤如下：操作步骤如下：(1)启动企业管理器，登录到指定的服务器。启动企业管理器，登录到指定的服务器。(2)选择数据库和表，其操作过程与创建触发器相似，选择数据库和表，其操作过程与创建触发器相似，参见图参见图10-17。(3)从快捷菜单项中选择从快捷菜单项中选择“所有任务所有任务管理触发器管理触发器”选项，打开选项，打开“触发器属性触发器属性”对话框，参见图对话框，参见图10-20。(4)在在“触发器属性触发器属性”对话框的对话框的“名称名称”列表中选择列表中选择要修改的触发器名，在要修改的触

52、发器名，在“文本文本”编辑框中可以修编辑框中可以修改选择的触发器正文。再使用改选择的触发器正文。再使用“检查语法检查语法”选项选项对语法进行检查。对语法进行检查。10.4.3 10.4.3 修改触发器修改触发器3. 使用使用ALTER TRIGGER语句修改触发器语句修改触发器n修改触发器的语法如下：修改触发器的语法如下：ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFDELETE , INSERT , UPDATE AS sql_statement .n 10.4.3 10.

53、4.3 修改触发器修改触发器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+个员工数据个员工数据被插入被插入SELECT msgRETURNn对对employee表执行以下插入语句：表执行以下插入语句：INSERT employee(employee_id,employee_name)VALUES (E016,王五王五)n激活激活INSERT触发器触发器tr_employee，显示信息：，显示信息：1个员工数据被插入个员工数据被插入例如，将例例如

54、，将例10-6的触发器的触发器tr_employee修改为修改为INSERT操作后进行。操作后进行。10.5.1 事务概述事务概述10.5.2 事务管理事务管理10.5 事务处理事务处理10.5 10.5 事务处理事务处理n事务事务(Transaction)是是SQL Server中的一个逻中的一个逻辑工作单元，该单元将被作为一个整体进辑工作单元，该单元将被作为一个整体进行处理。行处理。n事务保证连续多个操作必须全部执行成功，事务保证连续多个操作必须全部执行成功，否则必须立即回复到未执行任何操作的状否则必须立即回复到未执行任何操作的状态，即执行事务的结果要不全部将数据所态，即执行事务的结果要不

55、全部将数据所要执行的操作完成，要不全部数据都不修要执行的操作完成，要不全部数据都不修改。改。10.5.1 事务概述事务概述10.5.1 10.5.1 事务概述事务概述n例如，企业取消了仓储部，需要将例如，企业取消了仓储部，需要将“仓储部仓储部”从从department表中删除，而表中删除，而employee表中的部门编号与仓储表中的部门编号与仓储部相对应的员工也应删除。部相对应的员工也应删除。假设仓储部编号为假设仓储部编号为D004，第一条，第一条DELETE语句修改语句修改department表表为：为：DELETE FROM department WHERE department_id =

56、 D004第二条第二条DELETE语句修改语句修改employee表为：表为：DELETE FROM employee WHERE department_id = D004n因此，必须保证这两条因此，必须保证这两条DELETE语句同时执行，或都不执语句同时执行，或都不执行。这时可以使用数据库中的事务行。这时可以使用数据库中的事务(Transaction)技术来实技术来实现。现。1事务的由来事务的由来2事务属性事务属性n由于事务作为一个逻辑工作单元，当事务由于事务作为一个逻辑工作单元，当事务执行遇到错误时，将取消事务所做的修改。执行遇到错误时，将取消事务所做的修改。一个逻辑单元必须具有一个逻辑单

57、元必须具有4个属性：个属性：原子性原子性(Atomicity)、一致性一致性(Consistency)隔离性隔离性(Isolation)持久性持久性(Durability)，n这些属性称为这些属性称为ACID。10.5.1 10.5.1 事务概述事务概述3事务模式事务模式nSQL Server以以3种事务模式管理事务。种事务模式管理事务。(1) 自动提交事务模式：每条单独的语句都是一个事务。自动提交事务模式：每条单独的语句都是一个事务。在此模式下，每条在此模式下，每条Transact-SQL语句在成功执行完成语句在成功执行完成后，都被自动提交，如果遇到错误，则自动回滚该语后，都被自动提交，如果

58、遇到错误，则自动回滚该语句。该模式为系统默认的事务管理模式。句。该模式为系统默认的事务管理模式。(2) 显式事务模式：该模式允许用户定义事务的启动和结显式事务模式：该模式允许用户定义事务的启动和结束。事务以束。事务以BEGIN TRANSACTION语句显式开始，以语句显式开始，以COMMIT或或ROLLBACK语句显式结束。语句显式结束。(3) 隐性事务模式：在当前事务完成提交或回滚后，新事隐性事务模式：在当前事务完成提交或回滚后，新事务自动启动。隐性事务不需要使用务自动启动。隐性事务不需要使用BEGIN TRANSACTION语句标识事务的开始，但需要以语句标识事务的开始，但需要以COMM

59、IT或或ROLLBACK语句来提交或回滚事务。语句来提交或回滚事务。10.5.1 10.5.1 事务概述事务概述10.5 10.5 事务处理事务处理1启动和结束事务启动和结束事务n启动事务语句的语法格式如下：启动事务语句的语法格式如下：BEGIN TRANSACTION transaction_name | tran_name_variable WITH MARK description n结束事务语句的语法格式如下：结束事务语句的语法格式如下：COMMIT TRANSACTION transaction_name | tran_name_variable 10.5.2 事务管理事务管理10.

60、5.2 10.5.2 事务管理事务管理例例10-8 建立一个显式事务以显示建立一个显式事务以显示Sales数据库数据库的的employee表的数据。表的数据。BEGIN TRANSACTION SELECT * FROM employee COMMIT TRANSACTIONn本例创建的事务以本例创建的事务以BEGIN TRANSACTION语句语句开始，以开始，以COMMIT TRANSACTION语句结束。语句结束。10.5.2 10.5.2 事务管理事务管理DECLARE transaction_name varchar(32)SELECT transaction_name=tran_d