存储过程和触发器

存储过程和触发器第一页，共四十八页，编辑于2023年，星期五10.1存储过程10.1.1存储过程概述存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。一般用来处理需要与数据库进行频繁交互的复杂的业务。因为存储过程具有以下优点：①存储过程在服务器端运行，执行速度快。②封装业务逻辑也可以使数据库操作人员与应用系统开发人员的分工更明确，支持模块化设计。2023/5/272第二页，共四十八页，编辑于2023年，星期五③存储过程执行一次后，其执行计划就驻留在高速缓冲存储器，如果需要再次调用，只需要从高速缓冲存储器中调用，提高系统性能。④存储过程可以作为安全机制。通过授予用户对存储过程的操作权限来实现安全机制。⑤使体现企业规则的运算程序放入数据库服务器中，以便集中控制。2023/5/273第三页，共四十八页，编辑于2023年，星期五10.1.2存储过程的分类1．系统存储过程系统存储过程存储在master数据库中，以sp_开头，可以在其他数据库中进行调用。如：sp_help就是取得指定对象的相关信息。2．扩展存储过程在SQLServer环境之外执行动态链接库称为扩展存储过程，以XP_开头,用来调用操作系统提供的功能。3．用户自定义的存储过程由用户创建并能完成某一特定功能的存储过程。包括Transact-SQL和CLR两种类型。（1）Transact-SQL存储过程是指保存的Transact-SQL语句集合，可以接受和返回用户提供参数。也可以从数据库向客户端应用程序返回数据。（2）CLR在本书不做详细介绍。2023/5/274第四页，共四十八页，编辑于2023年，星期五10.1.3建立存储过程1.使用对象资源管理器建立存储过程2.使用T-SQL命令建立存储过程语法形式如下：CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]ASsql_statement[...n]

2023/5/275第五页，共四十八页，编辑于2023年，星期五【例题10.1】创建一个不带参数的存储过程，名称为pro_stu，用于检索学生的姓名、性别和家庭住址。USEscoreGO--判断pro_stu存储过程是否存在，若存在，则删除Ifexists(selectnamefromsysobjectswherename='pro_stu'andtype='p')DROPPROCEDUREpro_stuGo--建立存储过程CREATEPROCpro_stuAsSELECTstudent_name,sex,addressFROMstudent2023/5/276第六页，共四十八页，编辑于2023年，星期五【例题10.2】创建一个带参数的存储过程，名称为pro_class，在执行存储过程的时候输入班级编号，然后检索该班级的学生的详细信息。USEscoreGO--判断pro_class存储过程是否存在，若存在，则删除Ifexists(selectnamefromsysobjectswherename='pro_class'andtype='p')DROPPROCEDUREpro_classGo--建立存储过程CREATEPROCpro_class@c_nochar(8)AsSELECT*FROMstudentWHEREclass_id=@c_no2023/5/277第七页，共四十八页，编辑于2023年，星期五10.1.4存储过程的执行语法形式如下：[[EXEC[UTE]]{[@return_status=]{procedure_name|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}

[,...n][WITHRECOMPILE]2023/5/278第八页，共四十八页，编辑于2023年，星期五1.无参存储过程的执行【例题10.3】执行在例题10.1中创建的存储过程pro_stu。

EXECpro_stu在执行带参存储过程的时候参数有以下几种传递方式：2.带参存储过程的执行（1）顺序法2023/5/279第九页，共四十八页，编辑于2023年，星期五CREATEPROCpro_update@s_idchar(10),@c_idchar(5),@resultint--参数ASUPDATEscoreSETgrade=@resultWHEREstudent_id=@s_idANDcourse_id=@c_id/*顺序法执行存储过程，把0801101号学生1001号课程的成绩修改为85分*/EXECpro_update'0801101','1001',85【例题10.4】建立一个存储过程pro_update，通过指定学号和课程编号来把成绩表中的成绩修改为指定的某个值，然后执行该存储过程。2023/5/2710第十页，共四十八页，编辑于2023年，星期五EXECpro_update@s_id=’0801101’,@result=85,@c_id=’1001’说明：提示法中的参数顺序可以与建立存储过程时的参数顺序不一致，而顺序法必须保持一致。（2）提示法【例题10.5】通过提示法来执行存储过程pro_update。2023/5/2711第十一页，共四十八页，编辑于2023年，星期五建立存储过程时，可以指定参数的默认值，默认值必须为常量或者为NULL。（3）使用默认参数【例题10.6】建立一个存储过程pro_insert，用来完成向课程表中插入记录的功能，然后执行该存储过程并检查执行结果。CREATEPROCpro_insert@c_idchar(5),@c_namechar(20),@periodint=64,@c_typechar(10)='NULL'ASINSERTcourseVALUES(@c_id,@c_name,@period,@c_type)GO--执行存储过程pro_insertEXECpro_insert'1006','c++程序设计'GO--查看插入结果SELECT*FROMcourse2023/5/2712第十二页，共四十八页，编辑于2023年，星期五在建立存储过程时，通过定义输出参数，可以从存储过程中返回一个或多个值。在执行存储过程时，可以将结果返回给返回参数。定义输出参数需要在参数定义的数据类型后使用关键字OUTPUT，或省写为OUT。（4）使用带OUTPUT的返回参数【例题10.7】建立一个带参数的存储过程，用于求两个整数的和，其中和定义为OUTPUT参数，然后执行存储过程并输出和。CREATEPROCpro_sum@aint,@bint,@sumintoutputASSET@sum=@a+@bGO--执行存储过程DECLARE@sintEXECpro_sum20,15,@soutputPRINT@s2023/5/2713第十三页，共四十八页，编辑于2023年，星期五10.1.5存储过程的返回值存储过程被执行以后可以返回一个整数类型的代码值，该值用来记录存储过程的执行情况。若返回值为0，表示执行成功；否则表示执行不成功。

1）用一个变量来捕捉存储过程执行后返回值。

2）使用RETURN语句为存储过程指定一个确定的返回值，以用来反映存储过程执行的结果情况。捕捉存储过程返回值的方法2023/5/2714第十四页，共四十八页，编辑于2023年，星期五【例题10.9】用变量来捕捉存储过程执行后返回值。建立一个存储过程，完成查询指定学生指定科目的成绩。然后执行该存储过程并捕捉执行是否成功。CREATEPROCpro_grade@s_idchar(10),@c_idchar(5)ASSELECTgradeFROMscoreWHEREstudent_id=@s_idANDcourse_id=@c_idGODECLARE@statusint--声明变量,用来存放执行结果的状态EXEC@status=pro_grade'0801101','1001'SELECT@status--输出状态值2023/5/2715第十五页，共四十八页，编辑于2023年，星期五【例题10.10】使用RETURN语句为存储过程指定一个确定的返回值。建立一个存储过程，完成查询指定学生指定科目的成绩，如果成绩大于60分，返回1；如果等于60分，返回0；小于60分，则返回-1。然后执行该存储过程并输出返回值。CREATEPROCpro_return@s_idchar(10),@c_idchar(5)ASDECLARE@resultintSELECT@result=gradeFROMscoreWHEREstudent_id=@s_idANDcourse_id=@c_idIF@result>60RETURN1IF@result=60RETURN0IF@result<60RETURN-1GODECLARE@statusint--声明变量,用来存放执行结果的状态EXEC@status=pro_return'0801101','1001'SELECT'状态'=@status--输出状态值2023/5/2716第十六页，共四十八页，编辑于2023年，星期五10.1.6查看、修改和删除存储过程1.修改存储过程语法形式如下：ALTERPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]ASsql_statement[...n]

2023/5/2717第十七页，共四十八页，编辑于2023年，星期五【例题10.11】修改存储过程pro_sum，使其功能更改为输出两个数相加的和，不使用返回参数。USEscoreGOALTERPROCpro_sum@aint,@bintASDECLARE@sumintSET@sum=@a+@bPRINT@sum2023/5/2718第十八页，共四十八页，编辑于2023年，星期五2.查看存储过程（1）使用对象资源管理器查看（2）使用T-SQL命令查看1）查看存储过程的参数及其数据类型格式：sp_helpname2）查看存储过程的源代码格式：sp_helptextname3）查看和存储过程相关的数据库对象格式：sp_dependsname2023/5/2719第十九页，共四十八页，编辑于2023年，星期五【例题10.12】查看存储过程pro_return的定义文本。

EXECsp_helptextpro_return例题10.13】查看存储过程pro_return的所有者、类型及参数。

EXECsp_helppro_return【例题10.14】查看存储过程pro_return的相关性。

EXECsp_dependspro_return2023/5/2720第二十页，共四十八页，编辑于2023年，星期五3.删除存储过程（1）使用对象资源管理器删除存储过程（2）使用T-SQL语句删除存储过程语法格式：DROPPROC[EDURE]{procedure_name}[,…n]【例题10.15】删除存储过程pro_return。

DROPPROCpro_return2023/5/2721第二十一页，共四十八页，编辑于2023年，星期五10.2触发器10.2.1触发器的概述MicrosoftSQLServer2005提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。具体表现为：①触发器可以通过数据库中的相关表进行级联更改。②触发器可以防止恶意或错误的UPDATE、INSERT、DELETE、CREATE和ALTER操作，这些限制比用CHECK约束所定义的更复杂。③触发器可以评估数据修改前后表的状态，并根据该差异采取措施。④一个表可以有多个同类触发器允许采取多个不同的操作来响应同一个修改语句。2023/5/2722第二十二页，共四十八页，编辑于2023年，星期五10.2.2触发器的类型1.DML触发器DML触发器是当数据库服务器中发生数据操作语言事件时所执行的操作

.DML触发器有三种类型：AFTER触发器、INSTEADOF触发器和CLR触发器。

两大类触发器：DML触发器和DDL触发器。

2023/5/2723第二十三页，共四十八页，编辑于2023年，星期五指定用触发器中的操作代替触发语句的操作，也就是该触发器并不执行所定义的操作（INSERT、DELETE、UPDATE），而是执行触发器本身的SQL语句。可以为基于一个或多个表的视图定义INSTEADOF触发器，而这些触发器可能够扩展视图可支持的数据类型。（2）INSTEADOF触发器表示只有在执行了指定的操作（INSERT、DELETE、UPDATE）之后触发器才被激活，执行触发器中的SQL语句。若只指定FOR，则默认为AFTER触发器，且该类型触发器仅能在表上创建。（1）AFTER触发器2023/5/2724第二十四页，共四十八页，编辑于2023年，星期五DDL触发器是一种特殊的触发器，当服务器或数据库中发生数据定义语言（DDL，CREATE、DROP、ALTER等）事件时将激活这些触发器。他们可以用于在数据库中执行管理任务，譬如，审核以及规范数据库操作。DDL触发器无法作为INSTEADOF触发器使用。2.DDL触发器如果要执行以下操作，可以考虑使用DDL触发器。①防止他人对数据库架构进行修改。②希望数据库发生某种情况以响应数据库架构中的更改。③要记录数据库架构中的更改或事件。2023/5/2725第二十五页，共四十八页，编辑于2023年，星期五(1)使用对象资源管理器建立DML触发器10.2.3DML触发器1.建立DML触发器(2)使用T-SQL语句建立DML触发器语法格式：CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}AS

sql_statement[...n]

}

}2023/5/2726第二十六页，共四十八页，编辑于2023年，星期五【例题10.16】在student表中创建了一个触发器，当往该表中成功插入记录时，输出提示信息。然后执行插入记录操作，检验触发器。--判断insert_stu触发器是否存在，若存在，则删除IFEXISTS(SELECTnameFROMsysobjectsWHEREname='insert_stu'ANDtype='TR')DROPTRIGGERinsert_stuGO--建立触发器CREATETRIGGERinsert_stuONstudentFORINSERTASPRINT'插入记录成功'--插入记录，激活触发器INSERTstudent(student_id,student_name,sex,class_id)VALUES('0801109','吴盼盼','男','08011')2023/5/2727第二十七页，共四十八页，编辑于2023年，星期五（1）inserted表和deleted表2．DML触发器的应用在执行触发器时，SQLServer会为触发器建立两个临时表：deleted表和inserted，它们的结构和触发器所在的表的结构相同Inserted表存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。

Deleted表存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，记录从触发器表中删除，并传输到deleted表中。Deleted表和触发器表通常没有相同的行。2023/5/2728第二十八页，共四十八页，编辑于2023年，星期五注意一：执行UPDATE语句时，会被视为插入或删除事务，旧的行值会保留一份副本在deleted表中，而新的行值的副本则保留在触发器表与inserted表。注意二：inserted和deleted表中的值只限于在触发器中使用。一旦触发器完成就无法再使用。2023/5/2729第二十九页，共四十八页，编辑于2023年，星期五【例题10.17】建立一个触发器，在修改数据时激活该触发器。然后执行修改操作，查看inserted和deleted表中的数据。--建立触发器CREATETRIGGERupdate_stuONstudentFORinsert,update,deleteASSELECT*FROMinsertedSELECT*FROMdeleted--修改数据，激活触发器UPDATEstudentSETsex='男'WHEREstudent_id='0801101'2023/5/2730第三十页，共四十八页，编辑于2023年，星期五

当往表中插入记录时，INSERT触发器被执行。一般情况下，INSERT触发器被用来更新时间标记字段，或者验证被触发器监控的字段中的数据是否满足要求，以确保数据完整性。（2）INSERT触发器2023/5/2731第三十一页，共四十八页，编辑于2023年，星期五【例题10.18】建立一个触发器，当向成绩表中插入一条记录时，检查被插入的成绩是否大于等于0和小于等于100。若不满足该条件，不允许插入记录。CREATETRIGGERcheck_insertONscoreAFTERinsertASDECLARE@成绩intSELECT@成绩=gradeFROMinsertedIF@成绩>=0AND@成绩<=100 PRINT'插入成功'ELSE BEGIN PRINT'成绩值超出范围,不允许插入' ROLLBACKTRANSACTION END--插入记录，检验insert触发器的作用INSERTgrade(student_id,course_id,grade)VALUES('0801107','1001',-10)2023/5/2732第三十二页，共四十八页，编辑于2023年，星期五(3)UPDATE触发器修改触发器和插入触发器的工作过程基本上一致，修改一条记录等于插入了一条新的记录并且删除一条旧的记录。2023/5/2733第三十三页，共四十八页，编辑于2023年，星期五【例题10.19】假设成绩信息被成功录入到表中以后将允许修改，建立一个触发器，禁止其修改成绩。CREATETRIGGERcheck_updateONscoreAFTERupdateASIFupdate(grade)BEGINPRINT'修改无效,成绩被录入后,不允许修改'ROLLBACKTRANSACTIONEND--修改成绩值，检查

update触发器的作用UPDATEscoresetgrade=80WHEREstudent_id='0801101'ANDcourse_id='1001'2023/5/2734第三十四页，共四十八页，编辑于2023年，星期五(4)DELETE触发器

DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。2023/5/2735第三十五页，共四十八页，编辑于2023年，星期五【例题10.20】在学生表中建立一个触发器，若删除学生记录，则把该学生在成绩表中的成绩记录一并删除。CREATETRIGGERcheck_deleteONstudentAFTERdeleteASDELETEscoreWHEREstudent_idin(SELECTstudent_idFROMdeleted)--删除学生记录，检查

delete触发器的作用DELETEstudentWHEREstudent_id='0801103'--查看成绩表

grade中‘0801103’号学生的成绩是否被删除SELECT*FROMgrade2023/5/2736第三十六页，共四十八页，编辑于2023年，星期五10.2.4DDL触发器1、建立DDL触发器

CREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE} [WITHENCRYPTION] {FOR|AFTER} {event_type|event_group}[,…n] AS

{sql_statement[...n]}

2023/5/2737第三十七页，共四十八页，编辑于2023年，星期五参数说明：②ALLSERVER指明触发器的作用域为当前服务器。如果指定了此参数，则只要当前服务器中的任何位置出现event_type或event_group，就会激活触发器。③DATABASE指明触发器的作用域为当前数据库。如果指定了此参数，则只要当前数据库中的任何位置出现event_type或event_group，就会激活触发器。⑤event_type将导致激活DDL触发器的T-SQL语言的名称。event_type选项有：CREATE_TABLE、CREATE_DATABASE、ALTER_TABLE等等。⑥event_group预定义的T-SQL语言事件分组的名称。执行任何属于event_group的T-SQL语言事件，都将激活DDL触发器。event_group有：DDL_SERVER_SECURITY_EVENTS代表所有以服务器为目标的各类DDL语法语法事件，而DDL_TABLE_VIEW_EVENTS代表了针对数据表、视图表、索引与统计的DDL事件。2023/5/2738第三十八页，共四十八页，编辑于2023年，星期五【例题10.21】使用DDL触发器来防止数据库score中的表被任意修改或删除。USEscoreGOCREATETRIGGERsafetyONDATABASEFORDROP_TABLE,ALTER_TABLEASBEGIN PRINT'禁止修改或删除表' ROLLBACKEND--修改表，检验safety触发器的功能ALTERTABLEstudentADDf_namechar(8)2023/5/2739第三十九页，共四十八页，编辑于2023年，星期五【例题10.22】建立

DDL触发器来防止在当前服务器下建立数据库。CREATETRIGGERforbid_create ONALLSERVER FORCREATE_DATABASE AS BEGIN PRINT'禁止建立数据库' ROLLBACK END --建立数据库，检验forbid_create触发器的功能

CREATEDATABASEbook2023/5/2740第四十页，共四十八页，编辑于2023年，星期五10.2.5查看、修改和删除触发器1.查看触发器（１）使用对象资源管理器查看触发器信息（２）使用系统存储过程查看触发器2023/5/2741第四十一页，共四十八页，编辑于2023年，星期五（1）sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。格式：sp_help‘触发器名称‘

（2）sp_helptext：用于查看触发器的正文信息格式：sp_helptext‘触发器名称’

（3）sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。

sp_depends’触发器名称’