触发器与存储过程

/触发器与存储过程一、实验目的和要求1、理解触发器与存储过程的概念.２、掌握使用Ｔ_SQＬ创建、查看、删除触发器的方法。３、掌握使用T_SQL创建、查看、删除存储过程的方法.４、掌握触发器和存储过程在数据库中的应用。二、实验内容和步骤㈠触发器1、触发器的概念触发器是一段能自动执行的程序,是一种特殊的存储过程，其特殊性在于：不允许使用参数,没有返回值。不允许用户调用，当对表进行插入、删除、修改操作时由系统自动调用并执行(相当于事件方法）.为数据表中某个字段设置触发器后，当该字段的数据被INSEＲT插入、DELEＴE删除、UPＤAＴE修改更新时，触发器便被激活并自动执行.SＱＬＳｅrver按触发器被激活的时机可分为“后触发"和“替代触发”两种触发方式。（1）后触发若引发触发器执行的语句通过了各种约束检查,成功执行后才激活并执行触发器程序,这种触发方式称为“后触发”。后触发的特点:若引发触发器执行的语句违反了某种约束,该语句不会执行，则后触发方式的触发器也不被激活。后触发方式只能创建在数据表上,不能创建在视图上。一个表可以有多个后触发触发器。（2）替代触发若激活触发器的语句仅仅起到激活触发器的作用,一旦激活触发器后该语句即停止执行,立即转去执行触发器的程序—激活触发器的语句并不被执行，相当于禁止某种操作.这种触发方式称为“替代触发".替代触发的特点:

替代触发可以创建在表上，也可以创建在视图上。

一个表只能有一个替代触发的触发器。２、触发器使用的ｉnsｅｒteｄ临时表和ｄeleted临时表不论后触发或替代触发，每个触发器被激活时，系统都自动为它们创建两个临时表：insertｅｄ和ｄeleteｄ表.两个表的结构与激活触发器的原数据表结构相同。用IＮSEＲT语句插入记录激活触发器时，系统在原表插入记录的同时，也自动把记录插入到insertｅd临时表。用SELECT语句删除记录激活触发器时，系统在原表删除记录的同时，会把删除的记录添加到deleted临时表。用UPDAＴE语句修改数据激活触发器时，系统先在原表删除原有记录，删除的记录被添加到ｄeleｔｅｄ临时表,然后再插入新记录，并同时插入到inserｔｅd临时表。用户可以用SELＥCT语句查询这两个临时表，但不允许进行修改。触发器一旦执行完成,这两个表将被自动删除。３、触发器语法格式：ＣRＥＡTETRＩGGER触发器名OＮ｛表名｜视图名｝{fｏr|ａfteｒ|iｎstｅadｏf}[inserｔ，update，delete][ＷITHencrｙｐtioｎ］AＳSQＬ语句系列[ＲOLLBACKTRANSAＣTIＯN］——事务回滚ON{表名｜视图名｝:指定激活触发器被操作的表或视图。for与after：指定所创建的触发器为后触发方式，fｏｒ与afteｒ完全相同，for是为了与以前老版本兼容而保留.ｉｎsteadｏf:指定所创建的触发器为替代触发方式。iｎserｔ,updａｔe,deｌeｔe：指定激活该触发器的具体操作,可以指定一项，也可三项同时指定，但必须以逗号隔开。SQＬ语句系列：即触发器被指定操作激活后要执行的SＱL代码，其中可包含获得被操作数据的ＳELＥCT语句：后触发方式被操作数据一定在ｉｎsｅrｔed或dｅletｅd临时表中。如果被操作的数据是多值的,可用IＮ判断是否被包含在其中：被操作数据IN（SＥＬＥCT被操作字段FROＭ临时表）如果被操作的数据是单值的，可用以下语句获得：ＳＥLECT@变量=被操作字段ＦROM临时表ROLLＢACＫＴRANSACTＩＯＮ：事务回滚语句。对于后触发方式,语句已经执行完毕才执行触发器，如果发现操作不符合规则，可用该语句取消操作.４、触发器实例STEP１、创建《电脑器材销售管理》数据库ｄianｎaoxs。代码如下：CRＥAＴEDATAＢASEｄiannaｏｘs—-创建数据库ＯN（NAME＝ｄiannaoxｓ1，-—创建主数据文件='D：\DＮＸＳ＼diaｎnaoxs１。mdf',SＩZE=1，MAXSＩZE=UNLIＭITED，－—最大容量不受限制=1０％),（NAMＥ＝diａｎnaoｘs2，-－创建辅助数据文件＝'D:\ＤNXS\diａnnaoxs２。ndf’，SIZE=１,MAXSIZＥ=ＵNLIＭITＥD，=１０%）LＯGON/＊创建事务日志文件*/(ＮAME=dianｎaoxslog，='Ｄ:\DNXS\diaｎｎaoxslog.ＬＤF',SIZE＝5０0KB，MAXSＩＺＥ=５，/*日志文件最大容量5ＭＢ＊/＝５0０KB)STEP２、在数据库diａnｎaoxs中创建4张表。请留意每张表的字段属性及约束.涉及到数据库完整性的内容，包括默认值、规则、数据表结构的修改等，不明之处可参见前面的实验.usedｉannａｏxsＣＲEATＥTABＬE商品一览表(ﻩ货号cｈａｒ(4）ＮOTNULLPRIMARYKEY, 货名nvarｃhar(8）NＯTNUＬLｄefault（'计算机＇）， 规格ｃhar(4)ＮOTNULL,ﻩ单位chａr(2）ＮOTＮULL，ﻩ参考价格ｎumeriｃ（6，2)NUＬL，ﻩ库存量ｉntＮＵLLdefaｕlt（０））CREATETABLE员工表( 员工ＩDｃhar（5)NOTNULＬCONSTRAINTＰK_员工表PRＩMARYKEＹ, 姓名vａｒchaｒ（8)NＯTNUＬＬCＯNSＴRAＩNTIX_员工表ＵNIQＵＥ, 性别biｔNOTNULLCONSＴRAINTCK_员工表ＣHEＣK（性别＝0or性别=1), 出生日期datｅtimｅNOTNULL,ﻩ部门nvａrcｈar(５)NＯTNULＬCONSTRAINＴＤＦ_员工表_部门DＥＦＡＵLＴ（'销售科'),ﻩ工作时间sｍａlldatetimｅNOTNＵＬLﻩ）ＣRＥATETＡBLＥ销售表（ 序号bｉgｉｎｔIＤEＮTITＹ(1，1)ＮOTNＵLLPRIMARYＫＥY, 销售日期ｓmallｄatｅtimeNＯTNＵＬL,ﻩ客户名称nvaｒcｈar(1５)NOTNUＬL, 货号cｈar(４)ＮOＴNULＬ，ﻩ货名nvaｒcｈar（８）NＵLL, 单价ｓｍallmｏｎeyNOTＮＵＬＬ，ﻩ数量iｎtNOＴNULL, 金额mｏｎｅｙNULＬ，ﻩ销售员varchaｒ（8)NOTNＵLLﻩ）GＯCREATＥDEＦAＵLTDeｆａultDａteGOsｐ_bｉnｄefaultDefａultDate,'销售表.销售日期＇GＯcreatｅｒule大于0as@y＞0ＧOSp_binｄｒulｅ大于０,’销售表.单价＇GＯＳp_bindrule大于０，＇销售表．数量＇GOAltertａble销售表ＡddConstrａｉｎtFＫfFｏrｅignKey(销售员)Ｒeferences员工表（姓名)CＲＥＡＴＥＴAＢＬE供货商表(供货商IDＣhaｒ(4)ｎotnullprimarykｅycｈeｃk（供货商ＩＤlｉke'［a-zA－Z0－9]［ａ—zA-Z0－9］[ａ-zA-Ｚ0-9]［a－ｚA-Z0－９］’），供货商Nvａrchar（1５）ｎｏtnull,厂家地址Nvａrchａｒ(２0）nｏtnulluｎiｑｕｅ,账户Ｃhaｒ(15）notｎｕllunique，联系人Varchaｒ(8））ＡＬTERTABLE供货商表ａdｄ联系电话chａｒ（13)nulｌAＬTERTＡBLE供货商表ａltercｏlumｎ联系电话chａｒ（２0）nｏtnullALTERTＡBLE供货商表adｄcｏnｓtraiｎt惟一厂家unique（供货商)AＬTERＴABLＥ供货商表ａddcｏｎｓtraint电话约束unｉque（联系电话)，ｄefaulｔ'’FOＲ联系电话注释：新增加字段时可以同时设置空值约束、默认值约束。

若不允许为空时则必须给新增加的列指定默认值，否则语句执行错误。添加的字段若不允许为空则必须设置默认值,如果不允许为空又不需要默认值，可在添加字段时先允许为空，再用alterｃolumn子句修改为不允许为空,则没有默认值.ＳTEＰ3、数据操纵,往各表中添加记录。INSERＴ商品一览表(货号,货名，规格，单位，参考价格，库存量）ＶAＬUＥＳ（’1０01’，’计算机＇,’LC','套’,58００，16)INSERT商品一览表（货号，货名,规格，单位，参考价格,库存量）VAＬＵＥS('10０2’,defauｌt，＇LX’，'套'，5600，8)INＳEＲＴ商品一览表（货号，货名，规格，单位,参考价格,库存量)VALUＥＳ('20０1’,’显示器',’15','台’，980，26)IＮSERT商品一览表(货号,货名，规格，单位，参考价格,库存量)ＶALUES(＇２0０2'，'显示器＇，＇1７’，＇台’,１２50，2３)INSEＲT商品一览表(货号，货名，规格，单位,参考价格，库存量)VAＬUES（’3００１’,'CPU处理器',’P４’，'个’，４20,48）ＩNＳERＴ商品一览表(货号，货名,规格，单位,参考价格，库存量）VＡＬUES（'4００１＇，'内存储条'，'５１2M’，'片＇，225.5，７0）IＮＳERT商品一览表(货号,货名，规格，单位,参考价格,库存量)VＡＬUＥS（＇400２'，'内存储条＇,'１G’，'片'，335.５,105）INSEＲT供货商表VＡLUES(＇SＤＬC’,'山东省浪潮集团公司销售公司’，’济南市山大路１００8号','１0０2—305－６'，＇刘绪华＇,’8’）INＳERT供货商表VALUEＳ(＇BJFZ'，＇北京方正电脑有限公司'，＇北京市海淀区友谊路23５号甲','20０06786570＇,’王连胜’，'＇）INＳＥRＴ供货商表VALUEＳ（’BJＬX’，’北京联想科技股份有限公司'，＇北京市中关村6０６8-6号＇,＇11204567765',＇赵捷’，’＇)ＩNSＥＲＴ供货商表VALUES('SＨSC＇,’上海电脑市场器材销售中心'，'上海市虹口区8弄科技路2２5号’,'336-44８-66９’,’李群'，'’)IＮSEＲＴ供货商表VAＬＵＥS（'SHＫD＇,＇上海科大计算机技术服务公司','上海市浦东东方明珠5925号＇,’22468０00１２’,’张茂岭＇，ｄefauｌｔ）INSERT供货商表VALＵES(’SＤＫJ＇，’山东科技市场计算机销售处＇,'济南市经七纬二路9４15号','12４5—７84５—5５',NULＬ，'')IＮＳＥＲT员工表(员工ＩD，姓名，性别,出生日期,部门，工作时间）VＡLUＥS（＇11001'，’吕川页’,1，'１963－３-7'，’办公室＇,’19８5-2—INSERT员工表（员工ID,姓名,性别,出生日期，部门,工作时间)VＡLUES（’2200１’,’郑学敏',0,'１96９—１1-２3＇,＇办公室',＇1994INＳEＲＴ员工表(员工ID,姓名，性别，出生日期，部门，工作时间）VALUES（’22０02＇，’于丽'，0,'1980—12—5’，’IＮSＥRT员工表(员工ＩD,姓名，性别,出生日期，部门,工作时间)VALＵES('22０03',＇孙立华’，１,’１979—5-4’,＇材料处','IＮSERＴ员工表（员工ID，姓名,性别,出生日期，部门，工作时间）VALUES('3300１'，＇高宏’，1，'19８2－9—２9＇,ｄefault，'INＳＥRT员工表（员工ID,姓名,性别,出生日期，部门,工作时间）VＡLUＥS（’330０２＇,’章晓晓'，０,＇19８0－11—1＇,＇销售科',’２0０0-5－30IＮSEＲT员工表(员工ＩD，姓名，性别,出生日期，部门,工作时间)VAＬUES（'3３003＇，’陈刚',1，'1979—6—３0’IＮSEＲT销售表（销售日期，客户名称，货号,数量,单价，销售员）VＡＬUＥS(＇2010—３－２0＇，'济南新浪计算机公司’，3，57８０,＇高宏’)ＩＮＳＥRT销售表（销售日期，客户名称,货号，数量，单价,销售员)ＶALＵES（'20１０—6－20＇，'济南新浪计算机公司'，5，40０,'章晓晓'）INSERT销售表(销售日期，客户名称,货号，数量，单价，销售员)VALUES('2010-１1—20'，’潍坊电脑器材商店'，'40０225，32０,＇陈刚’）ＳＴEＰ4、触发器应用例题例一：为“员工表"创建一个名为“修改姓名”的后触发器，当修改某个员工姓名时，需要把“销售表”的“销售员”同时进行修改,实现级联修改。首先删除在企业管理中删除“销售表"上的外码约束FKｆ.在查询分析器中输入以下代码:IFｅｘｉｓｔｓ(SＥLECＴnaｍeFＲOMsｙsobjｅctsWHEREnaｍe='修改姓名'ＡNDtｙpe=’tｒ＇）DROPＴＲＩGＧER修改姓名GＯCＲEATＥTRIGGER修改姓名OＮ员工表aｆterｕpｄatｅASDEＣLARE＠xm1vａｒchａr（8），@xm2varｃhar（８）ＳELECＴ＠xm1＝姓名ＦROMdelｅted--从dｅleｔed表得到被删除的原姓名SＥＬＥＣT＠xｍ2＝姓名ＦRＯＭiｎserted－—从ｉnserted表得到被更新的新姓名UPDATＥ销售表SET销售员＝＠ｘｍ２WHＥRE销售员=＠xm1GO请同学自己验证此触发器的作用。例二：为“销售表”创建一个名字为“销售检查”的后触发器，当销售某种商品添加一条新记录时，能自动执行以下操作:自动检查销售“数量”不允许大于《商品一览表》中的“库存量”。自动检查“单价”下浮或上调不允许超出《商品一览表》“参考价格”的5％范围.根据“货号”自动从《商品一览表》中获得相应的“货名”数据.自动计算“金额=单价＊数量”。对《商品一览表》中“库存量”进行自动更新。实现此例触发器的语句如下：IFｅｘｉsts(ＳＥLECTnameFROMsｙｓoｂｊeｃｔｓWＨＥＲEnaｍe=’销售检查’ANDtｙpe=＇tr＇)ＤRＯPＴRIGGEＲ销售检查GOCREATETRＩＧGER销售检查ON销售表afterｉnｓertＡSDＥＣＬARE@xhBigInt，@hhchａr（4)，＠slｉnt，＠ｄjSmaｌlmoney，@ｈmNvarchar（8)，@ckｊgｎumeｒic(6,2）,＠kｃinｔSELECT@xh=序号,@hｈ=货号，@ｓl=数量，@ｄj＝单价FROMｉnserｔedSEＬECT@ｈm=货名,@ｃkｊg=参考价格,@ｋc＝库存量FROM商品一览表ＷＨERE货号=＠hhIF＠ｓl＜=＠ｋcBEGINIF＠ｄj＞=＠ckjg*0.９５ＡNＤ＠dj<=＠ckjｇ＊１.0５BEGINUＰＤATＥ销售表--条件不能使用货号SEＴ货名=＠ｈm，金额=＠sl*@djWHEＲE序号=＠xhUPDAＴE商品一览表SEＴ库存量=@ｋc—＠slＷHEＲE货号=@hhENDEＬＳＥBEＧＩNＰＲINT＇单价超出参考价格’+cａst（＠ckjgASｖarchar(１０））＋'的％5范围，不能销售'ＲOLＬBACKＴRANSACＴIOＮ-—事务回滚，撤消插入ENDＥＮＤELSEＢEＧＩNＰRINT’销售量大于库存量’＋casｔ（＠kｃASvaｒchar（4））+'，库存不足不能销售'RＯLＬBACKＴＲANＳＡCTＩＯNENDGO下面的INSERＴ代码在上述触发器的影响下，可生成不同的响应.ＩNSＥRT销售表（销售日期,客户名称,货号，数量，单价,销售员）VAＬＵES（'2０１0—12－20’，＇北京市海淀区友谊路２35甲'，'4001’,８0，2４０，＇陈刚—－销售量大于库存量70,库存不足不能销售ＩNＳERT销售表(销售日期，客户名称，货号,数量，单价，销售员）VALＵES(’2０1０-1２－20’，'北京市海淀区友谊路23５甲’，’4001’，１0,2４0，＇陈刚’）－-单价超出参考价格２２5。５0的％５范围，不能销售INＳERT销售表（销售日期，客户名称，货号,数量，单价,销售员）VALＵES(’2０1０-12—２0＇，'北京市海淀区友谊路235甲','4001'，１0，23０，'陈刚例三：为“商品一览表”创建一个名为“修改商品”的后触发器,禁止修改“库存量”；当修改某个商品的货名时，需要把“销售表”中相应的数据同时全部修改，实现“商品一览表”和“销售表”的级联修改。IFｅxists(SEＬＥCTnameFRＯMsysobｊeｃtsWHＥREｎame=＇修改商品’ＡNDtype=＇tr＇）DＲOPTRIGGＥR修改商品ＧOCＲＥATETRIGGＥR修改商品ON商品一览表aftｅｒupdａｔeＡSＤECＬARE@ｈｈchａr（４)，＠hｍｎvａrcｈａr(８）,＠pｊ１int,＠pj2ｉｎt，＠kc1iｎt，＠kc２intSEＬECT＠hｈ=货号，@kｃ１=库存量ＦROMｄeｌeｔｅdSELECT＠ｈm=货名，＠ｋc２=库存量FＲOMiｎseｒｔedＩF＠ｋｃ１<＞@ｋc2BEGＩNPＲINT＇库存量不允许修改！'ＲOＬLBACKTRAＮSACTIＯN－-事务回滚，撤消修改ENDＥＬSＥＵＰDAＴE销售表SET货名＝＠hmWHERE货号=@hhGO若执行uｐｄaｔe商品一览表set库存量＝100whｅre库存量＝10５语句会看到系统提示“库存量不允许修改!”若在“商品一览表”中把货号为4001的货名改为“内存条”,可在销售表中看到级联修改效果。例四：设置替代触发器“禁止修改”，不允许对“供货商表"的厂家记录进行修改、删除。IFeｘists(SELＥCTnameFRＯMｓysobjectｓWHEREｎame=’禁止修改＇AＮＤｔype='tr’)DROPＴRIGGＥＲ禁止修改GOCREATＥTRIGＧER禁止修改ON供货商表insteadofupdate,delｅtｅ－-修改或删除激活AＳPRＩNT’请原谅，《供货商表》不允许对任何数据修改和删除。'ＧO该触发器为替代触发,只要对“供货商表"进行任何修改、删除操作,则立即停止并取消该SＱＬ语句对“供货商表”的操作，激活并执行触发器,所以不需要事务回滚语句。5、触发器的查看在控制台根目录下展开数据库→展开数据表节点，选中要创建触发器的表，右键单击鼠标，从快捷菜单中选择“所有任务”→“管理触发器”命令，弹出“触发器属性”对话框。语法格式：Ｓｐ_helｐ触发器名Ｓp_ｈelpｔext触发器名６、触发器的删除Ｄroptrigger触发器名(二）存储过程１、存储过程的概念存储过程（storedｐroceｄure)是由一系列对数据库进行复杂操作的ＳQＬ语句、流程控制语句或函数组成的，并且将代码事先编译好之后、象规则、视图那样作为一个独立的数据库对象进行存储管理。使用存储过程的优点执行速度快：存储过程在创建时已经通过语法检查和编译,调用时则直接执行,程序的运行效率高,其执行速度要比标准SQＬ语句快得多。含有大量SQL语句的批处理需要重复多次执行时，定义为存储过程可大大提高运行效率。利于模块化程序设计:存储过程创建后，即可以无数次随时任意繁荣调用。可根据不同的功能模式设计不同的存储过程以供调用。便于程序的维护管理:当用户对数据库使用的功能改变时,只需对相应的存储过程进行修改而不用修改应用程序.减少网络通信量:存储过程可包含大量对数据库进行复杂操作的SQL语句，它的存储执行都在ＳQLSｅrｖer服务器(数据库)端，网络用户使用时只需发送一个调用语句就可以实现，大大减少了网络上SＱＬ语句的传输。保证系统的安全性:可以在存储过程中设置用户对数据的访问权限，只允许用户调用存储过程而不允许直接对数据进行访问，充分发挥安全机制的作用。2、存储过程的语法格式：CREAＴＥPROCEＤURE存储过程名［＠形参变量数据类型［=默认值］［oｕtｐut］］［，…n］ASSQL语句系列其中：＠形参变量为指定接收调用参数或返回值的变量，默认状态下只表示单一数值,不能代表表名、列名或其他对象名,形参变量的作用域为该存储过程;

默认值：调用过程语句时不提供参数时，形参变量则取该默认值.默认值只能是常量或ＮULL。Ｏutpｕt:指定形参变量是返回给调用语句的参数，可以是所有数据类型，也可以是游标占位符.注意：在数据库内创建的每个对象(约束、默认值、日志、规则、存储过程等）都会作为该库系统表Sｙsobｊects中的一条记录占一行，该表的结构如下表所示：Sｙsｏbjectｓ系统表的结构列名数据类型存储内容说明ＮameSysname对象名Idiｎt对象标识名Xtype或tｙpｅChar(2）对象类型。其中的主要类型:C=CHＥＣK约束P=存储过程Ｄ=默认值或DEＦAＵLT约束TＲ=触发器Ｆ=FORＥIGＮＫEＹ约束Ｖ=视图K=PRIＭＡRYＫEY或UNIＱUE约束R=规则FN=标量函数（自定义函数）U=用户表L＝日志Ｓ=系统表３、存储过程实例：例一：在dｉannａoxｓ数据库中建立一个名为“计算机_pro”的存储过程,用于在“商品一览表”和“销售表”中查询“计算机”产品的基本信息及其销售情况。先判断sysobjｅctｓ系统表中是否存在名为“计算机_pro"的存储过程,如果存在则删除原有的存储过程，然后再创建。IＦexｉsts（SELEＣTnａｍｅFROMｓysoｂjecｔsWＨEＲEname＝’计算机_pｒo＇ANDtype='p’)DＲOPproceduｒｅ计算机_proGOCREATＥproceｄure计算机＿prｏ－－创建存储过程ASSEＬECＴ＊ＦRＯM(selｅct*from商品一览表ｗhere货名=’计算机＇）ａsCＴｌeｆtjoin销售表onＣT．货号=销售表．货号ＧOＥXECUTE计算机＿pｒo－—调用执行存储过程例二:建立一个名为“商品＿ｐｒｏ”的存储过程，带有一个参数接收指定的商品名称,于在“商品一览表"和“