存储过程和触发器

存储过程和触发器第1页，共67页，2023年，2月20日，星期四任务掌握存储过程，触发器的概念和使用方法；掌握运用T-SQL编写基本的存储过程、触发器。（☆

＊）理解存储过程、触发器的用途；进一步理解数据完整性的含义（☆）了解数据完整性的规则、默认值等使用。第2页，共67页，2023年，2月20日，星期四5.1存储过程5.1.1存储过程的基本概念5.1.2存储过程的优点5.1.3存储过程的分类5.1.4存储过程的使用方法第3页，共67页，2023年，2月20日，星期四5.1存储过程传统的数据库结构管理数据等共享资源，所有的应用程序都在用户端，都与用户实际运行的应用程序捆绑在一起；客户/服务器数据库管理数据等共享资源承担一些应用逻辑，完成来自客户端的一些处理请求，在数据库中还可以存放程序，即存储过程。第4页，共67页，2023年，2月20日，星期四5.1.1存储过程的基本概念是什么：是事先编好的、存储在服务器端的数据库中的程序（预编译的SQL集合），这些程序用来完成对数据库的指定操作。怎么使用：这些程序可以由应用程序的调用启动，或由数据完整性规则和触发器调用。怎么理解：存储过程是用户可以简单地将其作为一个函数来调用，无须重复执行存储过程的SQL语句。它包含一组经常执行的、逻辑完整的SQL语句。第5页，共67页，2023年，2月20日，星期四5.1.2存储过程的优点减轻程序编写的工作量：可以在各个程序中反复调用定义好的存储过程。存储过程能够实现较快的执行速度：因为存储过程是预编译的，而批处理的T-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。

存储过程能够减少网络流量：对于同一个针对数据库对象的操作，所涉及到的T-SQL语句被组织成一存储过程，当在客户端调用该存储过程时，网络中传送的只是该调用语句，降低网络负载。存储过程增加安全机制：系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。第6页，共67页，2023年，2月20日，星期四客户端应用（不使用存储过程）客户端应用（使用存储过程）.Starttransaction.INSERTdataUPDATEdataDELETEdataEndtransaction..Starttransaction.CallStoredprocedureEndtransaction.DBMSServerDBMSServerProcedure:INSERTdataUPDATEdataDELETEdata(a)(b)

不使用存储过程时，所有的数据处理都在客户端完成；而使用存储过程时，可以使数据处理在服务器端完成。第7页，共67页，2023年，2月20日，星期四5.1.3

储存过程的分类系统存储过程SQLServer本身提供了一些存储过程，用于管理SQLServer和显示有关数据库和用户的信息，我们称之为系统存储过程。系统存储过程都以“sp_”开头，存储在master数据库中。用户存储过程用户也可以编写自己的存储过程，并把它存放在数据库中。这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。第8页，共67页，2023年，2月20日，星期四5.1.4存储过程的使用方法创建存储过程执行存储过程修改存储过程删除存储过程第9页，共67页，2023年，2月20日，星期四创建存储过程CREATEPROC[edure]procedure_name[;number][@parameterdata_type[=default],…

]ASsql_statement

s

procedure_name：给出存储过程名；s

number：对同名的存储过程指定一个序号；s

@parameter：给出参数名；s

data_type：指出参数的数据类型；s

=default：给出参数的默认值；s

sql_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。第10页，共67页，2023年，2月20日，星期四例：创建一个最简单的存储过程（无参数调用）：CREATE

PROCedure

sp_getemp;

1ASSELECT*FROM职工说明：创建存储过程sp_getemp;1，要求查询职工信息第11页，共67页，2023年，2月20日，星期四例：带参数的存储过程：CREATE

PROCedure

sp_getemp;

2(@salaryint)ASSELECT*FROM职工WHERE工资>@salary说明：创建存储过程sp_getemp;2，要求查询工资值大于给定值的职工信息第12页，共67页，2023年，2月20日，星期四说明：存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，即在存储过程中一般不能含有以下语句：CREATETABLECREATEVIEWCREATEDEFAULTCREATERULECREATETRIGGERCREATEPROCEDURE第13页，共67页，2023年，2月20日，星期四执行存储过程[EXECute][@<返回状态码>=]<存储过程名>[[@<参数>=]{<值>|@<变量>}…]第14页，共67页，2023年，2月20日，星期四例：执行带参数的sp_getemp;2存储过程Execute

sp_getemp;21240

说明：执行存储过程sp_getemp;2，要求查询工资值大于1240元的职工信息第15页，共67页，2023年，2月20日，星期四存储过程的返回值和状态信息无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。我们在创建存储过程时，也可以定义自己的状态码和错误信息。第16页，共67页，2023年，2月20日，星期四常用的存储过程返回状态表0过程成功执行－1对象丢失－2发生数据类型错误－3处理过程被死锁－4发生权限错误－5发生语法错误－6发生恶意用户错误－7发生资源错误－8遭遇非致命的内部错误－9遭遇系统限制－10发生致命的内部不稳定性－12表或索引被破坏－13数据库被破坏－14发生硬盘错误通常用全局变量@@ERROR返回最后执行的SQL语句的错误代码。第17页，共67页，2023年，2月20日，星期四CREATEPROCeduresp_getemp;3(@salaryint=NULL)ASIF@salaryISNULLBEGINPRINT'必须提供一个数值作参数！'RETURN13ENDIFNOTEXISTS(SELECT*FROM职工WHERE工资>@salary)BEGINPRINT'没有满足条件的记录！'RETURN-103ENDSELECT*FROM职工WHERE工资>@salaryRETURN0例：带参数和返回状态值的存储过程。第18页，共67页，2023年，2月20日，星期四利用全局变量修改刚才的例子CREATEPROCeduresp_getemp;3(@salaryint=NULL)ASSELECT*FROM职工WHERE工资>@salaryIF@@ERROR=0RETURN0ELSERETURN-1第19页，共67页，2023年，2月20日，星期四例：执行以上存储过程。DECLARE@statusintEXECUTE@status=sp_getemp;3

@salary

＝1200print@status第20页，共67页，2023年，2月20日，星期四存储过程的修改和删除

修改存储过程的语句是（一般格式）：ALTERPROC[edure]procedure_name[;number][@parameterdata_type[=default],…

]ASsql_statement

删除存储过程的语句是：

DROPPROC[edure]procedure_name注意：删除存储过程的语句中不能指定序号。也就是说，该语句将同时删除同名的所有存储过程。第21页，共67页，2023年，2月20日，星期四小结：要用好存储过程存储过程是客户/服务器机制的一个重要组成部分，如果使用客户/服务器机制的数据库管理系统，但是不理解存储过程或没有充分利用存储过程，那将使客户/服务器机制的功能大打折扣，使系统的整体性能可能降低很多。第22页，共67页，2023年，2月20日，星期四5.2触发器5.2.1

触发器的基本概念5.2.2触发器的用途5.2.3触发器与存储过程的比较5.2.4触发器的使用方法第23页，共67页，2023年，2月20日，星期四5.2.1

触发器的基本概念触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。因此，相应的，触发器根据触发类型分为insert，delete，update触发器。第24页，共67页，2023年，2月20日，星期四5.2.2触发器的用途触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有以下的功能：触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。触发器可以禁止或撤消违反参照完整性的修改。触发器可以强制比用CHECK约束定义更加复杂的限制。第25页，共67页，2023年，2月20日，星期四5.2.3触发器与存储过程的比较联系：1、触发器也是存储过程。2、它们都是提高数据库服务器性能的工具。区别：1、执行方法不同。触发器主要是通过事件进行触发而被执行的，存储过程可以通过存储过程名字而被直接调用。2、建立方法不同。第26页，共67页，2023年，2月20日，星期四触发器是依附于表的数据库对象CREATETRIGGER

语句必须是批处理的第一个语句表的所有者具有创建触发器的缺省权限，表的所有者不能把该权限传给其它用户。触发器是数据库对象，所以其命名必须符合命名规则。尽管在触发器的SQL语句中可以参照其它数据库中的对象，但是触发器只能创建在当前数据库中。虽然触发器可以参照视图或临时表，但不能在视图或临时表上创建触发器，而只能在基表或在创建视图的表上创建触发器。一个触发器只能对应一个表，这是由触发器的机制决定的。第27页，共67页，2023年，2月20日，星期四触发器是依附于表的数据库对象一个触发器和三部分内容有关：定义触发器的表激活触发器的数据操作语句触发器要采取的动作第28页，共67页，2023年，2月20日，星期四不能在触发器中使用的sql语句Createdatabase和createtable所有drop语句数据库修改语句altertable,alterdatabase对象权限语句grant和revoke…第29页，共67页，2023年，2月20日，星期四5.2.4

触发器的使用方法创建触发器的语句触发器的使用原理-理解两个视图并掌握它们的使用插入类触发器删除类触发器更新类触发器触发器的相关操作修改删除触发器第30页，共67页，2023年，2月20日，星期四建立触发器的语句CREATETRIGGERtrigger_nameONtableFOR{INSERT|UPDATE|DELETE}AS[IFUPDATE(column)[{AND|OR}UPDATE(column)…]]sql_statements

trigger_name：给出了触发器的名称；s

table：说明了定义触发器的表或视图；s

FOR{INSERT|UPDATE|DELETE}：说明了激活触发器的数据操作语句；s

IFUPDATE(column)：对应于UPDATE类触发器，说明如果更新某（些）列则做如何处理；s

sql_statement：触发器所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。第31页，共67页，2023年，2月20日，星期四例：建立一个简单的触发器。CREATETRIGGERwh_triggerON仓库FORINSERTASPRINT'插入了一个仓库元组'说明：当在“仓库”表中插入一行元组，则输出“插入了一个仓库元组”。第32页，共67页，2023年，2月20日，星期四触发器的原理

—两个特殊的视图每个触发器有两个特殊的视图：插入视图（inserted）和删除视图（deleted）。它们是逻辑表且是由系统管理的，存储在内存中，不允许用户直接对其修改，结构与原表有相同的表结构。当触发器工作完成，这两个视图也被删除。它们主要保存因用户操作（存放刚插入的新记录和存放刚删除的旧记录）而被影响到的原数据值或新数据值。它们是只读的，即用户不能向这两个表写入内容,但可以引用表中的数据。第33页，共67页，2023年，2月20日，星期四触发器的原理

—这两个视图与数据操作的关系一旦对表执行了插入操作，插入视图就是用来存储向原表插入的内容。一旦对表执行了删除操作，则将所有的删除行存放至删除视图中。更新操作包括两个部分即先将更新的内容去掉然后将新值插入，因此对一个定义了更新类型触发器的表来讲，在删除视图中存放了旧值，然后在插入视图中存放新值。第34页，共67页，2023年，2月20日，星期四插入类触发器插入类触发器就是当表上发生插入操作时所触发执行的程序。第35页，共67页，2023年，2月20日，星期四例：对职工表的插入操作定义一个触发器，使得当插入职工记录时，检查相应的仓库元组是否存在，如果不存在则撤消所做的插入操作。CREATETRIGGERe_ins_triggerON

职工FORINSERTAS如果插入的职工元组的仓库号在仓库表中存在,那么插入成功，职工表增加一行元组。如果插入的职工元组的仓库号在仓库表中不存在,则插入操作不成功，给出相应的提示，并且事务回滚到插入操作之前。IF(SELECTCOUNT(*)FROM仓库w,insertediWHEREw.仓库号=i.仓库号)=0BEGINRAISERROR('非法仓库号！',1,1)ROLLBACKTRANSACTIONEND第36页，共67页，2023年，2月20日，星期四思考：如果上题要求改为在职工表的插入操作定义一个触发器，使得当插入职工记录时，检查实体完整性，如果不满足实体完整性则撤消所做的插入操作。CREATETRIGGERe_ins_trigger2ON

职工FORINSERTAS如果插入的职工元组的职工号在原职工表中不存在,那么插入成功，职工表增加一行元组。如果插入的职工元组的职工号在原职工表中已存在,则插入操作不成功，给出相应的提示，并且事务回滚到插入操作之前。IF(SELECTCOUNT(*)FROM职工w,insertediWHEREw.职工号=i.职工号)>0BEGINRAISERROR(‘重复的职工号！',1,1)ROLLBACKTRANSACTIONEND第37页，共67页，2023年，2月20日，星期四删除类触发器删除类触发器就是当表上发生删除操作时所触发执行的程序。第38页，共67页，2023年，2月20日，星期四例：定义一个触发器，使得当删除仓库记录时，同时将所属所有职工记录的仓库号字段值置为空值NULL：

CREATETRIGGERw_del_triggerON仓库

FORDELETEASUPDATE职工SET仓库号=NULLWHERE仓库号=(SELECT仓库号FROMdeleted)第39页，共67页，2023年，2月20日，星期四思考：若改为定义一个触发器，使得当删除仓库记录时，同时将所属所有职工记录删除：

CREATETRIGGERw_del_trigger2ON仓库

FORDELETEASDELETE

FROM职工FROMdeletedWHERE职工.仓库号=deleted.仓库号第40页，共67页，2023年，2月20日，星期四更新类触发器更新类触发器就是当表上发生更新操作时所触发执行的程序。第41页，共67页，2023年，2月20日，星期四例：对职工表的更新操作定义一个触发器，使得当职工变换所属仓库时，检查相应的仓库元组是否存在，如果不存在则撤消所做的更新操作，如果新的仓库号是WH2则将工资提高10%。定义语句（注意表名，哪一类的触发器）当改变职工所属的仓库号时（仓库号a->仓库号b，不确定，怎么写语句？）如果仓库号b在仓库表中不存在，事务回滚，撤销更新职工表的仓库号字段，维持原来的元组。如果仓库号b在仓库表中存在，则进行下面的工作如果更新的仓库号b为WH2，则给这个职工增加10%工资如果更新的仓库号b不是WH2，而是其它合法的仓库号，则只是修改该职工所属的仓库号（即用户的更新操作成功）WH31331WH5第42页，共67页，2023年，2月20日，星期四CREATETRIGGERe_upd_triggerON职工FORUPDATEASDECLARE@wh_noCHAR(4)IFUPDATE(仓库号)BEGINIF(SELECTCOUNT(*)FROM仓库w,insertediWHEREw.仓库号=i.仓库号)=0BEGINRAISERROR('非法仓库号！',16,1)ROLLBACKTRANSACTIONENDELSEBEGINSELECT@wh_no=仓库号FROMinsertedIF@wh_no='WH2'UPDATE职工SET工资=工资*1.10WHERE职工号=(SELECT职工号FROMinserted)ENDEND定义语句当更新操作，生成两张视图如果仓库号b在仓库表中不存在，不符合参照完整性，事务回滚。如果仓库号b在仓库表中存在，且为WH2则增加工资第43页，共67页，2023年，2月20日，星期四使用系统存储过程查看触发器系统存储过程sp_help，sp_helptext和sp_depends分别提供有关触发器的不同信息。sp_help，通过该系统过程可以了解触发器的一般信息如触发器的名字属性类型创建时间使用sp_help系统过程的命令格式是sp_help

触发器名字第44页，共67页，2023年，2月20日，星期四sp_helptext

通过sp_helptext能够查看触发器的正文信息，其语法格式为

sp_helptext触发器名sp_depends

通过sp_depends能够查看指定触发器所引用的表或指定的表涉及到的所有触发器，其语法形式如下

sp_depends

触发器名字

sp_depends

表名第45页，共67页，2023年，2月20日，星期四修改删除触发器可以修改触发器的名字和正文使用sp_rename命令修改触发器的名字，其语法格式为

sp_renameoldname,newname用Alerttrigger命令修改触发器正文删除已创建的触发器有两种方法：用系统命令DROPTRIGGER删除指定的触发器，其语法形式如下

DROPTRIGGER触发器名字删除触发器所在的表时，将自动删除与该表相关的触发器。第46页，共67页，2023年，2月20日，星期四5.3数据完整性

在第3章已经介绍了在关系数据模型上数据完整性的概念和规则；在前一章介绍了CREATETABLE语句中可以实现的一些完整性约束。这里介绍与数据完整性有关的其他一些内容。.

第47页，共67页，2023年，2月20日，星期四5.3.1规则

在CREATETABLE语句中可以使用CHECK子句实现一些用户定义完整性或域完整性约束。另外还可以通过“规则”（RULE）来实现用户定义完整性或域完整性。CHECK约束固定在一个表的一个列上，它只在指定的列上起作用。如果在不同的列上有相同的约束条件，则可以使用规则，一个规则可以绑定在多个列上。规则是一种独立的数据库对象，它可以绑定到一个列上来约束该列的取值范围等。

第48页，共67页，2023年，2月20日，星期四规则的用法定义规则绑定到相应的列上第49页，共67页，2023年，2月20日，星期四建立规则的命令是：

CREATERULEruleAScondition_expression

rule：给出新建规则的名称；

condition_expression：定义规则的条件，可以是任何有效的表达式，并且可以包含诸如算术运算符、关系运算符和谓词（如IN、LIKE、BETWEEN）之类的元素。

注意：规则不能引用列或其它数据库对象，规则可以包含不引用数据库对象的内置函数；condition_expression需要包含一个变量，变量的前面有一个@前缀；该表达式引用通过

UPDATE或INSERT语句输入或传递的字段值。

第50页，共67页，2023年，2月20日，星期四例如，规定某类数值对象的取值范围是1000～3000，则可以定义规则：

CREATERULErange_ruleAS@range>=1000AND@range<=3000第51页，共67页，2023年，2月20日，星期四规则的绑定规则是独立的数据库对象，要通过系统存储过程sp_bindrule把规则绑定到数据列上，该系统存储过程的格式是：

sp_bindrulerulename,objname[,futureonly]

rulename是用CREATERULE命令建立的规则名；objname指出要绑定的表和列或用户定义的数据类型；futureonly，当绑定规则到用户定义的数据类型时可以选用此项，该选项是禁止已经存在的、用用户定义数据类型定义的列遵循新的规则。第52页，共67页，2023年，2月20日，星期四例如，将规则range_rule绑定到职工表的工资列上

sp_bindrulerange_rule,‘职工.工资’

使用CREATERULE命令创建的规则对象，可以绑定到多个数据列上，即一个规则可以反复使用。

第53页，共67页，2023年，2月20日，星期四绑定的消除和规则的删除绑定到数据列上的规则可以去除，相应的系统存储过程是sp_unbindrule。例如，取消绑定在职工表工资列上的规则可以使用如下语句：

sp_unbindrule'职工.工资'规则可以删除，删除规则的命令是DROPRULE，但是删除规则之前，必须首先解除所有的绑定。第54页，共67页，2023年，2月20日，星期四5.3.2默认值

在CREATETABLE命令中可以使用DEFAULT约束为数据列定义默认值。这里介绍另外一种方法：使用CREATEDEFAULT命令创建默认值对象。

第55页，共67页，2023年，2月20日，星期四CREATEDEFAULT命令的格式

CREATEDEFAULTdefaultASconstant_expression

default：是建立的默认值对象名；

constant_expression：定义默认值的常量表达式。第56页，共67页，2023年，2月20日，星期四例如，定义一个值为“北京”的默认值对象val_bj

CREATEDEFAULTval_bjAS'北京'第57页，共67页，2023年，2月20日，星期四绑定默认值

默认值是独立的数据库对象，它要作用于某个数据对象，则也和绑定规则一样，需要用类似的系统存储过程把默认值绑定到列，绑定默认值的系统存储过程是sp_bindefault，具体格式是：

sp_bindefaultdefname,objname[,futureonly]

defname：是用CREATEDEFAULT命令建立的默认值对象名；

objname：指出要绑定的表和列或用户定义的数据类型；

futureonly，当绑定默认值到用户定义的数据类型时可以选用此项，该选项是禁止已经存在的、用用户定义数据类型定义的列遵循新的默认值约定。第58页，共67页，2023年，2月20日，星期四例如，将定义的默认值对象val_bj绑定到仓库关系的城市列上和供应商关系的地址列上：

sp_bindefaultval_bj,'仓库.城市'sp_bindefaultval_bj,'供应商.地址'第59页，共67页，2023年，2月20日，星期四绑定的去除和默认值的删除绑定到数据列上的默认值可以去除，相应的系统存储过程是sp_unbindefault。例如，取消绑定在供应商表地址列上的默认值可以使用如下语句：

sp_unbindefault'供应商.地址'默认值也可以删除，删除默认值的命令是DROPDEFAULT，但是删除默认值之前，必须首先解除所有的绑定。第60页，共67页，2023年，2月20日，星期四5.3.3用