存储过程与触发器.pptx

本章内容本章内容10.1 存储过程存储过程概述概述10.2 存储过程的创建与使用存储过程的创建与使用10.3 触发器触发器概述概述10.4 触发器的创建与使用触发器的创建与使用10.5 事务处理事务处理10.6 SQL Server的的锁机制锁机制10.1 存储过程概述存储过程概述 n存储过程存储过程是是SQL Server服务器上一组预编服务器上一组预编译的译的Transact-SQL语句，用于完成某项任语句，用于完成某项任务，它可以接受参数、返回状态值和参数务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。值，并且可以嵌套调用。10.1 10.1 存储过程概述存储过程概述nSQL Server存储过程的类型包括：存储过程的类型包括：系统存储过程系统存储过程用户定义存储过程用户定义存储过程临时存储过程临时存储过程扩展存储过程。扩展存储过程。1.存储过程的类型存储过程的类型10.1 10.1 存储过程概述存储过程概述(1)系统存储过程系统存储过程 n是指由系统提供的存储过程，主要存储在是指由系统提供的存储过程，主要存储在master数据库中并数据库中并以以sp_为前缀为前缀，它从系统，它从系统表中获取信息，从而为系统管理员管理表中获取信息，从而为系统管理员管理SQL Server提供支持。提供支持。如：如：sp_helptext、sp_rename。10.1 10.1 存储过程概述存储过程概述(2)用户定义存储过程用户定义存储过程n是由用户创建并能完成某一特定功能（例是由用户创建并能完成某一特定功能（例如查询用户所需数据信息）的存储过程。如查询用户所需数据信息）的存储过程。注意：注意：存储过程名前不能有前缀存储过程名前不能有前缀sp_。10.1 10.1 存储过程概述存储过程概述(3)临时存储过程临时存储过程n临时存储过程与临时表类似，存储过程名临时存储过程与临时表类似，存储过程名称前面添加称前面添加“#”。当。当SQL Server关闭后，关闭后，这些临时存储过程将自动被删除。这些临时存储过程将自动被删除。10.1 10.1 存储过程概述存储过程概述(4)扩展存储过程扩展存储过程n扩展存储过程是扩展存储过程是SQL Server可以动态装载可以动态装载和执行的动态链接库和执行的动态链接库(DLL)。当扩展存储过。当扩展存储过程加载到程加载到SQL Server中，它的使用方法与中，它的使用方法与系统存储过程一样。扩展存储过程只能添系统存储过程一样。扩展存储过程只能添加到加到master数据库中，其前缀是数据库中，其前缀是xp_。10.1 10.1 存储过程概述存储过程概述2.存储过程具有以下优点存储过程具有以下优点(5)保证操作一致性保证操作一致性。(1)模块化编程模块化编程。(2)快速执行。快速执行。(3)减少网络通信量减少网络通信量。(4)提供安全机制。提供安全机制。10.2.1 创建创建存储过程存储过程10.2.2 执行执行存储过程存储过程10.2.3 修改修改存储过程存储过程10.2.4 删除删除存储过程存储过程10.2.5 存储存储过程参数过程参数与状态值与状态值10.2 存储过程的创建与使用存储过程的创建与使用在在SQL Server 中建立存储过程的两种方法：中建立存储过程的两种方法：一是一是使用企业管理器；使用企业管理器；二是二是使用使用Create Procedure语句。语句。10.2.1 创建存储过程创建存储过程1.使用企业管理器创建存储过程使用企业管理器创建存储过程例：例：建立存储过程建立存储过程stu_pro，查询所有女生的基本信息。，查询所有女生的基本信息。语法格式：语法格式：CREATE PROCEDURECREATE PROCEDURE 存储存储过程名过程名 WITHWITH ENCRYPTION ENCRYPTION ASAS sql_statement,.n sql_statement,.n2.使用使用CREATE PROCEDURE语句创建存储过程语句创建存储过程其中：其中：WITH ENCRYPTIONWITH ENCRYPTION：对定义存储过程的的语句文本进行加密。：对定义存储过程的的语句文本进行加密。sql_statementssql_statements：存储过程中实现功能的存储过程中实现功能的SQLSQL语句。语句。语法格式：语法格式：EXEC 存储过程名称存储过程名称 10.2.2 执行存储过程执行存储过程【例例】创建一存储过程创建一存储过程xs_proxs_pro，要求该存储过程返回学生，要求该存储过程返回学生姓名、所学课程名称和成绩的信息。姓名、所学课程名称和成绩的信息。CREATE PROCEDURECREATE PROCEDURE xs_pro xs_proASAS SELECT SELECT 姓名姓名,课名课名,成绩成绩 FROM FROM 学生学生 xs xs INNER JOININNER JOIN 选课选课 xk xk ONON xs.xs.学号学号 =xk.=xk.学号学号 INNER JOININNER JOIN 课程课程 kc kc ONON xk.xk.课号课号 =kc.=kc.课号课号如要执行该存储过程，可在查询分析器中执行如下语句：如要执行该存储过程，可在查询分析器中执行如下语句：EXECEXEC xs_proxs_pro【练习练习】创建存储过程创建存储过程cj_proccj_proc，查询每科的课号、最高分、，查询每科的课号、最高分、最低分、平均分信息。最低分、平均分信息。创建存储过程创建存储过程rs_procrs_proc，统计每科及格的人数。，统计每科及格的人数。create procedure cj_proc as select 课号课号,max(成绩成绩)最高分最高分,min(成绩成绩)最低分最低分 from 选课表选课表 group by 课号课号create procedure rs_procas select 课号课号,count(*)人数人数 from 选课表选课表 where 成绩成绩=60 group by 课号课号1.用用T-SQL语句查看存储过程语句查看存储过程 查看存储过程的定义查看存储过程的定义 sp_helptext 存储过程名存储过程名查看存储过程查看存储过程 查看有关存储过程的信息查看有关存储过程的信息 sp_help 存储过程名存储过程名2.使用企业管理器查看存储过程使用企业管理器查看存储过程 展开服务器组，然后展开服务器。展开服务器组，然后展开服务器。展开展开“数据库数据库”文件夹，展开存储过程所属的数据库，文件夹，展开存储过程所属的数据库，然后单击然后单击“存储过程存储过程”文件夹。文件夹。在详细信息窗格中，在详细信息窗格中，右击需要查看的存储过程右击需要查看的存储过程，然后单，然后单击击“属性属性”命令。也可以直接双击存储过程也可。命令。也可以直接双击存储过程也可。该存储过程的定义代码显示在该存储过程的定义代码显示在“存储过程属性存储过程属性”窗口的窗口的文本框中。文本框中。创建和执行存储过程创建和执行存储过程proc_grade,proc_grade,查询选课表中选修查询选课表中选修2 2号号课学生的学号、成绩信息，并使存储过程不能使用课学生的学号、成绩信息，并使存储过程不能使用sp_helptextsp_helptext查看。查看。CREATE PROCCREATE PROC proc_grade proc_grade WITH WITH encryption encryption AS AS SELECT SELECT 学号学号,成绩成绩 FROMFROM 选课表选课表 WHEREWHERE 课号课号=2=2GOGOsp_helptextsp_helptext proc_grade proc_grade【练习练习】10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.3 修改存储过程修改存储过程1.使用企业管理器修改存储过程使用企业管理器修改存储过程例：例：修改存储过程修改存储过程stu_pro，查询，查询“软件软件”专业学生的姓专业学生的姓名。名。10.2.3 10.2.3 修改存储过程修改存储过程2.使用使用ALTER PROCEDURE语句修改存储过程语句修改存储过程ALTERALTER PROCEDUREPROCEDURE 存储过程名存储过程名 WITH ENCRYPTIONWITH ENCRYPTION AS AS sql_statement .n sql_statement .n 创建存储过程创建存储过程proc_sjk,proc_sjk,获取选修获取选修“程序设计程序设计”课的课的所有学生的姓名，成绩信息。所有学生的姓名，成绩信息。修改存储修改存储过程过程proc_sjk，使其能够显示出选修该课的，使其能够显示出选修该课的女生的姓名、成绩信息，并对其加密。女生的姓名、成绩信息，并对其加密。【练习练习】create procedure pro_sjkas select 姓名姓名,成绩成绩 from 学生表学生表 xs inner join 选课表选课表 xk on xs.学号学号=xk.学号学号 inner join 课程表课程表 kc on xk.课号课号=kc.课号课号 where 课名课名=程序设计程序设计alter procedure pro_sjkwith encryptionas select 姓名姓名,成绩成绩 from 学生表学生表 xs inner join 选课表选课表 xk on xs.学号学号=xk.学号学号 inner join 课程表课程表 kc on xk.课号课号=kc.课号课号 where 课名课名=程序设计程序设计 and 性别性别=女女10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.4 删除存储过程删除存储过程 1.使用企业管理器删除存储过程使用企业管理器删除存储过程例：例：删除存储过程删除存储过程stu_pro。10.2.4 10.2.4 删除存储过程删除存储过程2.使用使用DROP PROCEDURE删除存储过程删除存储过程n语法如下：语法如下：DROP PROCEDURE 存储过程名存储过程名,.n 10.2 10.2 存储过程的创建与使用存储过程的创建与使用10.2.5 存储过程参数与状态值存储过程参数与状态值1.参数参数n存储过程的参数在创建过程时声明。存储过程的参数在创建过程时声明。nSQL Server支持两类参数：支持两类参数：输入参数输入参数和和输出参数输出参数。10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(1)输入参数输入参数CREATE PROCEDURECREATE PROCEDURE 存储过程名存储过程名 参数名参数名1 1 数据类型数据类型 长度长度,小数位小数位，WITHWITH ENCRYPTIONENCRYPTION ASAS sql_statement,.n sql_statement,.n10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例例10-310-3 创建带参数的存储过程，从表创建带参数的存储过程，从表employeeemployee、sell_ordersell_order、goodsgoods、goods_classificationgoods_classification的连接中返回输入的员工名、该的连接中返回输入的员工名、该员工销售的商品名、商品类别、销售量等信息。员工销售的商品名、商品类别、销售量等信息。CREATE PROCCREATE PROC sell_info sell_info employee_name varchar(20)employee_name varchar(20)ASAS SELECTSELECT employee_name,goods_name,employee_name,goods_name,classification_name,order_num classification_name,order_num FROM FROM employee e employee e INNER JOININNER JOIN sell_order s sell_order s ONON e.employee_id=s.employee_id e.employee_id=s.employee_id INNER INNER JOINJOIN goods g goods g ONON g.goods_id=s.goods_id g.goods_id=s.goods_id INNERINNER JOINJOIN goods_classification gc goods_classification gc ONON gc.classification_id=g.classification_id gc.classification_id=g.classification_id WHERE WHERE employee_name employee_name LIKE LIKE employee_nameemployee_name10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值n执行时，可以省略参数名，直接给参数值。在执行时，可以省略参数名，直接给参数值。在SQL查询分查询分析器中输入命令：析器中输入命令：EXEC sell_info 东方牧东方牧n运行结果如图。运行结果如图。n参数值可以包含通配符参数值可以包含通配符“%”，例如，查找所有姓，例如，查找所有姓“钱钱”的员工的销售情况可以使用以下命令：的员工的销售情况可以使用以下命令：EXEC sell_info 钱钱%10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值执行时，参数可以由位置标识，也可以由名字标识。执行时，参数可以由位置标识，也可以由名字标识。n例如，定义一个具有例如，定义一个具有3个参数的存储过程：个参数的存储过程：CREATE PROC myproc val1 int,val2 int,val3 intAS.n参数以位置传递：参数以位置传递：EXEC myproc 10,20,15n参数以名字传递，每个值由对应的参数名引导：参数以名字传递，每个值由对应的参数名引导：EXEC myproc val2=20,val1=10,val3=15n按名字传递参数比按位置传递参数具有更大的灵活性。但按名字传递参数比按位置传递参数具有更大的灵活性。但是，按位置传递参数却具有更快的速度。是，按位置传递参数却具有更快的速度。【例例】建立一个存储过程，根据所给学生的姓名查询该学建立一个存储过程，根据所给学生的姓名查询该学生的信息。生的信息。CREATE PROC xs_proc xm varchar(8)AS SELECT *from 学生表学生表 WHERE 姓名姓名=xmGOEXEC xs_proc 李明李明创建带参数的存储过程创建带参数的存储过程proc_list，根据输入的课号，查询选，根据输入的课号，查询选课表中该科成绩排名前课表中该科成绩排名前3位的学生的学号和成绩信息。位的学生的学号和成绩信息。执行该存储过程，查询课号为执行该存储过程，查询课号为 1的成绩排名前的成绩排名前3位学生。位学生。CREATE PROCEDURE proc_listkcbh char(1)AS SELECT TOP 3 学号学号,成绩成绩 FROM 选课表选课表 WHERE 课程编号课程编号=kcbh ORDER BY 分数分数 DESCGOEXEC proc_list 1【练习练习】10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(2)输出参数输出参数CREATE PROCEDURECREATE PROCEDURE 存储过程名存储过程名 参数名参数名1 1 数据类型数据类型 长度长度,小数位小数位,输出参数输出参数1 1 数据类型数据类型 长度长度,小数位小数位 OUTPUTOUTPUT,WITHWITH ENCRYPTIONENCRYPTION ASAS sql_statement,.n sql_statement,.n10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例例10-4 创建存储过程创建存储过程price_goods，通过输入，通过输入参数在参数在goods表中查找商品，以输出参数获取表中查找商品，以输出参数获取商品单价。商品单价。CREATE PROC price_goods goods_name varchar(80),price_goods real OUTPUT AS SELECT price_goods=unit_price FROM goods WHERE goods_name=goods_name10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值n执行执行price_goods存储过程的代码如下：存储过程的代码如下：DECLARE price realEXEC price_goods Canon LBP2900,price OUTPUTSELECT pricen运行结果是商品名为运行结果是商品名为Canon LBP2900的商品的商品单价：单价：1380.0EXEC语句还需要关键字语句还需要关键字OUTPUT以允许参数值以允许参数值返回给变量。返回给变量。【例例】创建一个实现两个整数加法运算并将运算结果作为输出创建一个实现两个整数加法运算并将运算结果作为输出参数的存储过程参数的存储过程spadd。CREATE PROCEDURE spAddValue1 INT,Value2 INT,ResultValue INT OUTPUTASSELECT ResultValue=Value1+Value2GODECLARE result intEXEC spAdd 100,20,result OUTPUTSELECT result创建带输入和输出参数的存储过程创建带输入和输出参数的存储过程proc_avg，根据输入的课号，查，根据输入的课号，查询选课表中该科的最高分、最低分、平均分。询选课表中该科的最高分、最低分、平均分。执行该存储过程，查询课号为执行该存储过程，查询课号为2的学生的最高分、最低分和平均的学生的最高分、最低分和平均分。分。CREATE PROC proc_avgkcbh char(4),max_fs float OUTPUT,min_fs float OUTPUT,avg_fs float OUTPUTAS SELECT max_fs=MAX(成绩成绩),min_fs=MIN(成绩成绩),avg_fs=AVG(成绩成绩)FROM 选课表选课表 WHERE 课号课号=kcbhGODECLARE max float,min float,avg floatEXEC proc_avg 0002,max OUTPUT,min OUTPUT,avg OUTPUTSELECT max AS 最高分最高分,min AS 最低分最低分,avg AS 平均分平均分【练习练习】10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值(1)用用RETURN语句定义返回值语句定义返回值n如果存储过程没有显式设置返回代码的值，则如果存储过程没有显式设置返回代码的值，则SQL Server 返回代码为返回代码为 0，表示成功执行，表示成功执行；返回返回-1-99之间的整数，表示没有成功执行。之间的整数，表示没有成功执行。n也可以使用也可以使用RETURN语句，用大于语句，用大于0或小于或小于-99的的整数来定义自己的返回状态值，以表示不同的执整数来定义自己的返回状态值，以表示不同的执行结果。行结果。2.返回存储过程的状态返回存储过程的状态10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值例例10-510-5 创建存储过程，输入商品类别，返回各种商品名称。创建存储过程，输入商品类别，返回各种商品名称。在存储过程中，用值在存储过程中，用值1515表示用户没有提供参数；值表示用户没有提供参数；值-l01-l01表示表示没有输入商品类别；值没有输入商品类别；值0 0表示过程运行没有出错。表示过程运行没有出错。CREATE PROC cl_goods cl_name varchar(40)=NULL AS IF cl_name=NULL RETURN 15 IF NOT EXISTS (SELECT *FROM goods_classification WHERE classification_name=cl_name)RETURN -101 SELECT g.goods_name FROM goods_classification gc,goods g WHERE gc.classification_id=g.classification_id AND gc.classification_name=cl_name RETURN 0CREATE PROC spzd x int,y intASIF xy RETURN 0ELSE RETURN -101GODECLARE result intEXEC result=spzd 10,20SELECT result结果结果:-101【练习练习】10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值n在执行过程时，要正确接收返回的状态值，必须在执行过程时，要正确接收返回的状态值，必须使用以下语句；使用以下语句；EXEC status_var=procedure_name(2)捕获返回状态值捕获返回状态值10.2.5 10.2.5 存储过程参数与状态值存储过程参数与状态值DECLARE return_status intEXEC return_status=cl_goods 笔记本计算机笔记本计算机IF return_status=15 SELECT 语法错误语法错误ELSE IF return_status=-101 SELECT 没有找到该商品类别没有找到该商品类别n执行时，将对不同的输入值返回不同的状态值及执行时，将对不同的输入值返回不同的状态值及处理结果。处理结果。例例10-5的存储过程的存储过程cl_goods执行时使用以下语句：执行时使用以下语句：n触发器（触发器（TriggerTrigger）1)1)触发器是一种实施复杂触发器是一种实施复杂数据完整性数据完整性的特殊存储过的特殊存储过程，在对表或视图执行程，在对表或视图执行 UPDATEUPDATE、INSERT INSERT 或或 DELETE DELETE 语句时语句时自动触发执行自动触发执行。2 2）触发器也有三种类型：触发器也有三种类型：INSERTINSERT触发器、触发器、UPDATEUPDATE触发器、触发器、DELETEDELETE触发器触发器。10.3 触发器概述触发器概述10.4.1 创建触发器创建触发器10.4.2 删除触发器删除触发器10.4.3 修改触发器修改触发器10.4 触发器的创建与使用触发器的创建与使用10.4 10.4 触发器的创建与使用触发器的创建与使用1.使用企业管理器创建触发器使用企业管理器创建触发器右击要设置触发器的表，在弹出菜单上选择右击要设置触发器的表，在弹出菜单上选择“所有任务所有任务”选选项。单击项。单击“管理触发器管理触发器”，弹出，弹出“触发器属性触发器属性”对话框。对话框。10.4.1 创建触发器创建触发器例：例：创建触发器创建触发器stu_tri，当往学生表中插入记录时，显示，当往学生表中插入记录时，显示学生表，查看是否插入成功。学生表，查看是否插入成功。10.4.1 10.4.1 创建触发器创建触发器2.使用使用CREATE TRIGGER语句创建触发器语句创建触发器n语法格式如下：语法格式如下：CREATE TRIGGER 触发器名触发器名 ON 表名表名|视图名视图名 WITH ENCRYPTION FOR INSERT ,UPDATE ,DELETEAS sql_statement .n 10.4.1 10.4.1 创建触发器创建触发器例例10-6 在在employee表上创建一个表上创建一个DELETE类型类型的触发器，该触发器的名称为的触发器，该触发器的名称为tr_employee。(1)创建触发器创建触发器tr_employeeCREATE TRIGGER tr_employee ON employeeFOR DELETEAS DECLARE msg varchar(50)SELECT msg=STR(ROWCOUNT)+个员工被删除个员工被删除 SELECT msg RETURN10.4.1 10.4.1 创建触发器创建触发器(2)执行触发器执行触发器tr_employeen触发器不能通过名字来执行，而是在相应的触发器不能通过名字来执行，而是在相应的SQL语句语句被执行时自动触发的。例如执行以下被执行时自动触发的。例如执行以下DELETE语句：语句：DELETE FROM employeeWHERE employee_name=张三张三n显示以下信息：显示以下信息：1个员工被删除个员工被删除1、创建创建UPDATE触发器触发器up_grade，当选课表中成绩列数据被修改，当选课表中成绩列数据被修改时，显示提示信息时，显示提示信息“用户了修改了成绩值用户了修改了成绩值”。CREATE up_gradeON 选课表选课表FORAS IF UPDATE(成绩成绩)BEGIN PRINT 用户修改了成绩值用户修改了成绩值 ENDGOUPDATE 选课表选课表 SET 成绩成绩=90 WHERE 学号学号=890163 and 课号课号=1TRIGGERUPDATE【练习练习】2、在选课表上创建一个在选课表上创建一个UPDATE、DELETE类型的触发器类型的触发器up_tri，当对选课表进行更新或删除操作时，显示提示信息：，当对选课表进行更新或删除操作时，显示提示信息：X条记录已被改动了。条记录已被改动了。create trigger up_tri on 选课表选课表 for update,delete as select str(rowcount)+已被改动了已被改动了10.4.1 10.4.1 创建触发器创建触发器n对于对于UPDATE操作，操作，SQL Server先将要进行修改的记录存储先将要进行修改的记录存储到到Deleted表中，然后再将修改后的数据复制到表中，然后再将修改后的数据复制到Inserted表中。表中。3.Deleted表和表和Inserted表表n 在执行在执行DELETE语句时，从触发的表中被删除的行会发送到语句时，从触发的表中被删除的行会发送到 Deleted表。表。n在触发器的执行过程中，在触发器的执行过程中，SQL Server自动建立两个虚拟表：自动建立两个虚拟表：Deleted表和表和Inserted表。表。n 在执行在执行INSERT语句之后所有被添加的记录都会存储在语句之后所有被添加的记录都会存储在 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 deleted10.4.1 10.4.1 创建触发器创建触发器ncustomer表执行以下插入操作：表执行以下插入操作：INSERT INTO customer(customer_id,customer_name,telphone)VALUES(12346,张三张三,1234567)1、建立建立DELETE触发器触发器del_st_g，当学生表中的记录被删除时，当学生表中的记录被删除时,选选课表中的所有相应记录能自动删除。课表中的所有相应记录能自动删除。CREATE TRIGGER del_st_gON 学生表学生表FOR DELETEAS BEGIN DELETE FROM 选课表选课表 WHERE 学号学号 IN(SELECT 学号学号 from deleted)ENDGODELETE FROM 学生表学生表 WHERE 学号学号=890163【练习练习】CREATE TRIGGER tr_up ON 课程表课程表FOR UPDATEAS UPDATE 选课表选课表 SET 课程编号课程编号=(SELECT 课程编号课程编号 FROM inserted)WHERE 课号课号=(SELECT 课号课号 FROM deleted)GOUPDATE 课程表课程表 SET 课号课号=5 WHERE 课号课号=12、在课程表中建立在课程表中建立UPDATE触发器触发器tr_up，如果更新课程表中的，如果更新课程表中的课号，则相应更新选课表的课号。课号，则相应更新选课表的课号。再进行再进行UPDATE操作，将课程表中值为操作，将课程表中值为“1”的课号值修改为的课号值修改为“5”，查看相应的选课表的记录值是否改变。，查看相应的选课表的记录值是否改变。【练习练习】3、为选课表建立一个名为为选课表建立一个名为insert_tr的的INSERT触发器，当用触发器，当用户向选课表中插入记录时，如果插入的是在课程表中没有的户向选课表中插入记录时，如果插入的是在课程表中没有的课号，则提示用户不能插入记录，否则提示记录插入成功。课号，则提示用户不能插入记录，否则提示记录插入成功。【练习练习】create trigger insert_tr on 选课表选课表 for insert as if exists(select*from 课程表课程表 where 课号课号 in (select 课号课号 from inserted)select 插入成功！插入成功！else select 用户不能插入记录用户不能插入记录!10.4 10.4 触发器的创建与使用触发器的创建与使用10.4.2 删除触发器删除触发器1.使用企业管理器删除触发器使用企业管理器删除触发器例：例：删除触发器删除触发器stu_tri。10.4.2 10.4.2 删除触发器删除触发器2.使用使用DROP TRIGGER删除指定触发器删除指定触发器n删除触发器语句的语法格式如下：删除触发器语句的语法格式如下：DROP TRIGGER 触发器名触发器名 ,.n 例如例如，删除例，删除例10-6的触发器的触发器tr_employee：DROP TRIGGER tr_employee10.4.3 修改触发器修改触发器1.修改触发器的名字修改触发器的名字：sp_rename oldname,newname2.使用企业管理器修改触发器的正文使用企业管理器修改触发器的正文10.4.3 10.4.3 修改触发器修改触发器3.使用使用ALTER TRIGGER语句修改触发器语句修改触发器n修改触发器的语法如下：修改触发器的语法如下：ALTER TRIGGER 触发器名触发器名 ON 表名表名|视图名视图名WITH ENCRYPTION FOR DELETE,INSERT,UPDATEAS sql_statement .n 10.4.3 10.4.3 修改触发器修改触发器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50)SELECT msg=STR(ROWCOUNT)+个员工数据被插入个员工数据被插入 SELECT msg RETURN对对employee表执行以下插入语句：表执行以下插入语句：INSERT employee(employee_id,employee_name)VALUES(E016,王五王五)激活激活INSERT触发器触发器tr_employee，显示信息：，显示信息：1个员工数据被插入个员工数据被插入例如，例如，将例将例10-6的触发器的触发器tr_employee修改为修改为INSERT操作后进行。操作后进行。10.5.1 事务概述事务概述10.5.2 事务管理事务管理10.5 事务处理事务处理10.5.1 10.5.1 事务概述事务概述例如，企业取消了仓储部，需要将例如，企业取消了仓储部，需要将“仓储部仓储部”从从department表表中中 删除，而删除，而employee表中的部门编号与仓储部相对应的员表中的部门编号与仓储部相对应的员 工也应删除。工也应删除。假设仓储部编号为假设仓储部编号为D004，第一条，第一条DELETE语句修改语句修改department表为：表为：DELETE FROM department WHERE department_id=D004第二条第二条DELETE语句修改语句修改employee表为：表为：DELETE FROM employee WHERE department_id=D004因此，必须保证这两条因此，必须保证这两条DELETE语句同时执行，或都不执行。这时可以语句同时执行，或都不执行。这时可以使用数据库中的事务使用数据库中的事务(Transaction)技术来实现。技术来实现。1事务的由来事务的由来 事务事务(Transaction),是指一个操作序列，这些操作序列要么都是指一个操作序列，这些操作序列要么都被执行，要么都不被执行，它是一个不可分割的工作单元。被执行，要么都不被执行，它是一个不可分割的工作单元。10.5.1 事务概述事务概述2事务属性事务属性n由于事务作为一个逻辑工作单元，当事务由于事务作为一个逻辑工作单元，当事务执行遇到错误时，将取消事务所做的修改。执行遇到错误时，将取消事务所做的修改。一个逻辑单元必须具有一个逻辑单元必须具有4个属性：个属性：原子性原子性(Atomicity)、一致性一致性(Consistency)隔离性隔离性(Isolation)持久性持久性(Durability)，n这些属性称为这些属性称为ACID。10.5.1 10.5.1 事务概述事务概述3事务模式事务模式nSQL Server以以3种事务模式管理事务。种事务模式管理事务。(1)自动提交事务模式自动提交事务模式：每条单独的语句都是一个事务每条单独的语句都是一个事务。在此模式下，每条。在此模式下，每条Transact-SQL语句在成功执行完成后，都被自动提交，如果遇到错误，语句在成功执行完成后，都被自动提交，如果遇到错误，则自动回滚该语句。该模式为系统默认的事务管理模式。则自动回滚该语句。该模式为系统默认的事务管理模式。(2)显式事务模式显式事务模式：该模式允许用户定义事务的启动和结束该模式允许用户定义事务的启动和结束。事务以。事务以BEGIN TRANSACTION语句显式开始，以语句显式开始，以COMMIT或或ROLLBACK语语句显式结束。句显式结束。(3)隐性事务模式隐性事务模式：在当前事务完成提交或回滚后，新事务自动启动。在当前事务完成提交或回滚后，新事务自动启动。隐性事务不隐性事务不需要使用需要使用BEGIN TRANSACTION语句标识事务的开始，但需语句标识事务的开始，但需要以要以COMMIT或或ROLLBACK语句来提交或回滚事务。语句来提交或回滚事务。10.5.1 10.5.1 事务概述事务概述10.5 10.5 事务处理事务处理1启动和结束事务启动和结束事务n启动事务语句的语法格式如下：启动事务语句的语法格式如下：BEGIN TRANSACTION