第7章存储过程和触发器7.1存储过程7.1.1存储过程的类型7.1.2用户存储过程的创建与执行7.1存储过
程7.1存储过程7.1存储过程7.1存储过程7.1存储过程7.1存储过程7.1存储过程7.1
存储过程7.1存储过程7.1存储过程7.1存储过程7.1存储过程7.1.3用户存储过程的编辑修
改7.1.3用户存储过程的编辑修改7.1.4用户存储过程的删除7.2.1利用SQL命令创建触发器7.2
.1利用SQL命令创建触发器7.2.1利用SQL命令创建触发器7.2.1利用SQL命令创建触发器7.2.1利
用SQL命令创建触发器7.2.1利用SQL命令创建触发器7.2.1利用SQL命令创建触发器7.2.2利用企业管理器
创建触发器7.2.3触发器的修改7.2.3触发器的修改7.2.4触发器的删除习题(1)系统
存储过程系统存储过程是由系统提供的存储过程，作为命令执行各种操作。(2)本地存储过程本地存储过程
是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以sp_为前缀。(3)临时存储过程临时存储过
程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用
。(4)远程存储过程远程存储过程指从远程服务器上调用的存储过程。(5)扩展存储过程在SQLServer环
境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQLServer系统中，并且按照使用存储过程的方法执
行。在用户存储过程的定义中不能使用下列对象创建语句：CREATEVIEWCREATEDEFAULTCREA
TERULECREATEPROCEDURECREATETRIGGER1．通过SQL命令创建和执行存储过程
如果要通过SQL命令定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩，然后调用该存储过程步骤如下：定义如下存储过程
USEXSCJGoCREATEPROCEDUREstudent_gradeASSELECTXS.学号,XS.姓
名,KC.课程名,XS_KC.成绩FROMXS,XS_KC,KCWHEREXS.学号=XS_KC.学号ANDXS_K
C.课程号=KC.课程号Go使用存储过程的优点：(1)存储过程在服务器端运行，执行速度快。(2)存储过程执行一
次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。
(3)确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。(4)自
动完成需要预先执行的任务。存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一
些需要预先执行的任务。调用存储过程EXECstudent_gradeGO通过上例了解了存储过程的使用，下面介绍创建
和执行存储过程的语法格式。1)创建存储过程语法格式:CREATEPROC[EDURE]procedure_na
me[;number]
/定义过程名/[{@parameterdata_type}
/定义参数的类型/[VARYING][=default][OUTPUT]?] /定义参数的属性
/[,...n1][WITH??{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRY
PTION}] /定义存储过程的处理方式/[FORREPLICA
TION]ASsql_statement[...n2]/执行的操作/
对于存储过程要注意下列几点：(1)用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在tempdb
中创建）。(2)成功执行CREATEPROCEDURE语句后，过程名称存储在
sysobjects系统表中，而CREATEPROCEDURE语句的文本存储在syscomments中。(3)自
动执行存储过程SQLServer启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在maste
r数据库中创建，并在sysadmin固定服务器角色下作为后台过程执行。(4)sql_statement的限制
除了SETSHOWPLAN_TEXT和SETSHOWPLAN_ALL外，其它SET语句均可在存储过程内使用。
(5)权限。CREATEPROCEDURE的权限默认授予sysadmin固定服务器角色成员、db_owner和db_dd
ladmin固定数据库角色成员。2）存储过程的执行通过EXEC命令可以执行一个已定义的存储过程。语法格式：[EXEC
[UTE]]?{??[@return_status=]{procedure_name[;number]
|@procedure_name_var}[[@parameter=]{value|@variable[
OUTPUT]|[DEFAULT]}[,...n][WITHRECOMPILE]｝存储过程的执行要
注意下列几点：(1)如果存储过程名的前三个字符为sp_，SQLServer会在Master数据库中寻找该过程。如果没
能找到合法的过程名，SQLServer会寻找所有者名称为dbo的过程。(2)参数可以通过value或@param
eter_name=value提供。(3)执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXECUTE
关键字。3)举例(1)设计简单的存储过程【例7.1】从XSCJ数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、学
分。该存储过程不使用任何参数。USEXSCJ/检查是否已存在同名的存储过程，若有，删除。/IFEXISTS(SEL
ECTnameFROMsysobjectsWHEREname=''stude
nt_info''ANDtype=''P'')DROPPROCEDUREstudent_infoGO/
创建存储过程/CREATEPROCEDUREstudent_infoASSELECTa.学号,姓名,课程名,
成绩,学分FROMXSaINNERJOINXS_KCbONa.学号=b.学号INNE
RJOINKCtONb.课程号=t.课程号GO(2)使用带参数的存储过程【例7.2】从XSC
J数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。USEXSCJIFEXISTS(S
ELECTnameFROMsysobjects WHEREname=''student_info1
''ANDtype=''P'') DROPPROCEDUREstudent_info1GOCREATEPRO
CEDUREstudent_info1 @namechar(8),@cnamechar(16)ASSEL
ECTa.学号,姓名,课程名,成绩,学分 FROMXSaINNERJOINXS_KCb
ONa.学号=b.学号INNERJOINKCtONb.课程号=
t.课程号 WHEREa.姓名=@nameandt.课程名=@cnameGO(3)使用带有通配符参数的存储
过程【例7.3】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有
提供参数，则使用预设的默认值。USEXSCJIFEXISTS(SELECTnameFROMsysobjects
WHEREname=''st_info''ANDtype=''P'') DROPPROCEDURE
st_infoGOCREATEPROCEDUREst_info @namevarchar(30)=''刘%''AS
SELECTa.学号,a.姓名,c.课程名,b.成绩 FROMXSaINNERJOINXS_KCb O
Na.学号=b.学号INNERJOINKCc ONc.课程号=b.课程号WHE
RE姓名LIKE@nameGO(4)使用带OUTPUT参数的存储过程【例7.4】用于计算指定学生的总学分，存储过程
中使用了一个输入参数和一个输出参数。USEXSCJGOIFEXISTS(SELECTnameFROMsysobje
ctsWHEREname=''totalcredit''ANDtype=''P'')DROPPROCEDUR
EtotalcreditGOUSEXSCJGOCREATEPROCEDUREtotalcredit@name
varchar(40), @totalintOUTPUTASSELECT@total=SUM(学分) FROM
XS,XS_KC,KC WHERE姓名=@nameANDXS.学号=XS_KC.学号 GROUPBYXS.
学号GO(5)使用OUTPUT游标参数的存储过程OUTPUT游标参数用于返回存储过程的局部游标。【例7.5】在XSC
J数据库的XS表上声明并打开一个游标。USEXSCJIFEXISTS(SELECTnameFROMsysobjec
tsWHEREname=''st_cursor''andtype=''P'') DROPPROCEDURE
st_cursor GO CREATEPROCEDUREst_cursor@st_cursorCURSORVAR
YINGOUTPUT AS SET@st_cursor=CURSORFORWARD_ONLYSTATICFOR
SELECT FROMXS OPEN@st_cursor GO(6)使用WIT
HENCRYPTION选项WITHENCRYPTION子句对用户隐藏存储过程的文本。【例7.6】创建加密过程，使用s
p_helptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。IF
EXISTS(SELECTnameFROMsysobjectsWHEREname=''encrypt_
this''ANDtype=''P'')DROPPROCEDUREencrypt_thisGOUSEXSCJ
GOCREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT
FROMXSGO(7)创建用户定义的系统存储过程【例7.7】创建一个过程，显示表名以xs开头的所有表及其对
应的索引。如果没有指定参数，该过程将返回表名以kc开头的所有表及对应的索引。IFEXISTS(SELECTnameF
ROMsysobjects WHEREname=''sp_showtable''ANDtype=''P'')
DROPPROCEDUREsp_showtableGOUSEmasterGOCREATEPROCEDUREsp
_showtable@TABLEvarchar(30)=''kc%''ASSELECTtab.nameASTAB
LE_NAME, inx.nameASINDEX_NAME, indidASINDEX_ID FROM
sysindexesinxINNERJOINsysobjectstabONtab.id=inx.id WH
EREtab.nameLIKE@TABLEGOUSEXSCJEXECsp_showtable''x
s%''GO2．利用企业管理器创建用户存储过程第1步在SQLServer企业管理器窗口中，选择相应的服务器、数据库和存
储过程图标（本例选择XSCJ数据库），单击鼠标右键，出现如图7.1所示的快捷菜单。第2步选择新建存储过程，则出现编辑存储过程
的属性窗口，在窗口中输入定义的存储过程，如图7.2所示，然后选择“确定”按钮。语法格式：ALTERPROC[EDURE
]procedure_name[;number][{@parameterdata_type}[VARYI
NG][0=default][OUTPUT]][,...n1][WITH??{RECOMPILE
|ENCRYPTION??|RECOMPILE,ENCRYPTION?}][FORREPLICATION]A
S??sql_statement[...n2]【例7.8】对存储过程student_info1进行修改。USEX
SCJGOALTERPROCEDUREstudent_info1 @namechar(8),@cnamech
ar(16)ASSELECTa.学号,姓名,课程名,成绩,学分 FROMXSaINNERjoin
XS_KCb ONa.学号=b.学号INNERJOINKCt ONb.课程号=t.课程号
WHEREa.姓名=@nameandt.课程名=@cnameGO【例7.9】创建名为select_stud
ents的存储过程，默认情况下，该过程可查询所有学生信息，随后授予权限。USEXSCJGOIFEXISTS(SELEC
TnameFROMsysobjectsWHEREname=''select_students''ANDtype
=''P'')DROPPROCEDUREselect_students /若该存储过程已存在，则删除/GOU
SEXSCJGOCREATEPROCEDUREselect_students /创建存储过程/ASSELE
CT FROMXS ORDERBY学号GO使用DROPPROCEDURE语句可永久地删除存储过程。在此之前
，必须确认该存储过程没有任何依赖关系。语法格式：DROPPROCEDURE{procedure}[,...n]
【例7.10】删除XSCJ数据库中的student_info1存储过程。USEXSCJGODROPPROCEDURE
student_info1procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除。语
法格式CREATETRIGGERtrigger_nameON{table|view} /
指定操作对象/[WITHENCRYPTION] /说明是否采用加密方式/{{{FOR|
AFTER|INSTEADOF}{[INSERT][,][UPDATE]}??[WITHAPP
END]????[NOTFORREPLICATION] /说明该触发器不用于复制/?????AS
?????[{IFUPDATE(column)????????????[{AND|OR}UPDATE
(column)]????????????????[...n]?????|IF(COLUMNS_UPDATED
(){bitwise_operator}updated_bitmask)????????????????{com
parison_operator}column_bitmask[...n]?????}]
/两个IF子句用于说明触发器执行的条件/????????sql_statement[...n]
/一条或若干条SQL语句/?}}?2．触发器中使用的特殊表
inserted逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。
deleted逻辑表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表
中。3．使用触发器的限制使用触发器有下列限制：(1)CREATETRIGGER必须是批处理中的
第一条语句，并且只能应用到一个表中。(2)触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。(3)
如果指定触发器所有者名限定触发器，要以相同的方式限定表名。(4)在同一CREATETRIGGER语句中，可以为多种操作（如
INSERT和UPDATE）定义相同的触发器操作。(5)一个表的外键在DELETE、UPDATE操作上定义了级联，
不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。(7)在触发器内可以指
定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。(8)触发器中
不允许包含以下T-SQL语句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE
、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT
、DISKRESIZE和RECONFIGURE(9)触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义
中包含SELECT语句或变量赋值。4．权限CREATETRIGGER权限默认授予定义触发器的表所有者、sysadmi
n固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员，并且不可转让。5．举例【例7.11】
对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。/使用带有提示消息的触发器/USEXSCJ
IFEXISTS(SELECTnameFROMsysobjects WHEREname=''remin
der''ANDtype=''TR'')DROPTRIGGERreminderGOCREATETRIGGER
reminderONXS FORINSERT,UPDATEASRAISERROR(4008,16,10)
GO【例7.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在K
C表中是否存在，若有一项为否，则不允许插入。USEXSCJIFEXISTS(SELECTnameFROMsysob
jectsWHEREname=''check_trig''ANDtype=''TR'')DROPTR
IGGERcheck_trigGOCREATETRIGGERcheck_trig ONXS_KC FORINSE
RTASSELECT FROMinserteda WHEREa.学号NOTIN(SELEC
Tb.学号FROMXSb)ORa.课程号NOTIN(SELECTc.课程
号FROMKCc)BEGIN RAISERROR(''违背数据的一致性.'',16,1) ROLLBACKTR
ANSACTIONEND【例7.13】在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，
并取消修改操作。USEXSCJGOCREATETRIGGERupdate_trig ONXS_KC FORu
pdateAS/检查学号列（C0）和课程号列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。/IF(COL
UMNS_UPDATED()&3)>0 BEGIN RAISERROR(''违背数据的一致性.'',16,1)
ROLLBACKTRANSACTION ENDGO6.INSTEADOF触发器的设计如果视图的数据来
自于多个基表，则必须使用INSTEADOF触发器支持引用表中数据的插入、更新和删除操作。如果视图的列为以下几种情况之一：
（1）基表中的计算列。（2）IDENTITYINSERT为OFF的基表中的标识列。（3）具有timestamp数据
类型的基表列。该视图的INSERT语句必须为这些列指定值，INSTEADOF触发器在构成将值插入基表的
INSERT语句时会忽略指定的值。【例7.14】在XSCJ数据库中创建表、视图和触发器，以说明INSTEADOFINSE
RT触发器的使用。USEXSCJCREATETABLEbooks(BookKeyintIDENTITY(1,
1),BookNamenvarchar(10)NOTNULL,Colornv
archar(10)NOTNULL,ComputedColAS(BookName+Color),
Pagesint)GO/建立一个视图，包含基表的所有列/CREATEVIEWView2AS
SELECTBookKey,BookName,Color,ComputedCol,Pages FROMbooksG
O/在View2视图上创建一个INSTEADOFINSERT触发器/CREATETRIGGERInsteadT
rigonView2 INSTEADOFINSERTASBEGIN/实际插入时，INSERT语句中不包含Boo
kKey字段和.ComputedCol.字段的值/ INSERTINTObooks SELECTBookNam
e,Color,PagesFROMinsertedENDGO步骤如下：第1步展开服务器组，然后展开服务器。第
2步展开“数据库”文件夹，展开将定义触发器的表所属的数据库，然后单击“表”文件夹。第3步选择将在其上创建触发器的表右击，出现
快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，如图7.1所示。执行该命令后，进入如图7.2所示的界面。第4步在
“名称”中，单击“新建”，在“文本”框中输入触发器文本。若要检查语法，单击“检查语法”命令。1．利用SQL命令修改触发器语
法格式：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYP
TION]{???{(FOR|AFTER|INSTEADOF){[DELETE][,][
INSERT][,][UPDATE]}????????[NOTFORREPLICATION]?????
???AS????????sql_statement[...n]????}??|{(FOR|AFTER|INSTEADOF){[INSERT][,][UPDATE]}????????[NOTFORREPLICATION]????????AS????????{IFUPDATE(column)????????[{AND|OR}UPDATE(column)]????????[...n]????????|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)????????{comparison_operator}column_bitmask[...n]????????}????????sql_statement[...n]????}【例7.15】修改XSCJ数据库中在XS表上定义的触发器reminder。USEXSCJALTERTRIGGERreminderONXS FORUPDATE ASRAISERROR(“执行的操作是修改”,16,10)GO2.通过企业管理器修改触发器进入企业管理器，修改触发器的步骤与创建的步骤相同，进入图7.2的界面后在“名称”对应的下拉表中选择要修改的触发器名即可进入触发器修改状态。说明：trigger_name：指要更改的现有触发器。如果原来的触发器定义是用WITHENCRYPTION或RECOMPILE创建的，那么只有在ALTERTRIGGER中也包含这些选项时，这些选项才有效。其他参数含义参考创建触发器命令部分。 |
|
