《存储过程与触发器》PPT课件.ppt

SQLServer2005数据库应用技术（第二版）,第10章存储过程与触发器（2）,复习,上次课的主要内容创建存储过程的语法及准则创建简单存储过程创建接受输入参数的存储过程创建接受输出参数和返回值的存储过程查看、修改和删除存储过程,导入,在实际的数据库应用中，往往需要系统具备一些能自动执行的功能，以保证数据的一致性和完整性。问题：如何保证数据的一致性和完整性？约束触发器与CHECK约束、默认值和规则相比较，触发器可以强制实现更加复杂的数据完整性，而且可以参考其他表的字段。在表中如果既有约束又有触发器，则在执行时约束优先于触发器。,10-2触发器,10.2.1触发器概述1.触发器及优点：触发器是一类特殊的存储过程，它不能直接调用执行，也不能传递与接受参数。触发器定义在一个表（视图）中，该表（视图）称为触发器表。当触发器表执行插入（INSERT）、修改（UPDATE）、DELETE（删除）操作时，触发器被触发，SQLServer就会自动执行触发器所定义的SQL语句，以保证数据的完整性与一致性。当表被删除后，与它关联的触发器一并被删除。,10-2触发器,优点：触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。触发器可以通过数据库中的相关表进行层叠更改。触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。2触发器的限定（1）触发器只在触发它的语句完成后执行，一个语句只能触动一次触发器。（2）如果语句在表中执行违反条件约束或引起错误，触发器不会触动。（3）触发器视为单一事务中的一部份，因此可以由原触发器复原事务，如果在事务过程中侦测到严重的错误（如用户中断联机），则会自动复原整个事务。（4）当触发器触动时若产生任何结果，就会将结果传回其调用的应用程序。,10-2触发器,10-2-2触发器的分类按引起触发的操作语言的类型,可分为DDL触发器与DML触发器.1、DML触发器根据引起触发的操作又可分为：INSERT触发器、UPDATE触发器、DELETE触发器根据引起触发的时刻可分为：AFTER触发器与INSTEADOF触发器。（1）AFTER触发器（之后触发器）是在执行引起触发的操作（INSERT、UPDATE、DELETE）和处理完约束之后，再执行触发器所定义的操作，即之后触发。当执行INSERT、UPDATE或DELETE语句时，违反约束条件，INSERT、UPDATE或DELETE操作将被中止，所以，AFTER触发器所定义的操作也将不会执行。（2）INSTEADOF触发器（替代触发器）：是指当执行引起触发的操作（INSERT、UPDATE、DELETE）时，直接激发触发器，执行触发器所定义的操作，而不再执行INSERT、UPDATE或DELETE语句本身。即将触发器的程序替代了INSERT、UPDATE或DELETE操作语句执行。,10-2触发器,10-2-2触发器的分类2、DDL触发器SQLSERVER2005的新增功能DDL触发器的触发事件是在CREATE、ALTER、DROP，以及GRANT、DENY和REVOKE等DDL语句。触发的时间条件只有AFTER。DDL触发器可以用于管理任务，如审核和控制数据库操作。,10-2触发器,10-2-2触发器的分类3、DDL与DML触发器的相似点（1）两者可以使用相似的Transact-SQL语法创建、修改和删除，它们还具有其他相似的行为，都可以嵌套运行。（2）都可为一个Transact-SQL语句创建多个触发器，同时，触发器和激发它的语句运行在相同的事务中，并可从触发器中回滚此事务。（3）两者均可运行在Microsoft.NETFramework中创建的以及在SQLServer中上载的程序集中打包的托管代码,触发器,10-2-3创建触发器创建触发器时，应确定以下内容：触发器的名称相关联的表（触发器表）或视图（触发器视图）（对DDL触发器而言，是当前的服务器还是数据库）触发器将何时激发（之前还是之后）（对DDL触发器而言，只有AFTER）激活触发器的数据操作语句（对DDL触发器而言，是数据库定义语句）触发器本身需要完成的功能,触发器,DML触发器的创建基本格式：CREATETRIGGERtrigger_nameONtable|view-在指定表或视图上创建DML触发器FOR|AFTER|INSTEADOF-指出DML触发器类型INSERT,UPDATE,DELETE-指出触发的语句ASIFUPDATE（列名1）AND|ORUPDATE（列名2）n-如果触发的语句是UPDATE，指出修改的是哪列SQL_statement-AS引导的SQL语句指出了触发器需要完成的工作,触发器,DDL触发器的创建基本格式：CREATETRIGGERtrigger_nameONALLSERVER|DATABASE-在当前服务器或当前数据库上创建DDL触发器FOR|AFTER-指出DDL触发器类型event_type|event_group,n-指出激发DDL触发器的T-SQL事件名称或事件分组名称ASSQL_statement-AS引导的SQL语句指出了触发器需要完成的工作,触发器,例1创建一个DML触发器,当操作者试图向成绩表中添加或修改数据时,该触发器向客户端显示一条消息.ifexists(select*fromsysobjectswherename=xs_t_uandiandtype=tr)droptriggerxs_t_uandigocreatetriggerxs_t_uandionxscjforinsert,updateasprint插入或更新了成绩表go-insertintoxscjvalues(200010,202,77),触发器,例2创建一个DDL触发器,当操作者试图创建、修改和删除数据库表时，该触发器向客户端显示一条消息。ifexists(select*fromsys.triggerswherename=safetyandtype=tr)droptriggersafetyondatabasegocreatetriggersafetyondatabaseforcreate_table,drop_table,alter_tableasprint创建、修改或删除了数据库表go-droptablefxsda,触发器,查看触发器（DML）的相关信息sp_helpxs_t_uandi-查看触发器的名称、属性、类型与创建时间sp_helptextxs_t_uandi-查看触发器的定义文本sp_dependsxs_t_uandi-查看触发器的依赖关系sp_helptriggerxscj-查看表中所有的触发器信息,DML触发器的应用,DML触发器中使用的两个特殊表执行触发器时，系统创建了两个特殊的逻辑表：inserted表和deleted表。（1）inserted表：当向触发器表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。（2）deleted表：用于保存已从触发器表中删除的记录，当触发一个DELETE触发器时，被删除的记录保存到deleted表中。（3）修改一条记录相当于插入一条新记录，同时删除原来旧记录。当UPDATE触发器触发时，触发器表中原记录移到deleted表中，修改后的记录插入到inserted表中。,INSERT触发器,执行INSERT语句,1,执行AFTERINSERT触发器语句,3,记录INSERT语句,2,DML触发器的应用-INSERT触发器,例：在XS数据库中创建一触发器，当向XSCJ表插入一记录时，检查该记录的学号在XSDA表是否存在，检查课程编号在KCXX表中是否存在，若有一项为否，则不允许插入。-创建触发器USEXSGOCREATETRIGGERxscjins_trigONXSCJFORINSERTASIFEXISTS(SELECT*FROMinsertedWHERE学号NOTIN(SELECT学号FROMXSDA)OR课程编号NOTIN(SELECT课程编号FROMKCXX),INSERT触发器,BEGINRAISERROR(违背数据的一致性.,16,1)ROLLBACKTRANSACTIONENDGO-验证触发器insertXSCJvalues(001101,105,89)go,DML触发器的应用-INSERT触发器,补充：RAISERROR(msg_str,severity,state)功能：返回用户定义的错误信息并设系统标志，记录发生错误，作为服务器错误信息返回给客户端。参数：msg_str：是一条特殊消息，最多可包含400个字符。Severity：用户定义的与消息关联的严重级别。用户可以使用从0到18之间的严重级别。19到25之间的严重级别只能由sysadmin固定服务器角色成员使用。State：从1到127的任意整数，表示有关错误调用状态的信息。state的默认为1。,INSERT触发器,例：在XS数据库中创建一触发器，当向XSCJ表插入一记录时，若该记录成绩在60分以上，XSDA中对应学号的记录的总学分增加该门课程的学分。-创建触发器CREATETRIGGERxscjins2_trigONxscjFORINSERTASUPDATEXSDASETXSDA.总学分=XSDA.总学分+KCXX.学分FROMXSDAJOINXSCJONXSDA.学号=XSCJ.学号JOINKCXXONXSCJ.课程编号=KCXX.课程编号WHEREXSDA.学号=(SELECT学号FROMINSERTED)AND(SELECT成绩FROMINSERTED)=60GO,INSERT触发器,主体也可以写为：UPDATEXSDASET总学分=总学分+(select学分FROMKCXXWHERE课程编号=(SELECT课程编号FROMINSERTED)WHERE学号=(SELECT学号FROMINSERTED)AND(SELECT成绩FROMINSERTED)=60-验证触发器INSERTINTOXSCJVALUES(200501,212,78),DELETE触发器,DELETE触发器,例：创建一个触发器，当在XSCJ表中删除一条记录时，XSDA中对应学生的总学分减去该课程的学分createtriggerxscj_t_delonxscjfordeleteasupdatexsdaset总学分=总学分-(select学分fromkcxxwhere课程编号=(select课程编号fromdeleted)where学号=(select学号fromdeleted)Go-验证deletefromxscjwhere学号=200010and课程编号=305,ifobject_id(sales.delCustomer,TR)ISNOTNULLDRopTRIGGERsales.delCustomerGOCREATETRIGGERdelCustomerONSales.CustomerAFTERDELETEASBEGINPrintdeletefromtable-SETNOCOUNTON-EXECmaster.xp_sendmail-recipients=NSalesManagersAdventure-W,-message=NCustomershavebeendeleted!END;,DELETE触发器,UPDATE触发器,执行UPDATE语句,执行AFTERUPDATE触发器语句,记录UPDATE语句,1,3,2,UPDATE触发器,例：在XS数据库中创建一触发器，当在XSDA中修改学号字段时，XSCJ中对应学号随之修改。-创建触发器CREATETRIGGERxsdaupd_trigONxsdaFORUPDATEASIFUPDATE(学号)UPDATEXSCJSETXSCJ.学号=(SELECT学号FROMINSERTED)WHEREXSCJ.学号=(SELECT学号FROMDELETED)GO,UPDATE触发器,-验证触发器UPDATEXSDASET学号=200500WHERE学号=200501,INSTEADOF触发器,执行UPDATE,INSERT,或者DELETE语句,执行INSTEADOF触发器语句,未执行语句,1,3,2,INSTEADOF触发器,例：INSTEADOF触发器的应用。-创建READERS表USEXSGOCREATETABLEREADERS(编号CHAR(6)PRIMARYKEY,姓名CHAR(8)NOTNULL,省份CHAR(4),城市CHAR(6)NOTNULL,电话号码CHAR(20)NOTNULL,E_MAILCHAR(40)GO,INSTEADOF触发器,-创建视图CREATEVIEWREADER_VASSELECT编号,姓名,电话号码FROMREADERSGO-创建触发器CREATETRIGGERREVINS_TRIGONREADER_V/*为视图创建触发器*/INSTEADOFINSERTAS,INSTEADOF触发器,DECLARENOCHAR(6),NAMECHAR(8),CITYCHAR(6),PHONECHAR(20)SETCITY=济南SETNO=(SELECT编号FROMINSERTED)SETNAME=(SELECT姓名FROMINSERTED)SETPHONE=(SELECT电话号码FROMINSERTED)INSERTREADERS(编号,姓名,城市,电话号码)VALUES(NO,NAME,CITY,PHONE)-验证INSERTREADER_VVALUES(000001,王小平,修改触发器,修改触发器语法格式与创建时类似，只需将关键字CREATE改为ALTER。DML触发器修改的主要语法格式如下：ALTERTRIGGERtrigger_nameONtable|viewFOR|AFTER|INSTEADOFINSERT,UPDATE,DELETEASIFUPDATE（列名1）AND|ORUPDATE（列名2）nSQL_stat