[计算机软件及应用]第5章 存储过程与触发器ppt课件

1、1第第5 5章章 存储过程与触发器存储过程与触发器 n主要内容主要内容n两个重要的数据库对象n存储过程存储过程n存储过程的根本概念n存储过程的各种创立和维护方法n各种存储过程的调用方法n触发器触发器n触发器的根本概念n各种类型触发器的创立方法n触发器的应用和维护12第第5 5章章 存储过程与触发器存储过程与触发器n存储过程存储过程Stored ProcedureStored Proceduren具有一个名字、存储在数据库中、经过预编译的T-SQL语句集合n包含逻辑、对数据库的查询n支持控制流，具备过程处理才能，支持过程调用n可以独立执行或通过应用程序的调用来执行n一旦创立，在效劳器上即被编译，

2、可在需要时执行屡次n存储过程的特点存储过程的特点 n代码简单化n代码重用性n高速性n网络流量小n平安性存储过程23第第5 5章章 存储过程与触发器存储过程与触发器n系统存储过程系统存储过程System Stored ProcedureSystem Stored Proceduren存储在master数据库中n以以sp_sp_为前缀为前缀n通过从系统表中获取信息提供给系统管理员n执行SQL Server许多管理活动的特殊存储过程n例：sp_adduser系统存储n以以xp_xp_为前缀称为常规扩展存储过程为前缀称为常规扩展存储过程n为 SQL Server 与外部应用程序之间的接口n利用他们可进

3、展各种维护活动n例：xp_cmdshell存储过程34第第5 5章章 存储过程与触发器存储过程与触发器n存储过程与函数区别存储过程与函数区别n存储过程可以返回一个称为返回码的整型值，说明过程是否成功完成n而大多数函数可以返回其它值n存储过程要返回其它数据，包括结果集时，需使用输出参数n存储过程的调用语句假设不是批处理的第一条语句，不能直接调用，必须用“EXEC 过程名参数或CALL 过程名参数的形式来调用n而函数的调用可直接使用“函数名参数的形式调用存储过程45第第5 5章章 存储过程与触发器存储过程与触发器n存储过程的创立存储过程的创立 n建立存储过程来执行表的各种操作n存储过程作为数据库的

4、对象存在数据库中n创立存储过程时，应指定三项内容创立存储过程时，应指定三项内容n所有输入参数和向调用过程或批处理返回的输出参数n执行数据库操作包括调用其它过程的编程语句n返回至调用过程或批处理以说明成功或失败以及失败原因的状态值返回码 存储过程56第第5 5章章 存储过程与触发器存储过程与触发器n用用T-SQLT-SQL创立存储过程创立存储过程nCREATE PROCEDURE n , ,n OUTPUT nAS 67第第5 5章章 存储过程与触发器存储过程与触发器n其中其中n：过程中的参数，参数名前为号。可声明一个或多个参数。在未提供参数的默认值时，调用过程时要提供参数值n：参数的数据类型n

5、：参数的默认值。指定默认值后，过程执行时用户不必提供参数值。默认值必须是常量或 NULL，假设使用LIKE关键字，那么默认值中可以使用通配符nOUTPUT：说明该参数为返回参数。该参数值可以将信息返回给调用程序nAS：指定过程要执行的操作n：过程中包含的T-SQL 语句 存储过程78第第5 5章章 存储过程与触发器存储过程与触发器n例例5-15-1：创立第一个存储过程：创立第一个存储过程存储过程- 建立第一个存储过程，查询学生人数USE 学生学籍数据库GO - 判断存储过程是否存在，假设存在先删除它IF EXISTS SELECT name FROM sysobjects WHERE name

6、 =MyFirstSPAND type=P DROP PROCEDURE MyFirstSPGO CREATE PROCEDURE MyFirstSP AS DECLARE Con INT SELECT Con=COUNT* FROM 学生根本信息 PRINT Con SELECT * FROM 学生根本信息GO- 执行存储过程EXEC MyFirstSP89第第5 5章章 存储过程与触发器存储过程与触发器nsysobjectssysobjects表表typetype字段的取值字段的取值 值值含义含义值值含义含义CCHECK约束R规那么D默认值或DEFAULT约束S系统表FFOREIGN KE

7、Y约束TF表函数FN标量函数TR触发器IF内嵌表函数U用户表KPRIMARY KEY或UNIQUE约束V视图P存储过程X扩展存储过程存储过程910第第5 5章章 存储过程与触发器存储过程与触发器n例例5-25-2：创立带参数的存储过程：创立带参数的存储过程n存储过程通过其参数与调用程序进展通讯n每个参数都有名称、数据类型、方向和默认值n当程序执行存储过程时，通过输入参数将数据传递给存储过程，通过OUTPUT参数将数据返回至调用程序存储过程- 建立存储过程，按照性别查询学生的姓名USE 学生学籍数据库GO IF EXISTS SELECT name FROM sysobjects WHERE n

8、ame =FindSexAND type=P DROP PROCEDURE FindSexGO - 创立过程，输入参数为学生性别，得到该性别的学生姓名CREATE PROCEDURE FindSexStuSex CHAR2AS SELECT S.姓名 FROM 学生根本信息 AS S WHERE S.性别 = StuSexGO- 执行过程，得到男同学的姓名DECLARE MS INTEXEC FindSex 男1011第第5 5章章 存储过程与触发器存储过程与触发器n例例5-35-3：创立带：创立带OUTPUTOUTPUT参数的存储过程参数的存储过程存储过程- 求男同学的总人数USE 学生学籍

9、数据库GOIF EXISTSSELECT name FROM sysobjects WHERE name =GetMaleCount AND type=P DROP PROCEDURE GetMaleCountGO- 创立过程，求男同学的总人数CREATE PROCEDURE GetMaleCount MS INT OUTPUT AS SELECT MS = COUNT* FROM 学生根本信息 AS S WHERE S.性别 = 男GO- 执行过程，得到学生的总人数DECLARE MS INTEXEC GetMaleCount MS OUTPUTPRINT 男同学总人数为：+ CASTMS

10、AS CHAR结果为：结果为： 男同学的总人数为：71112第第5 5章章 存储过程与触发器存储过程与触发器n例例5-45-4：创立带返回值的存储过程：创立带返回值的存储过程n在存储过程中使用 RETURN 语句，可将一个返回值返回给调用程序n假设RETURN 未指定值，那么存储过程返回 0nRETURN 语句可无条件地终止存储过程，其后的语句将不会被执行。n返回值表示执行成功0或失败非0存储过程- 按照学号查询该学生姓名- 创立过程，输入参数为学生学号，返回码为该学生是否存在CREATE PROCEDURE GetName StuNO INT AS DECLARE ErrorSave INT

11、 -声明变量ErrorSave用于存储返回码 SET ErrorSave = 0 SELECT S.姓名 FROM 学生根本信息 AS S WHERE S.学号 = StuNO IF ERROR 0 - ERROR值存储于ErrorSave SET ErrorSave = ERROR RETURN ErrorSave -假设SELECT语句未出现错误，返回0，否那么返回错误号GO1213第第5 5章章 存储过程与触发器存储过程与触发器n结果为结果为n在“网格窗格中显示该学生的姓名为“齐霖n在“消息窗格中显示“返回码为： 0存储过程- 执行过程，得到该学号的学生姓名及存储过程返回值DECLARE

12、 ReturnStatus INTEXECUTE ReturnStatus = GetName 02002- 显示返回值PRINT 返回码为： + CASTReturnStatus AS CHAR10GO1314第第5 5章章 存储过程与触发器存储过程与触发器n例例5-55-5：创立根据返回情况的不同而返回不同返回码的存储过程：创立根据返回情况的不同而返回不同返回码的存储过程n创立一个根据其姓名获得其家庭地址的存储过程n该过程中各种返回情况的返回码值如表所示值值含义含义0成功执行1未指定所需的参数值2指定的参数值无效3获得地址值时出现错误4发现地址值为NULL存储过程1415第第5 5章章 存

13、储过程与触发器存储过程与触发器存储过程-按照学生姓名查询该学生的家庭地址-创立过程，输入参数为学生姓名，得到其家庭地址，返回码表示各种返回情况CREATE PROCEDURE GetAddress StuName varchar20 = NULLAS DECLARE StuAddress VARCHAR50 IF StuName IS NULL - 判断是否未提供姓名参数 BEGIN PRINT 错误：请提供学生的姓名！ RETURN1 END ELSE BEGIN - 判断该学生是否存在 IF SELECT COUNT* FROM 学生根本信息 AS S WHERE S.姓名 = StuNa

14、me = 0 BEGIN PRINT 提示：该学生名字有误，或该学生不存在！ RETURN2 END1516第第5 5章章 存储过程与触发器存储过程与触发器n续前页代码续前页代码存储过程 - 查询该学生的家庭地址，赋给输出参数 StuAddress SELECT StuAddress=家庭地址 FROM 学生根本信息 AS S WHERE S.姓名 =StuName - 判断查询中是否存在问题 IF ERROR 0 BEGIN PRINT 提示：查询过程中出错！ RETURN3 END ELSE - 判断该学生的家庭地址是否为NULL IF StuAddress IS NULL BEGIN P

15、RINT 提示：该学生家庭地址为空！ RETURN4 END -成功查询出该学生的家庭地址！ ELSE BEGIN PRINT 查询成功！ RETURN0 END ENDGO1617第第5 5章章 存储过程与触发器存储过程与触发器n执行语句假设为执行语句假设为nEXEC GetAddress，那么在“消息格中显示“错误：请提供学生的姓名！n执行语句假设为执行语句假设为：nDECLARE returnval intnEXEC returnval=GetAddress 高得非nprint returnvaln那么在那么在“消息消息窗格中显示窗格中显示n提示：该学生家庭地址为空！n4n执行语句假设为

16、执行语句假设为nDECLARE returnval intnEXEC returnval=GetAddress 张军nprint returnvaln那么在那么在“消息消息窗格中显示窗格中显示n查询成功！n0存储过程1718第第5 5章章 存储过程与触发器存储过程与触发器n例例5-65-6：使用：使用LIKELIKE、通配符和参数默认值的存储过程、通配符和参数默认值的存储过程存储过程-求“龙姓学生的根本情况USE 学生学籍数据库GOIF EXISTSSELECT name FROM sysobjects WHERE name =GetStuInfoAND type=P DROP PROCEDU

17、RE GetStuInfoGO- 创立过程，返回“龙姓学生的家庭地址CREATE PROCEDURE GetStuInfo SNAME VARCHAR20 = 龙% AS SELECT S.* FROM 学生根本信息 AS S WHERE S.姓名 LIKE SNAMEGO- 执行过程，得到“龙姓学生的根本情况EXEC GetStuInfoGO 1819第第5 5章章 存储过程与触发器存储过程与触发器n例例5-75-7：使用参数默认值：使用参数默认值NULLNULL创立存储过程创立存储过程n将参数默认值指定为NULL值，来判断存储过程调用时，用户是否提供参数值存储过程- 求某学生的出生日期-

18、创立过程，输入某学生的姓名，得到该学生的出生日期。- 当未输入学生姓名时，显示“请输入学生的姓名！CREATE PROCEDURE GetStuBirth SNAME VARCHAR20 = NULLAS IF SNAME IS NULL PRINT 请输入学生的姓名！ ELSE SELECT S.姓名, S. 出生日期 FROM 学生根本信息 AS S WHERE S.姓名= SNAMEGO- 执行过程USE 学生学籍数据库EXEC GetStuBirth- 未提供参数时，会显示：请输入学生的姓名！GOEXEC GetStuBirth 张军1920第第5 5章章 存储过程与触发器存储过程与触

19、发器n使用企业管理器创立存储过程使用企业管理器创立存储过程存储过程2021第第5 5章章 存储过程与触发器存储过程与触发器n使用企业管理器的使用企业管理器的“创立存储过程向导创立存储过程向导创立存储过程创立存储过程存储过程2122第第5 5章章 存储过程与触发器存储过程与触发器存储过程2223第第5 5章章 存储过程与触发器存储过程与触发器n执行存储过程执行存储过程 nEXECUTE n= ,= ,| OUTPUT|DEFAULTn其中其中n：过程中参数的值n：用来保存参数或者返回参数的变量nOUTPUT：指定存储过程必须返回一个参数nDEFAULT：根据过程的定义，提供参数的默认值存储过程2

20、324第第5 5章章 存储过程与触发器存储过程与触发器n修改存储过程修改存储过程n使用企业管理器修改和重命名存储过程n使用ALTER PROCEDURE更改存储过程n可以删除重建n可以在原有存储过程根底上修改存储过程2425第第5 5章章 存储过程与触发器存储过程与触发器n例例5-145-14：检索所有学分为：检索所有学分为4 4的课程，修改该过程为检索所有学分为的课程，修改该过程为检索所有学分为3 3的课程的课程USE 学生学籍数据库GOIF EXISTSSELECT name FROM sysobjects WHERE name=GetCreditAND type = P DROP PRO

21、CEDURE GetCreditGOUSE 学生学籍数据库GOCREATE PROCEDURE GetCreditAS SELECT * FROM 课程根本信息 WHERE 学分 = 4GO- 修改该存储过程ALTER PROCEDURE GetCreditAS SELECT * FROM 课程根本信息 WHERE 学分 = 3GO2526第第5 5章章 存储过程与触发器存储过程与触发器n触发器触发器 n触发器Trigger是一种特殊类型的存储过程，用户或应用程序不直接调用执行它，而是在手工或通过应用程序对表或视图进展插入、删除和更新操作n执行UPDATE、INSERT 或 DELETE命令时

22、自动执行n主要功能是维护表中数据的完好性，检查对表所做的修改是否允许或进展一些特殊的自动化操作n一个表或视图可以有多个触发器触发器2627第第5 5章章 存储过程与触发器存储过程与触发器n触发器的创立触发器的创立n使用企业管理器创立触发器2728第第5 5章章 存储过程与触发器存储过程与触发器n使用使用T-SQLT-SQL创立触发器创立触发器nCREATE TRIGGER ON | n FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATEnASnn其中其中n | ：在其上执行触发器的表或视图，称为触发器表或视图nFOR子句指定触发器的执行时间nAFTER：触发器在

23、SQL语句中的所有操作成功执行后以及所有引用级联操作和约束检查成功完成后激活n假如仅指定 FOR 关键字，那么 AFTER 是默认设置nINSTEAD OF：用触发器中的操作替代触发语句的操作触发器2829第第5 5章章 存储过程与触发器存储过程与触发器n DELETE , INSERT , UPDATE n在表或视图上执行相应修改语句时激活触发器n至少需指定一项n指定多项时用逗号分隔，顺序任意nAS：触发器要执行的操作nn触发器的条件和操作触发器2930第第5 5章章 存储过程与触发器存储过程与触发器n例例5-175-17：当试图在：当试图在“学生根本信息学生根本信息表中删除或更改数据时，显

24、示表中删除或更改数据时，显示警告消息。警告消息。触发器USE 学生学籍数据库IF EXISTS SELECT name FROM sysobjects WHERE name = StuInfoChangeRem AND type = TR DROP TRIGGER StuInfoChangeRemGOCREATE TRIGGER StuInfoChangeRem ON 学生根本信息FOR UPDATE,DELETEAS RAISERROR 50001, 16, 10GO3031第第5 5章章 存储过程与触发器存储过程与触发器n上例中使用的上例中使用的5000150001号消息的定义号消息的定义

25、nEXEC sp_addmessage 50001,16, 注意:学生根本信息表正在被试图修改或删除！,us_Englishnsp_addmessagesp_addmessagensp_addmessage , ,replacen其中其中n：用户定义的错误信息号从 50001 开场，且必须唯一n：有效级别为1到25，19到25为系统管理员使用的严重级别n：错误信息的文本n：消息的语言。默认为会话的默认语言，在可以以其它语言添加消息之前，消息的美语版us_english必须已经存在nreplace：假设指定REPLACE，那么可改写原定义消息文本和严重级别触发器3132第第5 5章章 存储过程与

26、触发器存储过程与触发器n50001号消息定义添加完成后，可在触发器中使用RAISERROR命令nRAISERRORRAISERROR命令的作用命令的作用n使当触发条件满足时，显示消息或返回消息给客户应用程序nRAISERRORRAISERROR语句的语法格式语句的语法格式nRAISERROR |,，n其中其中n：sysmessages表中用户定义的消息的id号n：一条带格式控制的特殊消息，格式与C语言中的prinf函数的格式相似n：同sp_addmessagen：1到127的整数，表示有关错误调用状态的信息，默认值为1n：替代在中定义的变量或参数。可以有0或不超过20个的替代参数，每个替代参数

27、可以是部分变量 触发器3233第第5 5章章 存储过程与触发器存储过程与触发器n触发器触发器StuInfoChangeRemStuInfoChangeRem的测试过程的测试过程n将“学生根本信息表中“张军同学的“家庭地址改为“丰台区那么激活触发器n执行“RAISERROR 50001, 16, 10语句，显示提醒信息触发器3334第第5 5章章 存储过程与触发器存储过程与触发器ndeleteddeleted和和insertedinserted表表nCREATE TRIGGER 语句中使用的两个特殊表n由SQL Server自动创立和管理的逻辑概念表n这些表临时驻留于内存中，在构造上类似于触发器

28、表，保存着用户操作可能更改的行的旧值或新值n触发器使用deleted和inserted表来检查数据修改的效果以及设置触发器操作的条件触发器3435第第5 5章章 存储过程与触发器存储过程与触发器ndeleted deleted 表表nDeleted表保存了DELETE和UPDATE语句所影响的行的复本n在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到 deleted表中nDeleted表和触发器表通常没有一样的行nInsertedInserted表表nInserted表保存了INSERT和UPDATE语句所影响的行的复本n在一个插入或更新事务处理中，新建行被同时添加到ins

29、erted表和触发器表中n更新事务类似于在删除之后执行插入n首先旧行被复制到 deleted 表中n然后新行被复制到触发器表和inserted表中3536第第5 5章章 存储过程与触发器存储过程与触发器n在触发器中使用在触发器中使用inserted inserted 和和 deleted deleted 表表n例5-19：创立使用inserted表的触发器触发器- 创立触发器，当向“学生根本信息表中插入数据时，将插入的数据按插入时间保存在另一个表中CREATE TRIGGER SaveStuInfo_InsON 学生根本信息FOR INSERTAS INSERT Ins_SaveStuInfo

30、 SELECT * ,getdate FROM inserted3637第第5 5章章 存储过程与触发器存储过程与触发器n测试测试 SaveStuInfo_ InsSaveStuInfo_ Ins触发器触发器n新建一个构造与“学生根本信息表一样的空白表Ins_SaveStuInfon新添加一列为“记录插入时间的datetime类型的字段，用于新记录的插入时间n执行触发器创立语句，创立触发器n执行如下语句，在“学生根本信息表中插入一条新记录insert into 学生根本信息学号,姓名,性别values08888,新1,女n执行如下语句，查看从inserted表中插入到Ins_SaveStuIn

31、fo表中的数据select * from Ins_SaveStuInfo查询结果3738第第5 5章章 存储过程与触发器存储过程与触发器n例5-20：创立使用deleted表的触发器触发器-创立触发器，在删除“学生根本信息表的数据时，将删除的数据保存在另一个表中CREATE TRIGGER SaveStuInfo_DelON 学生根本信息FOR DELETEAS INSERT Del_SaveStuInfo SELECT * FROM deleted3839第第5 5章章 存储过程与触发器存储过程与触发器n测试测试SaveStuInfo_DelSaveStuInfo_Del触发器触发器n在“学

32、生根本信息表中添加一条记录“09999，姗姗，女，汉族，团员n新建一个构造与“学生根本信息表一样的空白表Del_SaveStuInfon执行触发器创立语句，创立触发器n执行如下语句，从“学生根本信息表中删除新添加的记录DELETE FROM 学生根本信息WHERE 学号=09999n执行如下语句，查看从deleted表中插入到Del_SaveStuInfo表中的数据select * from Del_SaveStuInfo查询结果3940第第5 5章章 存储过程与触发器存储过程与触发器n触发器的应用与维护触发器的应用与维护 nAFTERAFTER触发器的应用触发器的应用n触发器可以实现比CHE

33、CK约束更为复杂的约束，可以引用其它表中的列n一个表可以具备多个同类触发器INSERT、UPDATE 或 DELETE，对同一个修改语句可采取多个不同的对策来响应n约束只能提示系统错误信息，而使用触发器可以提供自定义错误信息和处理n假设触发器表有约束，那么在AFTER 触发器执行前检查这些约束。假设约束被破坏，那么不执行 AFTER 触发器 触发器4041第第5 5章章 存储过程与触发器存储过程与触发器n例例5-215-21：利用触发器定义：利用触发器定义“课程根本信息课程根本信息和和“学生成绩学生成绩表表之间的完好性约束。当插入或更新学生成绩时，触发器检查该之间的完好性约束。当插入或更新学生

34、成绩时，触发器检查该课程是否为课程是否为“考察考察课，假设是，那么通过的成绩只能以课，假设是，那么通过的成绩只能以6060分分计，未通过的成绩以计，未通过的成绩以2020分计。分计。触发器USE 学生学籍数据库IF EXISTS SELECT name FROM sysobjects WHERE name = ScoreCheck AND type = TR DROP TRIGGER ScoreCheckGOCREATE TRIGGER ScoreCheck ON 学生成绩 FOR INSERT, UPDATEAS DECLARE 成绩 smallint, 考核方式 varchar8,课程名

35、varchar20 SELECT 成绩 = i.成绩, 考核方式 = c.考核方式, 课程名 = c.课程名 FROM 学生成绩 sc INNER JOIN inserted i ON sc.学号 = i.学号 JOIN 课程根本信息 c ON c. 课程号= i. 课程号4142第第5 5章章 存储过程与触发器存储过程与触发器触发器IF 考核方式= 考察 and 成绩 60 AND 成绩20 BEGIN RAISERROR 课程 %s为考察课，成绩以60或20计！, 16, 1, 课程名 ROLLBACK TRANSACTION ENDn续前页代码4243第第5 5章章 存储过程与触发器存储

36、过程与触发器n触发器触发器ScoreCheckScoreCheck的测试的测试n在“学生成绩表中添加一条记录“01001，06，78n其中课程号为“06的课程为考察课，那么激活触发器的触发条件“FOR INSERT，显示错误信息4344第第5 5章章 存储过程与触发器存储过程与触发器n例例5-225-22：定义触发器，在插入记录或修改记录时，实现：定义触发器，在插入记录或修改记录时，实现“考考勤表勤表与与“学生根本信息学生根本信息表之间的完好性约束。表之间的完好性约束。触发器USE 学生学籍数据库IF EXISTS SELECT name FROM sysobjects WHERE name

37、= InsertAttenCheck AND type = TR DROP TRIGGER InsertAttenCheckGO- 实现“考勤表与“学生根本信息表之间的完好性约束，即检查考勤表中的新插入- 或修改的学号是否合法CREATE TRIGGER InsertAttenCheck ON 考勤表 FOR INSERT, UPDATEAS DECLARE StuNo VARCHAR10,Ins_StuNo VARCHAR10 SELECT StuNo = S.学号 FROM 学生根本信息 S, inserted i WHERE S.学号 = i.学号4445第第5 5章章 存储过程与触发器

38、存储过程与触发器n续前页代码触发器 IF StuNo IS NULL -假设得到的学号为空，表示在考勤表中插入的学号非法，回滚 BEGIN - 获得要插入记录中的学号 SELECT Ins_StuNo = i.学号 FROM inserted i RAISERROR 学号为 %s 的学生不存在！,16,1,Ins_StuNo ROLLBACK TRANSACTION ENDGO4546第第5 5章章 存储过程与触发器存储过程与触发器n触发器触发器InsertAttenCheckInsertAttenCheck的测试的测试n在“考勤表中添加一条记录“09876，04，10n其中学号为“09876

39、的学生不存在，会激活触发器的触发条件“FOR INSERT，从而显示错误信息 4647第第5 5章章 存储过程与触发器存储过程与触发器n例例5-235-23：定义删除型触发器，当对：定义删除型触发器，当对“学生根本信息学生根本信息表进展删表进展删除操作时先检查要删除的行数，删除多行时报错。除操作时先检查要删除的行数，删除多行时报错。触发器USE 学生学籍数据库IF EXISTS SELECT name FROM sysobjects WHERE name = DelStuNumCheck AND type = TR DROP TRIGGER DelStuNumCheckGOCREATE TRI

40、GGER DelStuNumCheck ON 学生根本信息FOR DELETEAS IF ROWCOUNT 1 BEGIN ROLLBACK TRANSACTION RAISERROR 请一次删除一行信息！,16,1 ENDGO4748第第5 5章章 存储过程与触发器存储过程与触发器n触发器触发器DelStuNumCheckDelStuNumCheck的测试的测试n在“学生根本信息表中先添加2条记录“11111，删1“22222，删2触发器删除记录个数大于1，激活触发器，显示错误信息，回滚删除事务，“学生根本信息表中的记录未被删除4849第第5 5章章 存储过程与触发器存储过程与触发器删除记录

41、个数不大于1，不会激活触发器，不显示错误信息，删除事务正常执行， “学生根本信息表中的记录确被删除4950第第5 5章章 存储过程与触发器存储过程与触发器nINSTEAD OFINSTEAD OF触发器的应用触发器的应用n主要作用为可以通过触发器更新那些不能直接更新的视图n如一些涉及多个根本表的视图不能直接进展插入、删除或更新操作，可使用INSTEAD OF触发器来完成触发器5051第第5 5章章 存储过程与触发器存储过程与触发器n例例5-245-24：定义：定义INSTEAD OFINSTEAD OF触发器，用它更新视图中涉及的两个根本表触发器，用它更新视图中涉及的两个根本表n创立两个根本表

42、，分别将学生的个人信息和其准考证信息分开CREATE TABLE 学生信息 学号 char5 PRIMARY KEY,姓名 varchar20,性别 char2,年龄 smallint,家庭地址 varchar100,专业名 char50,系名 char50CREATE TABLE 准考证信息 准考证号 char50 PRIMARY KEY,学号 char5,姓名 varchar20,考区地址 varchar100,CONSTRAINT FK_学生_准考证 FOREIGN KEY学号 REFERENCES 学生信息学号5152第第5 5章章 存储过程与触发器存储过程与触发器n创立基于上述两个根

43、本表的一个视图，包含学生在两个表中的详细信息触发器CREATE VIEW 学生详细信息AS SELECT S. 学号,S.姓名,S.性别,S.年龄,S.家庭地址,S.专业名,S.系名,P.准考 证号,P.考区地址 FROM 学生信息 S,准考证信息 P WHERE S.学号 = P.学号5253第第5 5章章 存储过程与触发器存储过程与触发器n建立两个根本表和一个视图后，假设想通过视图向根本表中插入数据，那么因视图涉及多个表，SQL Server不允许插入不允许在视图中执行插入操作5354第第5 5章章 存储过程与触发器存储过程与触发器n创立视图上的INSTEAD OF触发器，以便通过它在一个

44、视图涉及的两个根本表中插入记录触发器USE 学生学籍数据库-在视图上创立触发器，使可以通过试图将记录插入到其涉及的多个根本表中CREATE TRIGGER InsStuDetailInfo ON 学生详细信息INSTEAD OF INSERTAS INSERT INTO 学生信息 - 将记录插入“学生信息根本表中 SELECT 学号,姓名,性别,年龄,家庭地址,专业名,系名 FROM inserted INSERT INTO 准考证信息 - 将记录插入“准考证信息根本表中 SELECT 准考证号,学号,姓名,考区地址 FROM insertedGO5455第第5 5章章 存储过程与触发器存储过

45、程与触发器n在视图“学生详细信息上创立触发器后，再执行插入语句，结果为成功将该记录分别插入到根本表“学生信息和“准考证信息中，完成通过涉及多表的视图对这多个表进展插入的操作插入操作成功执行5556第第5 5章章 存储过程与触发器存储过程与触发器nAFTERAFTER触发器和触发器和INSTEAD OFINSTEAD OF触发器的比较触发器的比较nAFTER AFTER 触发器与触发器与INSTEAD OFINSTEAD OF触发器在适用范围上不同触发器在适用范围上不同nAFTER 触发器只能建立在表上n而INSTEAD OF触发器可建立在表或视图上nAFTER AFTER 触发器与触发器与IN

46、STEAD OFINSTEAD OF触发器在允许的触发器个数上不同触发器在允许的触发器个数上不同n在每个表的每个触发操作上都可建立多个AFTER 触发器n而在表或视图上的每个触发操作只能建立一个INSTEAD OF触发器。nAFTER AFTER 触发器与触发器与INSTEAD OFINSTEAD OF触发器在执行时间上不同触发器在执行时间上不同nAFTER 触发器在所有约束处理、级联引用操作和inserted和deleted表创立之后才产生触发动作n而INSTEAD OF触发器在所有约束处理之前、inserted和deleted表创立之后产生触发动作nAFTER AFTER 触发器与触发器与

47、INSTEAD OFINSTEAD OF触发器在产生的触发动作上不同触发器在产生的触发动作上不同nAFTER 触发器在激活时进展规那么检查，决定是否允许该操作的实现n而INSTEAD OF触发器在激活时是将其中定义的触发动作替代对触发器表或视图进展的UPDATE、DELETE和INSERT操作 触发器5657第第5 5章章 存储过程与触发器存储过程与触发器n修改触发器修改触发器n使用企业管理器修改触发器使用企业管理器修改触发器n使用使用ALTER TRIGGERALTER TRIGGER修改触发器修改触发器nALTER TRIGGER ON | FOR|AFTER|INSTEAD OFDELE

48、TE,INSERT,UPDATEnASn n触发器5758第第5 5章章 存储过程与触发器存储过程与触发器n例例5-275-27：修改：修改StuInfoChangeRemStuInfoChangeRem触发器触发器触发器-使用 ALTER TRIGGER 语句修改触发器，使之仅对INSERT操作有效USE 学生学籍数据库GOALTER TRIGGER StuInfoChangeRem ON 学生根本信息FOR INSERTAS RAISERROR 50010, 16, 10 - 定义消息50010的语句为：sp_addmessage 50010,16,注意:正试图将记录插入学生根本信息表！,

49、us_english5859第第5 5章章 存储过程与触发器存储过程与触发器n删除触发器删除触发器n使用企业管理器删除触发器使用企业管理器删除触发器n使用使用DROP TRIGGERDROP TRIGGER删除触发器删除触发器nDROP TRIGGER 触发器名n从当前数据库中删除一个或多个触发器 n删除触发器时，其所基于的表和数据不受影响n删除表时，所有与表关联的触发器也被删除n删除触发器时，sysobjects 和 syscomments 系统表中的触发器信息也被删除n默认情况下，DROP TRIGGER 权限为触发器表所有者，且不可转让触发器5960第第5 5章章 存储过程与触发器存储过

50、程与触发器n实例实例- -使用了存储过程和触发器的学生学籍数据库使用了存储过程和触发器的学生学籍数据库 n目的目的n学生学籍数据库包含五个根本表和一个视图，在其上参加的存储过程和触发器，实现数据的操作和完好性控制。6061第第5 5章章 存储过程与触发器存储过程与触发器学生基本信息表存储过程创立表查询学生人数例5.1按性别查询学生姓名例5.2求不同性别学生人数例5.3按学号查询学生姓名例5.4按学生姓名查询学生家庭地址例5.5按姓名模糊查询学生根本情况例5.6查询学生出生日期例5.7触发器修改、删除信息时，提醒例5.17老师基本信息表存储过程创立表触发器检查老师编号1检查职称2学生学籍数据库的

51、存储过程和触发器的设计构造学生学籍数据库的存储过程和触发器的设计构造 6162第第5 5章章 存储过程与触发器存储过程与触发器课程根本信息表存储过程创立表根据学分、考核方式查询课程根本信息例5.10检索所有学分为3、4的课程例5.16触发器 检查老师编号3 检查课程号考勤表存储过程创立表触发器检查学号应来自“学生根本信息表例5.21检查课程号学生成绩表存储过程创立表触发器检查“考察课成绩应以60或20分计例5.20检查学号和课程号学籍统计信息视图存储过程创立视图触发器检查学号续前表续前表6263第第5 5章章 存储过程与触发器存储过程与触发器USE 学生学籍数据库IF EXISTS SELECT name FROM sysobjects WHERE name = TeaNoCheck AND type = TR DROP TRIGGER TeaNoCheckGO- 在插入和修改记录时，检查老师编号是否为3位数字字符CREATE TRIGGER TeaNoCheck ON 老师