[第10章-存储过程与触发器-课件.ppt

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使用使用SQL Server管理平台创管理平台创建存储过程建存储过程（1）打开）打开SQL Server管理平台，管理平台，展开节点展开节点“对象资源管理对象资源管理器器”“数据库服务数据库服务器器”“可编程性可编程性”“存储存储过程过程”，在窗口的右侧显示出，在窗口的右侧显示出当前数据库的所有存储过程。当前数据库的所有存储过程。单击鼠标右

9、键，在弹出的快捷单击鼠标右键，在弹出的快捷菜单中选择菜单中选择“新建存储过程新建存储过程”命令命令。10.2 10.2 存储过程的创建与使用存储过程的创建与使用（2）在打开的）在打开的SQL命令命令窗口中，系统给出了创窗口中，系统给出了创建存储过程命令的模板，建存储过程命令的模板，如图如图10-2所示。在模板所示。在模板中可以输入创建存储过中可以输入创建存储过程的程的Transact-SQL语语句后，单击句后，单击“执行执行”按按钮即可创建存储过程。钮即可创建存储过程。10.2.1 10.2.1 创建存储过程创建存储过程（3）建立存储过程的）建立存储过程的命令被成功执行后，命令被成功执行后，在

10、在“对象资源管理对象资源管理器器”“数据库服务数据库服务器器”“可编程可编程性性”“存储过程存储过程”中可以看到新建立的中可以看到新建立的存储过程存储过程 10.2.1 10.2.1 创建存储过程创建存储过程2.使用使用CREATE PROCEDURE语句创建存储语句创建存储过程过程n使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程应该考虑以下几个方面：应该考虑以下几个方面：(1)在一个批处理中，在一个批处理中，CREATE PROCEDURE语句不能语句不能与其他与其他SQL语句合并在一起。语句合并在一起。(2)数据库所有者具有默认的创建存储过程的权限，它可数据库所有

11、者具有默认的创建存储过程的权限，它可把该权限传递给其他的用户。把该权限传递给其他的用户。(3)存储过程作为数据库对象其命名必须符合标识符的命存储过程作为数据库对象其命名必须符合标识符的命名规则。名规则。(4)只能在当前数据库中创建属于当前数据库的存储过程。只能在当前数据库中创建属于当前数据库的存储过程。10.2.1 10.2.1 创建存储过程创建存储过程创建存储过程语句的语法格式如下：创建存储过程语句的语法格式如下：CREATE PROCEDURE procedure_name;number parameter data_type VARYING=default OUTPUT,.n WITH

12、RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR 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_priceFR

13、OM goods g INNER JOIN goods_classification 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 O

14、UTPUT|DEFAULT ,.n WITH RECOMPILE 10.2 10.2 存储过程的创建与使用存储过程的创建与使用例如，执行例例如，执行例10-1的存储过程的存储过程goods_infon在在SQL查询分析器中输入命令：查询分析器中输入命令：EXEC goods_infon运行的结果：运行的结果：10.2.2 10.2.2 执行存储过程执行存储过程10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.3 修改存储过程修改存储过程n修改存储过程可以通过修改存储过程可以通过SQL Server管理平管理平台和台和Transact-SQL语句实现。语句实现。1使用使用SQ

15、L Server管理平台修改存储过程管理平台修改存储过程 10.2.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

16、.3 修改存储过程修改存储过程例例10-2 使用使用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

17、=D001GO执行存储过程执行存储过程employee_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 o.name=employee_depGO10.2.3 10.2.3 修改存储过程修改存储过程(3)使用使用ALTER PROCEDURE语句对语句对employee_dep过程进行修改，使其能够显示出过程

18、进行修改，使其能够显示出所有男员工，并使所有男员工，并使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 修改存储过程修改存储过程执行修改后的存储过程执行修改

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

20、的创建与使用存储过程的创建与使用10.2.4 删除存储过程删除存储过程 n存储过程可以被快速删除和重建，因为它没有存存储过程可以被快速删除和重建，因为它没有存储数据。储数据。1使用使用SQL Server管理平台删除存储过程管理平台删除存储过程（1）打开SQL Server管理平台，展开节点“对象资源管理器”“数据库服务器”“可编程性”“存储过程”，选择要删除的存储过程，单击鼠标右键，在弹出的快捷菜单中选择“删除”命令。（2）在弹出的“删除对象”对话框中单击“确定”按钮即可删除存储过程。10.2.4 10.2.4 删除存储过程删除存储过程2.使用使用DROP PROCEDURE删除存储过程删除

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

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

23、与状态值存储过程参数与状态值例例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

24、 sell_order s 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变量作为输变量作为输入参数，执行时，可以省略参数名，直接给参数入参数，执行时，可

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

26、TE PROC myproc 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输出参数允许存储

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

28、。CREATE PROC 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运行结果是商品名为运行结

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

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

31、有输入商品类别；值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 AN

32、D 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

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

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

35、与使用触发器的创建与使用1使用使用SQL Server管理平台创建触发器管理平台创建触发器 10.4.1 创建触发器创建触发器10.4.1 10.4.1 创建触发器创建触发器2.使用使用CREATE TRIGGER语句创建触发器语句创建触发器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 创建触

36、发器创建触发器例例10-6 在在employee表上创建一个表上创建一个DELETE类类型的触发器，该触发器的名称为型的触发器，该触发器的名称为tr_employee。(1)创建触发器创建触发器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触发器不能通过名字来执行，而是在

37、相应的触发器不能通过名字来执行，而是在相应的SQL语句被执行时自动触发的。例如执行以下语句被执行时自动触发的。例如执行以下DELETE语句：语句：DELETE FROM employeeWHERE employee_name=张三张三n该语句要删除员工姓名为该语句要删除员工姓名为“张三张三”记录，由此激记录，由此激活了表活了表employee 的的DELETE类型的触发器类型的触发器tr_employee，系统执行，系统执行tr_employee触发器中触发器中AS之后的语句，并显示以下信息：之后的语句，并显示以下信息：1个员工被删除个员工被删除10.4.1 10.4.1 创建触发器创建触发器

38、n在触发器的执行过程中，在触发器的执行过程中，SQL Server建立和管理两个临时建立和管理两个临时的虚拟表：的虚拟表：Deleted表和表和Inserted表。这两个表包含了在激表。这两个表包含了在激发触发器的操作中插入或删除的所有记录。在执行发触发器的操作中插入或删除的所有记录。在执行INSERT或或UPDATE语句之后所有被添加或被更新的记录语句之后所有被添加或被更新的记录都会存储在都会存储在Inserted表中。表中。n在执行在执行DELETE或或UPDATE语句时，从触发程序表中被删语句时，从触发程序表中被删除的行会发送到除的行会发送到Deleted表。表。n对于更新操作，对于更新

39、操作，SQL Server先将要进行修改的记录存储到先将要进行修改的记录存储到Deleted表中，然后再将修改后的数据复制到表中，然后再将修改后的数据复制到Inserted表以表以及触发程序表。及触发程序表。3.Deleted表和表和Inserted表表10.4.1 10.4.1 创建触发器创建触发器例例10-7 为表为表customer创建一个名为创建一个名为test_tr的的触发器，当执行添加、更新或删除时，激活触发器，当执行添加、更新或删除时，激活该触发器。该触发器。n创建创建test_tr触发器：触发器：CREATE TRIGGER test_trON customer FOR INS

40、ERT,UPDATE,DELETEAS SELECT*FROM inserted SELECT*FROM deletedncustomer表执行以下插入操作：表执行以下插入操作：INSERT INTO customer(customer_id,customer_name,telphone)VALUES(12346,张三张三,1234567)10.4.1 10.4.1 创建触发器创建触发器nINSERT操作激活触发器操作激活触发器test_tr，输出如图，输出如图10-19所示的表格。所示的表格。10.4.2 修改触发器修改触发器n通过通过SQL Server管理平台、系统存储过程管理平台、系统

41、存储过程或或Transact_SQL语句，可以修改触发器的语句，可以修改触发器的名字和正文。名字和正文。1.使用使用sp_rename系统存储过程修改触发器的系统存储过程修改触发器的名字：名字：sp_rename oldname,newname10.4 10.4 触发器的创建与使用触发器的创建与使用10.4.3 10.4.3 修改触发器修改触发器2使用使用SQL Server管理平台修改触发器的正文管理平台修改触发器的正文 n修改触发器的操作步骤如下：修改触发器的操作步骤如下：（1）打开）打开SQL Server管理平台，展开节点管理平台，展开节点“对象资源管理对象资源管理器器”“Sales”

42、数据库数据库|“表表”“customer”表表|“触触发器发器”，选择要删除的触发器（如例，选择要删除的触发器（如例10-7创建的创建的test_tr触触发器），单击鼠标右键，在弹出的快捷菜单中选择发器），单击鼠标右键，在弹出的快捷菜单中选择“修改修改”命令。命令。（2）此时在右边的编辑器窗口中出现触发器的源代码（将）此时在右边的编辑器窗口中出现触发器的源代码（将CREATE TRIGGER改为了改为了ALTER TRIGGER），可以直），可以直接进行修改。修改完后单击工具栏中的接进行修改。修改完后单击工具栏中的“执行执行”按钮执行按钮执行该触发器代码，从而达到目的。该触发器代码，从而达到目

43、的。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.4.3 修改触发器修改触发器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50)S

44、ELECT msg=STR(ROWCOUNT)+个员工数据个员工数据被插入被插入SELECT msgRETURNn对对employee表执行以下插入语句：表执行以下插入语句：INSERT employee(employee_id,employee_name)VALUES(E016,王五王五)n激活激活INSERT触发器触发器tr_employee，显示信息：，显示信息：1个员工数据被插入个员工数据被插入例如，将例例如，将例10-6的触发器的触发器tr_employee修改为修改为INSERT操作后进行。操作后进行。10.4 10.4 触发器的创建与使用触发器的创建与使用10.4.3 删除触发器

45、删除触发器1使用使用SQL Server管理平台删除触发器管理平台删除触发器 n操作步骤如下：操作步骤如下：n（1）打开）打开SQL Server管理平台，展开节点管理平台，展开节点“对对象资源管理器象资源管理器”“Sales”数据库数据库|“表表”“customer”表表|“触发器触发器”，选择要，选择要删除的触发器（如例删除的触发器（如例10-7创建的创建的test_tr触发器），触发器），单击鼠标右键，在弹出的快捷菜单中选择单击鼠标右键，在弹出的快捷菜单中选择“删除删除”命令。命令。n（2）在弹出的）在弹出的“删除对象删除对象”对话框中单击对话框中单击“确确定定”按钮即可删除触发器。按钮

46、即可删除触发器。10.4.2 10.4.2 删除触发器删除触发器2.使用使用DROP TRIGGER删除指定触发器删除指定触发器n删除触发器语句的语法格式如下：删除触发器语句的语法格式如下：DROP TRIGGER trigger_name ,.n n例如，删除例例如，删除例10-6的触发器的触发器tr_employee：DROP TRIGGER tr_employee10.5.1 事务概述事务概述10.5.2 事务管理事务管理10.5 事务处理事务处理10.5 10.5 事务处理事务处理n事务事务(Transaction)是是SQL Server中的一个逻中的一个逻辑工作单元，该单元将被作为

47、一个整体进辑工作单元，该单元将被作为一个整体进行处理。行处理。n事务保证连续多个操作必须全部执行成功，事务保证连续多个操作必须全部执行成功，否则必须立即回复到未执行任何操作的状否则必须立即回复到未执行任何操作的状态，即执行事务的结果要不全部将数据所态，即执行事务的结果要不全部将数据所要执行的操作完成，要不全部数据都不修要执行的操作完成，要不全部数据都不修改。改。10.5.1 事务概述事务概述10.5.1 10.5.1 事务概述事务概述n例如，企业取消了仓储部，需要将例如，企业取消了仓储部，需要将“仓储部仓储部”从从department表中删除，而表中删除，而employee表中的部门编号与仓储

48、表中的部门编号与仓储部相对应的员工也应删除。部相对应的员工也应删除。假设仓储部编号为假设仓储部编号为D004，第一条，第一条DELETE语句修改语句修改department表表为：为：DELETE FROM department WHERE department_id=D004第二条第二条DELETE语句修改语句修改employee表为：表为：DELETE FROM employee WHERE department_id=D004n因此，必须保证这两条因此，必须保证这两条DELETE语句同时执行，或都不执语句同时执行，或都不执行。这时可以使用数据库中的事务行。这时可以使用数据库中的事务(Tr

49、ansaction)技术来实技术来实现。现。1事务的由来事务的由来2事务属性事务属性n由于事务作为一个逻辑工作单元，当事务由于事务作为一个逻辑工作单元，当事务执行遇到错误时，将取消事务所做的修改。执行遇到错误时，将取消事务所做的修改。一个逻辑单元必须具有一个逻辑单元必须具有4个属性：个属性：原子性原子性(Atomicity)、一致性一致性(Consistency)隔离性隔离性(Isolation)持久性持久性(Durability)，n这些属性称为这些属性称为ACID。10.5.1 10.5.1 事务概述事务概述3事务模式事务模式nSQL Server以以3种事务模式管理事务。种事务模式管理事

50、务。(1)自动提交事务模式：每条单独的语句都是一个事务。自动提交事务模式：每条单独的语句都是一个事务。在此模式下，每条在此模式下，每条Transact-SQL语句在成功执行完成语句在成功执行完成后，都被自动提交，如果遇到错误，则自动回滚该语后，都被自动提交，如果遇到错误，则自动回滚该语句。该模式为系统默认的事务管理模式。句。该模式为系统默认的事务管理模式。(2)显式事务模式：该模式允许用户定义事务的启动和结显式事务模式：该模式允许用户定义事务的启动和结束。事务以束。事务以BEGIN TRANSACTION语句显式开始，以语句显式开始，以COMMIT或或ROLLBACK语句显式结束。语句显式结束