触发器及存储过程

1、数据库系统原理存储过程与触发器使用存储过程维护数据2救据库东经朦理使用存储过程维护数据客户端进行数据操作时反应较慢。 SQL Server提供“存储过程”这个机制来预先编 译好部分功能的语句，隐藏表的细节，提高执行 效率。一、存储过程的含义1、定义存储过程是一组为了完成特定功能的SQL语句 集合，经编译后以特定的名称存储在数据库中存储过程的主体是标准的SQL命令，包括SQL语句块、结构控制命令、变量、常量、运算符、表达式、流程控制语句等内容。二、存储过程的分类 系统存储过程 用户定义的存储过程 临时存储过程 远程存储过程二、存储过程的分类1、系统存储过程系统存储过程主要存储在master数据库

2、并以sp_为前缀，由SQL Server提供，可以作为命令直接执行，从系统表中获取信息。SQL Server中的许多管理性或信息性的活动都可以通过系统存储过程顺利有效地完成。可以在任意一个数 据库对其进行调用，在调用时不必在存储过程名前加上 数据库名。而且当创建一个新数据库时，一些系统存储 过程会在新数据库中被自动创建。/X,XL7二、存储过程的分类2、用户自定义的(本地)存储过程3、临时存储过程用户自定义的存储过程一般存放在用户建 立的数据库中，其名称前一般不加“§P_”前 缀，可以在管理平台和应用程序中调用，以 完成特定的任务。临时存储过程属于用户存储过程，存放在tempdb数据

3、库中。4、远程存储过程远程存储过程是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程o三、存储过程的创建1、在管理平台中创建存储过程(1)在对象资源管理器中展开“数据库”结点，选择要创建存储过程的数据库，然后展开“可编程性”结点，右 击“存储过程”结点，在弹出的快捷菜单中选择“新建 存储过程”命令.(2)打开新建存储过程模版。(3)根据用户需求创建存储过程。三、存储过程的创建1、在管理平台中创建存储过程对琼登港首理器国岂卷可区-Ld 0：W01V30Se««?FE7 (SQL Server 10.y卷据庄田_i系统数花层一1於据库快

4、照J DB_s tudentm an a ger J RoportSorver3二i数据库美索图国立表另一1视图3二司文词日I可16程空；:画面匾匚由题 数攵面触衷:嚣王La程序集a U类型田口规剧± 口默认值旬u计划花南SQLQueryl.s. .trator t53)|- XI - Description: <Description/ />臼 2REATZ FSCCZDTREsysJiaM, Procedurearae>一- Add paranie'cers Zzz me zorel procedure here <6P&xaml r sy

5、szarr.e, pl> <Daae Fgx Earar:；1, t -nc> <gParam2 r sysnane, p2> <Dauax;ypeFcr Parana, f ieo kSf1B£3I»- SET NOCOUNT CN added tc prevere extza result sets-mcezferxEg wich SELECT statements.SET MCCOU2TT OM;- 工"。=：二8fsz pzoz«duz« h«zeGOSELECT .&融L.a艮吟

6、的总，1R-2，业累糜£, 如 -EMC*您中城3%才10三、存储过程的创建2、使用Create Procedure语句创建存储过程Create Procedure/proc 存储过程名参数1数据类型”=默认数值IHoutputlJ参数2数据类型2"默认数值2 oiltput川,with recompilelencryptionlrecompile,encryption As<SQL语句块2、使用Create Procedure语句创建存储过程每个存储过程最多有1024个参数 参数的默认值可以是常量、NULL或包含通配符的字符串 Output表明该参数是一个输出参数，

7、执行存储过程时作为返回值，但是该参数不能是text类型 选项with recompile使得每次执行存储过程时重新编译，产生新的执行计划 选项with encryption将syscomments表中的存储过程文本进行加密，使用户不能利用sp_helptext查看存储过程内容。U!I、存储过程的执行1、利用图形工具执行（1）展开数据库结点，选择要执行存储过程的数据库。（2）依次展开：“可编程性”和“存储过程”结点，选择需要执行的存储过程，然后右键单击，在弹出的快捷菜 单中选择“执行存储过程”命令。（3）选择“执行存储过程”命令，出现参数指定对话框。单击“确定”按钮，在SQL编辑器中自动生代码,

8、当执行的存储过程无参数时，该对话框中的参数项为空。显示执行结果。（4）如果存储过程正常执行，则存储过程的返回值为“0”。1参;彳用"，力方142、利用SQL语句执行语法格式：Execute/exec存储过程名参数1=默认值数值1 output J参数2=默认值数值2 output,1应用：在学生成绩管理项目中使用存储过程任务1查询所有学生的学号和姓名。 使用图形工具创建存储过程：MJSET NOCCUMT CN added coSET NO2OUNT ON;crevenc exra 3aceraenc3.result se3siron通用”7上方16Insert sracemencs

9、 for procedure here SELECT s-no，学号 I s-name ,姓名，frcrc scudenc-ENDGOLj消息命令已成功完成.大方2、存储过程的执行15p5三：sdleniAnager ；GO工 value lt.z一 s tudentm 4naiger田、一j数拉库关系图 田£7表田口I视囱 田同义词 a可编程性 右键单击，弹m存储过程出式菜单中选 择“执行存储上 ，系统存储过程dbo.plEXECSELECT3C过程”(Si±iO函数口数据库触发弱 程序集3re-arn_value =感,Rex-rr, Val-e1 = grecurn

10、value学号姓名1i 0903100106 1张哲20903100107张杰31005030101张丽4100S030105刘灵51009030112王小米61009030201宋理程/1009030202郑动展8100980203张杰1Return Value 叵二二造果任务2根据学生学号，删除某个学生的基本信息 和选课信息，如果没有这个学号的学生，则给 出提示。create proc p2as beginIf exists(select * from student where s_no=,09031001,)begin)0delete from student where s_no=*

11、09031001 1delete from s_c where s_iw='0903100Tselect * from s_cendelse/ x.print，没有这个学生！，Select * From Student飞卸"，力方17存储过程的执行，s_nr>®«_»»bcrrdotecld5cna-»eWeohxieenrdldaleoctessccrrmertresxisoieteacher1：0903100106：张雪男NULLNULLKULL2314g29 田 1900NULLNULLNULL209)3131 力

12、9fS男NULLNU-LHULLX)14 舲29 15.21.00KULLNULLNULL310C903D1C6初灵女NULLNULLHULLNULLNULLNULLNULL41。：9：如12王小米*180MxM»计JTH.1£$0785433331W8-C2 00-0(/00修多市NULLNULL510030201宋理糕女199M31-O1 00:00:81273cse29292O1OM1 (XkOCOO新乡市NULLNULLG10C90X202郑功f!fl1992-C8-09 OCrXrCO187D95G3311X1DOM1 00:0000南田市NULLNULL7100

13、9030203张素9)1994C2-05 0C:30:CC计算Fl'>218904563322RKH)*H OaOQOONULLNULL81109030101AIMS女1WW243 0C1XCC计NFT118603278765R11 台1 OOOCDONULLNULL311C3DX102女1M5C2-1OOC：X：CC计箕缸llIB79C726655RI IQ义 1 OCk.OQ OO多市NULLNULL101109030103X199C2-11 OCrOO CC1S672453211X11-0M1 OOOQOO信阳市NULLNULL1111C9-3：2C1男l994-0-a3

14、0C.30.CC计算机112187039177882011go1 OWM.OO别多不NULLNULL121109030202刘出男199*120COCE计187D678SC432311-0M1 00：第 00涅河市NULLNULL1312C93X1C1线芯落女1 的0C-3CCC计机12 -18605S743223D12-0K1 OO-OC 00安丽NULLNULL1412C9030102郑法女19S7-O7-12OC:3O:CCvtMttlM1870$ 戈 33”R12 1 00:0000称港市NULLNULL1512C«)X103就差ILfl199M1-01 0C:X:CCi+U

15、fl'2-11870G99efi773312-OM1 oaoo-oo羽H市NULLNULL161X92101茶小小女199M1-13OC;DQ;CC189：汨节钻33I3-OW1 »OQ.OO洲H市NULLNULL171X90X102«S男1 的“329 0C.OGE计机1818SC78SC7873313-001 OOOC 00确mNULLNULL1813C9DX103王崂女0CHX3.0Ci+ttWM18S075383d3318刈 OQ.OO.OO寿州市NULLNULL1913C9D301CX王女1M4X5-01 OCXCC讨算机ill187D6725434331

16、3)1 OaOQOO旅阳市NULLNULL18五、创建带参数的存储过程任务3根据用户输入的学生姓名，查询学生的所有成绩。任务4根据用户输入的教师的教师号，检查这个教师是否 存在，若存在，显示出这个教师的基本信息，如果不 存在，则向系统输入这个教师的信息，同时输入这个 教师的授课信息。五、创建带参数的存储过程1、参数的分类（1）输入参数输入参数用来在调用存储过程时，将实际参数的值传 给对应的形式参数，并以此值参与存储过程中的数据处 理。 （2）输出参数和返回值通过使用输出参数和返回值，存储过程可将信息返回给调用的存储过程或客户端。如果在SQL中使用输出参数，必须在创建存储过程 和执行存储过程的语

17、句中同时指定OUTPUT关键字。如果省略，则存储过程仍将执行，但不会返回修改的值。212、使用图形工具创建带输入参数的存储过程(1)依次展开“StudentManager”数据库结点，“可编程 性”容器结点，选择“存储过程”，然后右击，在弹出 的快捷菜单中选择“新建存储过程”命令。(2)在细节窗格显示的新建存储过程模版中进行改写:222、使用图形工具创建带输入参数的存储过程(1)创建存储过程p3CREATE PROCEDURE p3- ITa :qc usra巧for the scored procedure hereJsnani* nchar (10 ： IAS-3EGTM一一 SET NC

18、COUilT ON added to prevent extxa result ses from一一 interfering wich SELECT sracerrnrs.SET NOCOUNT CN；| Tnaptf. «rfnr r看qyqSELECT student s_no '学F * r s_name '姓名 1 r c_no'l果程F ' , score 1 * From SC ,s&udentwhere SC.s no -student.s no and s name -Qsname2、使用图形工具创建带输入参数的存储过程存储过程

19、的执行1- , | Etudentmanager+ 口数强库关系图田口表王视图田一j同文词e Lj可编程性Q 口存储过程士系统存储过程王 dbo. pl三dbo p2pSE studer: tmara je r GCE DECLARE return_val-je Lnz3 EXEC ©return val-e = I坐g；，F3二-sname =歹王小米，dbo. p2SELECT Return Va 1-je - 3retur-_valueGC学号 姓名 课程W成绩i,土立而行】王小米 00219021009030112 王小米 0003 S6,，函数a 一I数据库触发坦Rexjm

20、 Valuei r0一一3、使用SQL语句创建带输入参数的存储过超创建存储过程p4根据用户输入的教师编号，检查这个教师是否存在，如果存在，显示出这个教师的基本信 息，如果不存在，向系统输入这个教师的信息，同时输入这个教师的授课信息o24，;彳川”了人分3、使用SQL语句创建带输入参数的存储过程创建存储过程p4create proc p4tno char( 10),tname nchar(10)tsex char(2)tbirth sirialldatetimetdept char(10),cno char(10),tcsem nvanAs beginif exists(select * fro

21、m Teacher where t_no=tno) select * from Teacher where t_no=tnoelsebegininsert into Teacher(t_no9tMname9t_sex9BornDate9dept_no)values (tno,tname,tsex ,tbirth,tdept)insert into TC values(tno ,cno,tcsem ) endselect * from Teacher where t_no=tnoselect * from TC where t_no=tno and c_no=cno end、使用SQL语句创建带

22、输入参数的存储过寝存储过程的执行(1)执行存储过程时直接传值sxeo 魅 2心00二2,3强林丽；，女3二9一二一二-9 333 3，“9120;2-201第二学期，3结果J占消息t_no tjemeBom DateTitle dept.no1； j行0而27 米林丽 ±1977-01 09 00:00:00 NULL 03t_no c_no semester1 花成1 cC9 201 >20上第二学期 ;I， t- «» 263、使用SQL语句创建带输入参数的存储过疲2、存储过程的执行（2）利用局部变量传值Declare tno char( 10),tna

23、me nchar(10),tsex char(12)Declare tbirth smalldatetime,©tdept char( 10),cno char(10)Declare tcsem nvarchar(30)set tno=,J0700205tset tname=，刘洋，set tsex='男，set tbirth='197687'set tdept='O2'set cno='c04'set ©tcsem ='20132014第一学期，弓” me:-e,c set se-Exec 煜二nanf,?匕

24、sex,3二bizxh.6七desc z Scno/ 3cosemTtet not namedept-no1t sex BomDcte男 1976-CS-C7 0C:30:00 NULL 02r-iJ070C2C5 ;刘洋t noc no semester3、使用SQL语句创建带输入参数的存储过程1Declare a 匕b 二匕 h small daQtdept char *10) F cno char 110)Declare 5ccseni nvarcnax (30)tno-fJCG0205f 刘洋1rsex=, 'cbxrtn=,1976-2-71zdepc=,02'cno

25、f cC41Q-cseni ='N。二 3-20第一学期284、使用SQL语句创建带输出参数的存储过程任务:,将该门课程的学分30create proc p5cno char( 10),ccredit decimal(5<l)outputAsbeginif exists(select * from Course where C_NO=cno) update Courseset C_Credit =C_Credit+lwhere C_NO=cnoselect ccredit =c_credit from Course whereC_NO=®cno /print cno+，

26、课程的学分为：(Accredit end29;彳Wz2存4、使用SQL语句创建带输出参数的存储过程执行存储过程Declare cno char( 10)ccredit decimal(54)set cno=*C06*exec p5 cnoccredit output臼 Declare cnc char(10)F Accredit decimal(5.1) sqz cno=t C061exec £5 Jcncz Sccred-t瞪消息消息=1%级别T6,状态S,过程第T。行 从数据类型varchar转换为nu.eric时出错. 、使用SQL语句创建带输出参数的存储过程执行存储过程修改语

27、句：print cno+,课程的学分为，+convert(varchar(10),ccredit)臼 Declare 3cnc char (10) , leered! de airmail (5Z1) sec cnc=r000|lfL exec 瑟 3cncx gccredi'c c葭二p二二<,二行受影晌0001课程的学分为：3.0六、使用存储过程调用触发器任务6：使用存储过程向学生表中输入一个新的学生信息,将刚输入的学生信息直接加载到选课表中。1、使用存储过程实现存储过程名参数名参数含义P6sno学号/snanie姓名 sex性别classnameenrolldate班级孽1

28、注册日期cno课程号创建一个带有输入参数的存储过程P6,在该过程中 实现输入操作。32六、使用存储过程调用触发器(1)使用存储过程创建p6create proc p6 sno char( 10),sname nchar( 10),ssex char(2), classnanie nvarchar(50),enrolldate smalldatetime9cno char(10)AsbeginIf exists(select 中 from student where s_no=sno)Print，该学生已经存在，勺Else)40Insert Into student(s_no,s_names_s

29、exclassname 9enrolldate) values(snosnanie,ssex 9classname 9enrolldate)Insert into SC(s_no,c_no)values(sno,cno)select * from student where s_no=snoselect * from SC where s_no=sno and c_no =cnoend/后33六、使用存储过程调用触发器器(2)执行存储过程p6Exec p6 1009030204：刘慧丹T女？计算机120109110003,exec £5，二“9C3” 1 3，刘慈丹 3，女，J讨算机

30、，2,二二 J二结果匕消息$sjiamebomdate cawname tetephone ercokJateaddress comnent neaponbteteacher1 009020204 I 刘慈丹 女 NULL 计域机 NULL 2010-03-01 00:00:00 NULL NULL NULLShmmb . «* «*M« «* yoejno $cr1 证函谖0)C3 NULL35六、使用存储过程调用触发器2、使用存储过程调用触发器实现用户可以创建一个带有输入参数的存储过程p7,在该过程中实现学生信息的输入操作，该操作将自动引发一个名为t

31、ri的触发器的执行，该触发器会根据输入的学生的学号，自动将这个学生的信息加入到选课表中，同时给该学生分配一门课程，课程号为“0001”。37再用少0方六、使用存储过程调用触发器35六、使用存储过程调用触发器(1)存储过程p7的创建Create Proc p7sno char( 10),sname nchar( 10),ssex char(2)9©classname nvarchar(50),enrolldate smalldatetimeAsbeginIf exists(select * from student where s_no=sno) Print，该学生已经存在，Elsei

32、nsert into student(s_no,s_name,s_sex,classname 9enrolldate)values(sno,sname,ssex,classnaine,enrolldate)select * from student where s_no=sno select * from SC/end3837六、使用存储过程调用触发器（2）存储过程p7的执行Exec p7 F109030105丫刘丹'，女，'计算机r,'20119Exec,刘丹、女计羿<2结果3泊Rej)obonxfateciae3roznetoooho>ccommentf

33、osoorobetoaoher11lO9C3C1Cf ：利用女NULLNULL2011吩01第笫00HULLNULLNULL3。i：10090301010001%21CCS03Q1C199310CS0301C1的41X9030112dC510090X112鸵 610090301039071X9O3C1C37081C090301C5NU.910CSO3C2C1851018903c2cl35111009C302C177121009030202951312090302c2NU1509031315NU_.16100903C2C4NU_.171：OSO3C2UNU_.18110

34、90X1C5NU使用触发器实现数据完整性一、触发器概述触发器可以在数据定义语言或者数据操纵语言修改指定表中的数据时自动执行。触发器是一种特殊的存储过程，是SQLServer为保证数据完整性和强制应用系统遵循业务规则而设置的一种高级约束技术，用户可以通过创建触发器在不同表中的逻辑相关数据之间实施完整性或一致性。.、触发器概述1、触发器的构成事件：对数据库对象的定义和操纵等操作。条件：对条件进行测试，满足则执行相应操作,否则什么也不做。动作：若触发器测试满足预定义条件，则DBMS执行这些动作。AuL .z nn 4isr、4 一触发器概述2、触发器的分类(1) DML触发器和DDL触发器。当数据库

35、中发生数据操纵语言(DML)事件时，调 用DML触发器。DML事件包含指定表上或视图上发生修 改数据的Insert、Update、Delete操作。当数据库中发生数据定义语言(DDL)事件时，调 用DDL触发器。DDL事件包括Create、Alter、Drop操作。通电/方41 一、触发器概述2、触发器的分类(2) After触发器、Instead Of触发器。After触发器：在Insert、Update或Delete语句操作 执行完成，并处理过所有约束后激活触发器。如果触发 器操作违反约束条件，将导致事务回滚，这时不会执行 触发器。在视图上不能定义After触发器。Instead of 触

36、发器：在执行Insert、Update或Delete 语句之约束检查之前执行触发器本身定义的操作，所以 可以对约束进行一些预处理，取代激发它的操作来执行。彳电”"出42二、触发器的实现1、两个特殊的临时的表当向表中输入数据时，所有约束都通过之后,在SQL Server中用两个特殊名称的临时表Deleted 和Inserted来保存输入和删除的记录。Insert触发器就会执行。新的记录不仅加入到触发器表 中，还会将副本加入到Inserted表中。当从表中删除数据时，所有数据约束都通过之后， Delete触发器就会执行，记录不仅从触发器表中被删除, 还会移到Deleted表中。簸辽丝2方

37、43二、触发器的实现"2、DML触发器与执行 (1)创建触发器Create Triggerv触发器名 Onv基本表名或视图名,with encrytionForlafterlinstead ofinsert 9 update , deleteAsvT_SQL语句块力方44二、触发器的实现2、DML触发器与执行(2) Insert触发器的工作方式向表中插入数据一触发Insert触发器执行一插入到数 据表中的新数据行同时自动写入Inserted表一判断如何执 行触发器动作一结束后Inserted表自动被删除。2、DML触发器与执行(2) Insert触发器的工作方式【创建触发器tri】创建Insert触发器，在向学生表输入一 条记录的同时输入这个学生的选课信息到学生的选课表中。create trigger trion student for insertbegindeclare sno varchar(lO) select sno=s_no