第7章事务、存储过程、触发器和游标ppt课件VIP

第7章事务、存储过程、触发器和游标,7.1事务7.2存储过程7.3触发器及其用途7.4游标,7.1事务,1、事务的概念事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。,2、事务的特性,原子性：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性：一事务的执行不能被其它事务干扰持续性（永久性）：指事务一旦提交，则其对数据库中数据的改变就应该是永久的,3、事务的分类,SQLServer的事务模式可分为显式事务、隐式事务和自动事务三种。1)显式事务显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括：BEGINTRANSACTION:标识一个事务的开始，即启动事务。COMMITTRANSACTION、COMMITWORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。ROLLBACKTRANSACTION、ROLLBACKWORK：标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。,2)隐式事务在隐式事务模式下，在当前事务提交或回滚后，SQLServer自动开始下一个事务。所以，隐式事务不需要使用BEGINTRANSACTION语句启动事务，而只需要用户使用ROLLBACKTRANSACTION、ROLLBACKWORK、COMMITTRANSACTION、COMMITWORK等语句提交或回滚事务。在提交或回滚后，SQLServer自动开始下一个事务。执行SETIMPLICIT_TRANSACTIONSON语句可使SQLServer进入隐式事务模式。在隐式事务模式下，当执行下面任意一个语句时，可使SQLServer重新启动一个事务：所有CREATE语句ALTERTABLE所有DROP语句TRUNCATETABLEGRANTREVOKEINSERTUPDATEDELETESELECTOPENFETCH需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONS连接选项即可。,3)自动事务模式在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQLServer的默认事务管理模式，当与SQLServer建立连接后，直接进入自动事务模式，直到使用BEGINTRANSACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS连接选项进入隐式事务模式为止。而当显式事务被提交或IMPLICIT_TRANSACTIONS被关闭后，SQLServer又进入自动事务管理模式。,示例：,BEGINTRANdemoSELECT*FROMStudentINSERTINTOStudentVALUES(9711112,张三,)SELECT*FROMStudentROLLBACK-回滚整个事务或：COMMIT-提交事务,下面例子说明自动事务模式下各语句的执行情况和SQLServer对批的处理：-SQLServer处于自动事务管理模式UsepubsGoCreatetableTB_transaction1(col1INTPRIMARYKEY,col2datetime)GoINSERTTB_transaction1VALUES(1,GETDATE()INSERTTB_transaction1VALUES(1,GETDATE()-违反约束Go,SELECTtimes=1,*fromTB_transaction1GoBegintran-进入显示事务模式INSERTTB_transaction1VALUES(2,GETDATE()SELECTtimes=2,*fromTB_transaction1INSERTTB_transaction1VALUE(3,GETDATE()-语法错误RollbackGoSELECTtimes=2,*fromTB_transaction1-此时，又重新进入自动事务模式Go,SETIMPLICIT_TRANSACTIONSON进入隐含事务模式goinsertTB_transaction1VALUES(3,GETDATE()insertTB_transaction1VALUES(4,GETDATE()rollbackgoselect*fromTB_transaction1deletefromTB_transaction1rollbackselect*fromTB_transaction1setimplicit_transactionsoffdeletefromTB_transaction1rollback-此时该命令不成功，因为已经进入自动事务模式go,4、并发问题与事务隔离,在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。SQLServer使用资源锁定的方法管理用户的并发操作。如果在用户并发访问期间没有锁定数据库资源，用户操作相同的数据时可能会产生一些意想不到的问题。这些问题包括：。丢失修改或被覆盖。读脏数据。不能重复读：一个事物多次访问同一行数据而每次所读取的数据是不同的。幻影读：是指一个事务多次读取一定范围内的数据行，而前后两次所读取的数据行是不同,为了避免产生并发访问问题，SQLServer使用不同类型的锁对资源进行锁定，从而限制在一个事务读取数据期间其他事务锁执行的操作类型，即对事务进行隔离。不同的并发访问问题可以通过设置不同的事务隔离级别加以解决。事务的隔离级别控制一个事务与其他事务的隔离程度，它决定该事务在读取数据时对资源所使用的锁类型。,SQL-92标准定义了以下4种隔离级别：未提交读：这是4种隔离级别中限制最低的级别，它仅能保证SQLServer不读取物理损坏的数据。在这种隔离级别下，不发出共享锁，也不接受排它锁，事务可以对数据执行未提交读或脏读；在事务结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据集消失。提交读：它要求在读取数据时控制共享锁以避免发生脏读，但数据可在事务结束前更改，这可能产生不能重复读或幻影读问题。可重复读：锁定查询中使用的所有数据以防止其他用户更新，但是其他用户可以将新的幻影行插入到数据集中，新插入的幻影行将出现在当前事物的后续读取结果集中。可重复读能够避免产生脏读和非重复读问题，但仍可能导致幻影读问题。可串行读：这是事务隔离的最高级别，它使事务之间完全隔离，所以将导致并发级别较低。在这种隔离级别下，SQLServer在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或向数据集内插入数据行，从而避免出现脏读、非重复读或幻影读等并发问题。,调用Transact-SQL中的SETTRANSACTIONINOLATIONLEVEL语句可以调整事务的隔离级别，以控制由该连接所发出的所有SELECT语句的默认事务锁定行为。该语句的语法格式为：SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED|READCOMMITTED|REPEATEDREAD|SERIALIZATION,5.事务处理语句,SQLServer中有关事务的处理语句有：,关于事务保存点,保存点提供了一种机制，用于回滚部分事务。在应用程序中，使用SAVETRANSACTIONsavepoint_name语句设置保存点，使用ROLLBACKTRANSACTIONsavepoint_name将事务回滚到保存点。,关于嵌套事务：说明：在定义一个事务时，BEGINTRANSACTION语句应与COMMITTRANSACTION语句或ROLLBACKTRANSACTION成对出现。在SQLServer中，显示事务定义语句可以嵌套.一个嵌套的事务是一系列子事务ti的集合,T=t1,t2,tn,这些子事务中的每一个又可以是拥有它自己的事务.T能够决定子事务ti的启动和终止,反过来,如果T中的一个子事务ti终止,它强制T终止;如拖ti提交,这一动作并不能使ti成为永久的,如果T终止(回滚),那么ti的提交将被撤消.也就是说，SQLServer忽略内部事务的提交，根据最外部事务结束时采取的操作，将提交或者回滚事务。如果提交外部事务，则内层嵌套的事务也会提交。如果回滚外部事务，则不论此前是否提交过内层事务，所有内层事务都将回滚。,但实际上只有最外层的BEGINTRANSACTION语句和COMMITTRANSACTION语句才能建立和提交事务；在回滚事务时，也只能使用最外层定义的事务名或存储点标记，而不能使用内层定义的事务名。事务嵌套常用在存储过程或触发器内，它们可以使用BEGINTRANSACTION。COMMITTRANSACTION对来相互调用。,6.事务处理实例,例1、UsepubsgoBEGINTRANSACTIONdemoSELECT*FROMdiscountsINSERTdiscountsVALUES(demo1,null,null,null,20.0)SAVETRANSACTIONsave_demoINSERTdiscountsVALUES(demo2,null,null,null,20.0)SELECT*FROMdiscountsROLLBACKTRANSACTIONsave_demo回滚部分事务SELECT*FROMdiscountsROLLBACKTRANSACTION回滚整个事务SELECT*FROMdiscounts,例2、事务嵌套usepubsgoBEGINTRANSACTIONdemoSELECT*FROMdiscountsINSERTdiscountsVALUES(demo1,null,null,null,20.0)beginTRANSACTIONdemo1INSERTdiscountsVALUES(demo2,null,null,null,20.0)SELECT*FROMdiscountsROLLBACKTRANSACTIONdemo1SELECT*FROMdiscountsROLLBACKTRANSACTION-出错，因为相应事务已经被回滚SELECT*FROMdiscounts,例3、USEpubsGOCREATETABLETB_transaction2(col1intIDENTITY,col2char(10)GOSETIMPLICIT_TRANSACTIONSONGOINSERTTB_transaction2VALUES(row1)INSERTTB_transaction2VALUES(row2)GoCOMMITTRANSACTIONPRINT第一个隐式事务所插入的数据:SELECT*FROMTB_transaction2GOINSERTTB_transaction2VALUES(row3)GO,PRINT第一、二个隐式事务所插入的数据：SELECT*FROMTB_transaction2GOROLLBACKTRANSACTIONGOSETIMPLICIT_TRANSACTIONOFFGOPRINT回滚第二个隐式事务后剩下的数据：SELECT*FROMTB_transaction2GO,7.2存储过程,7.2.1创建存储过程7.2.2执行存储过程7.2.3存储过程的返回值和状态信息7.2.4查看和修改存储过程7.2.5重命名和删除存储过程7.2.6系统存储过程,存储过程的概念,SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。存储过程是SQL语句和流程控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。在SQLServer中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。,7.2.1创建存储过程,在SQLServer中，可以使用三种方法创建存储过程：使用创建存储过程向导创建存储过程。利用SQLServer企业管理器创建存储过程。使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。,创建存储过程时，需要确定存储过程的三个组成部分：,所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其它存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。,1.使用创建存储过程向导创建存储过程,在企业管理器中，选择工具菜单中的向导选项，选择“向导”（如图8-1所示），则出现欢迎使用创建存储过程向导对话框，如图8-2所示。根据图8-2-图8-7提示可完成创建存储过程。,图8-1新建SQLServer组,图8-2选择创建存储过程向导,图8-3欢迎使用创建存储过程向导对话框,图8-4选择数据库对话框,图8-5选择数据库对象对话框,图8-6完成创建存储过程向导对话框,图8-7编辑存储过程属性对话框,图8-8编辑存储过程SQL对话框,2.使用SQLServer企业管理器创建存储过程,（）在SQLServer企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项，如图8-9所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项，如图8-10所示。均会出现创建存储过程对话框，如图8-11所示。（）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限”按钮，如图8-12所示。,图8-9选择新建存储过程对话框（1）,图8-10选择新建存储过程对话框（2）,图8-11新建存储过程对话框,图8-12设置权限对话框,3.使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程,创建存储过程前，应该考虑下列几个事项：不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。存储过程是数据库对象，其名称必须遵守标识符规则。只能在当前数据库中创建存储过程。一个存储过程的最大尺寸为128M。,用CREATEPROCEDURE创建存储过程的语法形式如下：,CREATEPROCEDURE存储过程名；版本号(参数名数据类型VARYING=defaultOUTPUT，)WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASSQL语句,1）：为新建立的存储过程名称，它必须遵守T-SQL标识符命名规则，且在一个数据库中，存储过程名必须唯一。2）；版本号:用来区分一组同名存储过程中的不同版本。同名存储过程只需调用一次DROPPROCEDURE即可全部删除。2）：存储过程的参数有两种类型：输入参数和输出参数.Cursor类型只能用做输出参数，使用该数据类型时，必须同时使用VARYING和OUTPUT。3）OUTPUT选项用来声明存储过程的返回参数，其值可以返回给调用它的EXECUTE语句，text、ntext和image类型参数可用做存储过程的返回参数。4）=default用来为存储过程参数设置默认值。5）：可以包含任意数量的T-SQL语句，它定义存储过程所执行的操作。定义的文本不能超过128MB。,6）WITHRECOMPILE选项要求SQLServer不要在缓存中保存存储过程的执行计划，而在每次执行时都重新对它进行编译。7）WITHENCRYPTION选项要求对存储在syscomments系统表中的存储过程定义文本进行加密。8）FORREPLICATION选项说明该存储过程只能在复制过程中执行，但这种类型的存储过程不能在订阅服务器上执行。不能与WITHRECOMPILE同时使用。,比如，我们创建一个最简单的存储过程:,CREATEPROCeduregetSTU_ISASSELECT*FROMStudentwheresdept=IS,CREATEPROCeduregetSTU(departmentvarchar(20)ASSELECT*FROMStudentwheresdept=department,若我们使用带参数的存储过程,则可用一个存储过程实现对不同系的学生数据的查询：,创建存储过程中的还可以含有流程控制等语句。存储过程可以嵌套，即在一个存储过程中可以调用另外一个存储过程。存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象等语句。这类语句如下：,.SETSHOWPLAN_TEXT.SETSHOWPLAN_ALLCREATETABLECREATEVIEWCREATEDEFAULTCREATERULECREATETRIGGERCREATEPROCEDURE,执行存储过程的语句：EXECute=|,7.2.2执行存储过程,例如、调用上面的过程，查询数学系学生的信息的语句为：EXECUTEgetSTUMA或者：EXECUTEgetSTUdepartment=MA或者：DECLAREdeptCHAR(20)SELECTdept=MAEXECUTEgetSTUdept,7.2.3存储过程的返回值和状态信息,无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。我们在创建存储过程时，也可以定义自己的状态码和错误信息。比如：,/*查询工资大于salary的员工信息*/CREATEPROCeduregetemp(salary)ASIFsalary=NULLRETURN13IFNOTEXISTS(SELECT*FROM职工WHERE工资salary)RETURN-103SELECT*FROM职工WHERE工资salary,其调用语句可以是：DECLAREreturn_statusintExecutereturn_status=getemp1200GoIfreturn_status=13print“必须提供一个数值作参数！”ElseIfreturn_status=-103print“没有满足条件的记录！”,以下存储过程用来查询学生的平均成绩：CREATEPROCEDUREGET_AVG(Snumberchar(8),savgtinyintOUTPUT)ASIFSnumber=NULLRETURN-1IFNOTEXISTS(SELECT*FROMSCWHEREsno=Snumber)return2Selectsavg=avg(grade)FromscWheresno=Snumber,其调用语句为：DECLARESnumchar(8),saveragetinying,re_statusintSELECTSnum=95002Executere_status=GET_AVGSnum,saverageOUTPUTIF.,7.2.4存储过程的自动执行,系统存储过程sp_procoption可以将存储过程设置为自动执行方式,使之在SQLServer每次启动时自动执行，其语法格式为：sp_procoptionprocName=procedure,optionName=option,optionValue=value其中：procedure为需要自动执行的存储过程名称。option和value参数分别为待设置的选项名称及其状态。Option唯一取值为startup,它设置存储过程的自动执行状态。Value的取值为true和false、或on和off。,注意：1）设置为自动执行的存储过程不要返回任何结果集合。因为它们由系统自动执行，无法处理其返回结果。2）SQLServer配置选项scanforstratup控制SQLServer每次启动时是否查找和执行系统中标识为自动执行的存储过程。只有打开该选项时，自动执行存储过程才能真正自动执行。,7.2.5查看和修改存储过程,存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。,使用企业管理器查看并修改用户创建的存储过程,在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码，同时还可以做修改，如下图所示。,用T-SQL修改存储过程,ALTERPROCEDURE存储过程名；版本号(参数名数据类型VARYING=defaultOUTPUT，)WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASSQL语句,7.2.6重命名和删除存储过程,1.重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：sp_rename原存储过程名称，新存储过程名称另外，通过企业管理器也可以修改存储过程的名称。,2.删除存储过程,删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：dropprocedureprocedure,n当然，利用企业管理器也可以很方便地删除存储过程。例如：dropproceduregetemp,示例1,A.使用带有复杂SELECT语句的存储过程：查询学生的考试成绩CREATEPROCEDUREstudent_grade1ASSELECTSname,Cname,GradeFROMStudentsINNERJOINscONs.sno=sc.snoINNERJOINcoursecONo=o执行：EXECstudent_grade1,示例2,B.使用带有参数的存储过程：查询某个学生某门课程的考试成绩CREATEPROCEDUREstudent_grade2student_namechar(10),course_namechar(20)ASSELECTSname,Cname,GradeFROMStudentsINNERJOINscONs.sno=sc.snoINNERJOINcoursecONo=oWHEREsname=student_nameANDcname=course_name,执行示例2存储过程,1.按参数位置传递值EXECstudent_grade2刘晨,数据库2.按参数名传递值EXECStudent_grade2student_name=刘晨,course_name=数据库,示例3,C.使用OUTPUT参数CreateProcedureProc1var1int,var2int,var3intoutputAsSetvar3=var1*var2执行Declaremint,m2int,m3intSetm1=5Setm2=7ExecuteProc1m1,m2,m3outputSelectm3,7.2.7系统存储过程,SQLServer提供了许多预定义的存储过程，这些存储过程存储在master数据库中（以_sp为前缀），提供了有效的查询系统表的方法，以及许多系统管理功能如：sp_help、sp_helpdatabases,1sp_helpdb,作用：报告有关指定数据库或所有数据库的信息。语法：sp_helpdbdbname=数据库名如果没有指定数据库名，则sp_helpdb报告master.dbo.sysdatabases中的所有数据库。例1.返回pubs数据库的信息execsp_helpdbpubs例2.返回有关所有数据库的信息execsp_helpdb,2.sp_databases,作用：列出驻留在SQLServer实例中的数据库。语法：sp_databases返回系统中的全部数据库名、数据库大小（以KB为单位）以及标记值（SQLServer的此项值为NULL）,3sp_tables,作用：返回当前环境下可查询对象的列表（任何可出现在FROM子句中的对象）。语法：sp_tablestable_type=“类型”其中：类型包括TABLE（用户表）、SYSTEMTABLE（系统表）和VIEW（视图）例1：返回在当前环境中可查询的对象的列表EXECsp_tables例2：返回当前数据库中的所有用户表EXECsp_tablestable_type=TABLE,4sp_helpconstraint,作用：返回某个表的所有约束语法：sp_helpconstraint表名示例：显示authors表的所有约束EXECsp_helpconstraintauthors,5sp_spaceused,作用：显示当前数据库或数据库中的某个表所使用的磁盘空间以及保留的磁盘空间。语法：sp_spaceused表名例1：察看当前数据库的空间使用情况EXECsp_spaceused例2：查看pubs数据库的titles表的空间信息EXECsp_spaceusedtitles,7.2.7用好存储过程,存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在SQLServer的系列版本中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQLServer提供支持。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。,存储过程的优点,当利用MSSQLServer创建一个应用程序时，Transaction-SQL是一种主要的编程语言。若运用Transaction-SQL来进行编程，有两种方法:其一是，在本地存储Transaction-SQL程序，并创建应用程序向SQLServer发送命令来对结果进行处理。其二是，可以把部分用Transaction-SQL编写的程序作为存储过程存储在SQLServer中，并创建应用程序来调用存储过程，对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。,优点:(1）存储过程允许标准组件式编程,存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。,（2）存储过程能够实现较快的执行速度,如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。,（3）存储过程能够减少网络流量,对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的Transaction-SQL语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL语句，从而大大增加了网络流量，降低网络负载。,（4）存储过程可被作为一种安全机制来充分利用,系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。,(5)自动完成需要预先执行的任务.,存储过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务注意：存储过程虽然既有参数又有返回值，但是它与函数不同。存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字（如何执行存储过程见本章下一字）。,练习：1.什么是存储过程？为什么要使用存储过程？2.试为如下问题编写、建立存储过程，并定义合适的过程返回状态码和信息1）插入一个新的仓库，其信息由参数确定。2）给指定城市的所有仓库增加xm2面积。3）检索某职工的工资信息。4）检索在某城市工作的仓库职工号。,7.3触发器及其用途,7.3.1基本概念7.3.2触发器的种类7.3.3创建触发器7.3.4触发器的原理7.3.5管理触发器,7.3.1基本概念,触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。存储过程和触发器同是提高数据库服务器性能的有力工具。,触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。,触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其他许多不同的功能。,触发器的主要作用:（1）强化约束（EnforceRestriction）触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的更新和变化。,（2）级联运行（CascadedOperation）触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器包含对另外一个表的数据操作（如删除、更新、插入），而该操作又导致该表上的触发器被触发。,（3）存储过程的调用(StoredProcedureInvocation)为了响应数据库更新，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS之外进行操作。,由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的操作。此外一个表的同一类型（INSERT、UPDATE、DELETE）的多个触发器能够对同一种数据操作采取多种不同的操作。,1触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。,2触发器可以禁止或撤消违反参照完整性的修改。,3触发器可以强制比用CHECK约束定义更加复杂的限制。,触发器也是一个数据库对象。一个触发器和三部分内容有关：激活触发器的表、激活触发器的数据修改语句和触发器要采取的动作。,触发器可以用于数据参照完整性和以下一些场合：,7.3.2触发器的种类,SQLServer2000支持两种类型的触发器：AFTER触发器和INSTEADOF触发器。1、AFTER触发器即为SQLServer2000版本以前所介绍的触发器。该类型触发器要求只有执行完某一操作（INSERT、UPDATE、DELETE），并处理过所有约束后，触发器才被触发，且只能在表上定义。如果操作违反约束条件，将导致事务回滚，这时就不会执行后触发器。可以为针对表的同一操作定义多个触发器。AFTER触发器可以指定哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程sp_settriggerorder来完成此任务。,2、INSTEADOF触发器该类触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。既可在表上定义INSTEADOF触发器，也可以在视图上定义INSTEADOF触发器，但对同一操作只能定义一个INSTEADOF触发器。,7.3.3创建触发器,SQLServer中可以用SQLServer的企业管理器和Transaction-SQL来创建触发器。在创建触发器以前必须考虑到以下几个方面：（1）CREATETRIGGER语句必须是批处理的第一个语句。（2）触发器是数据库对象，所以其命名必须符合命名规则。（3）虽然触发器可以参照视图或临时表，但不能在视图或临时表上创建触发器(Insteadof)，只能在基表或在创建视图的表上创建触发器。（4）一个触发器只能对应一个表，这是由触发器的机制决定的。,（5）在触发器定义中，所有建立和更改数据库以及数据库对象的语句、所有的drop语句都不允许在触发器中使用。（6）在触发器定义中，可使用IFUPDATE子句来测试INSERT、UPDATE语句中是否对指定字段有影响。如果将一个值赋给指定字段或更改了指定的字段，则这个子句为真。（7）通常不要在触发器中返回任何结果，因此不要在触发器定义中使用select语句或变量赋值语句。创建一个触发器时，必须指定触发器的名字、在哪一个表上定义触发器、激活触发器的修改语句，如INSERT、DELETE、UPDATE，当然两个或三个不同的修改语句也可以触发同一个触发器，如INSERT和UPDATE语句能激活同一个触发器。,1用CREATETRIGGER命令创建触发器可用CREATETRIGGER命令创建触发器，其语法规则如下CREATETRIGGERtrigger_nameONtable|viewFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEwithappendASSQL语句,从以上语句可以看出，一个表最多可以有三类触发器：插入（INSERT）触发器、更新（UPDATE）触发器、删除（DELETE）触发器。一个触发器只能应用到一个表上，但一个触发器可以包含很多动作，可以执行很多功能。,（1）trigger_name是用户要创建的触发器的名字。触发器的名字必须符合SQLServer的命名规则，且其名字在当前数据库中必须是惟的。（2）tablel|view是与用户创建的触发器相关联的表的名字或视图的名称，并且此表或视图必须已经存在。（3）DELETE,INSERT,UPDATE关键字用来指明哪种数据操作将激活触发器。至少要指明其中的一个选项，在触发器的定义中，三者的顺序不受限制，各选项要用逗号隔开。（4）WITHAPPEND表明增加另外一个已存在的触发器。只有在兼容性水平（指某一数据库行为与以前版本的SQLServer兼容程度）不大于65时才使用该选项。（5）AS是触发器将要执行的动作。（6）sql语句是包含在触发器中的条件语句或处理语句。,例创建一个触发器，当向s表中插入一条记录时，自动显示s表中的记录。CREATETRIGGERChange_DisplayOnsFORINSERT,UPDATE,DELETEASSELECT*FROMstudent,该触发器建立完毕后，当执行如下操作时，将会显示s数据表中的全部记录。EXECUTEInsertRecordDefasno=S11，sn=张建峰，age=17,sex=男,2用EnterpriseManger创建触发器的步骤如下：,（1）启动EnterpriseManager,登录到要使用的服务器。（2）在EnterpriseManager的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹，此时在右窗格中显示该数据库的所有表。,（3）在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，此时会出现“触发器属性”对话框。（4）在“名称”下拉框中选择“”，“文本”编辑框中输入触发器的文本命令。（5）单击“检查语法”按钮，检查语句是否正确。（6）单击“应用”按钮，在“名称”下拉列框中会显示新创建的触发器名字。,属性对话框,7.3.4触发器的原理,从以上的介绍中我们可以看出触发器具有强大的功能，那么SQLServer是如何用触发器来完成这些任务呢？下面我们将对其工作原理及实现做详细介绍。每个触发器有两个特殊的表：插入表和删除表，分别为inserted和deleted。有以下几个特点：1）这两个表是逻辑表，并且这两个表是由系统管理的，存储在内存中因此不允许用户直接对其修改。2）这两个表的结构总是与被该触发器作用的表有相同的表结构。,3）这两个表是动态驻留在内存中的，当触发器工作完成，这两个表也被删除。这两个表主要保存因用户操作而被影响到的原数据值或新数据值。4）另外，这两个表是只读的，且只在触发器内部可读。例如在触发器内可用如下语句查看DELETED表中的信息：select*fromdeleted,1插入表的功能（inserted）,对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入（INSERT）操作，那么对该表插入的所有行来说，都有一个相应的副本级存放到插入表（inserted）中，即插入表就是用来存储原插入的内容。,2删除表的功能(deleted),对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除（DELETE）操作，则将所有的被删除的行存放至删除表（deleted）中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表中得以还原。,需要强调的是，更新（UPDATE）操作包括两个部分，即先将旧的内容删除，然后将新值插入。因此，对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了旧值，然后在插入表中存放新值。,由于触发器仅当被定义的操作被执行时才被激活，即触发器仅在执行插入、删除和更新操作时才被执行。每条SQL语句仅能激活触发器一次，可能存在一条语句影响多条记录的情况。在这种情况下可以使用系统变量rowcount的值，该变量存储了最新的一条SQL语句执行后所影响的记录数。,3插入视图和删除视图,当在定义了触发器的表上发生修改操作时会自动派生出两个视图，一个是插入视图，一个是删除视图。当在表上发生插入操作时，新插入的行将出现在inserted表中形成插入视图；当在表上发生删除操作时，被删除的旧行将出现deleted表中，形成删除视图。而更新的实现过程是先删除旧行，然后再插入新行。,7.3.5管理触发器,如果要显示作用于表上的触发器究竟对表有哪些操作，必须查看触发器信息。在SQLServer中，有多种方法查看触发器信息。接下来，我们将介绍两种常用的方法，即通过SQLServer的管理工具EnterpriseManager以及系统存储过程sp_help、sp_helptext和sp_depends来查看触发器信息。,1使用EnterpriseManager显示触发器信息使用EnterpriseManager显示触发器信息，步骤如下。,（1）启动EnterpriseManager,登录到要使用的服务器。（2）在EnterpriseManager的左窗格中，展开要创建触发器的数据库文件夹，单击“表”文件夹，此时在右窗格中显示该数据库的所有表。（3）在右窗格中，右击要创建触发器的数据表，在弹出的快捷菜单中，将鼠标指向“所有任务”，在出现的下一级子菜单中选择“管理触发器”菜单项，出现“触发器属性”对话框。（4）在“名称”下拉列表中选择所要查看的触发器的名称，在“文本”编辑框中显示出该触发器的文本命令。,（1）sp_help通过该系统过程，可以了解触发器的一般信息，如触发器的名字、属性、类型、创建时间。使用sp_help系统过程的命令格式为：sp_help触发器名字,例查看我们已经建立的change_display触发器。sp_helpchange_display,2使用系统存储过程查看触发器系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。下面我们将分别对其进行介绍。,(2)sp_helptext通过sp_helptext能够查看触发器的正文信息，其语法格式为：sp_helptext触发器名,例查看我们已经建立的chang_display触发器的命令文本。sp_helptextchange_display,(3)sp_depends通过sp_depends能够查看指定触发器所引用的表或指定的表所涉及到的所有触发器。其语法形式如下：sp_depends触发器名字sp_depends表名,例6.13查看我们已经建立的change-display触发器所涉及的表。sp_dependschange_display,注意：用户必须在当前数据库中查看触发器的信息，而且被查看的触发器必须已经被创建。,使用sp_rename命令修改触发器的名字。sp_rename命令的语法格式为：sp_renameoldname,newnameoldname为触发器原来的名称，newname为触发器的新名称。,通过EnterpriseManager修改触发器正文和删除触发器。其步骤与查看触发器信息一样。修改完触发器后要使用“检查语法”选项对语句进行检查。,（1）修改触发器通过EnterpriseManagerAlerttrigger命令和sp_rename命令，可以修改触发器的名字和正文。,3.修改、删除触发器,SQL语句其中各参数或保留字的含义参看“创建触发器”一节。,通过Alerttrigger命令修改触发器正文。Alerttrigger命令的语法格式为：ALTERTRIGGERtrigger_nameON(table|view)FOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEAS,（2）删除触发器,可以使用企业管理器和T-SQL语句删除触发器。语句格式为：Droptriggertrigger_name,4.使用触发器（1）删除类触发器删除类触发器就是当表上发生删除操作时所触发执行的程序。我们用一个级联删除的例子来说明删除类触发器。我们对学生表的删除操作定义一个触发器，使得当删除学生记录时，也同时删除该学生的所有选课记录。定义删除触发器如下：CREATETRIGGERS_del_triggerONstudentFORDELETEASDELETEscFROMsc,deletedWHEREsc.sno=deleted.sno,如果加上状态判断，则改为：CREATETRIGGERSTU_del_tri