数据库技术及应用原理SQL ServerVB NET 龚沛曾 23345-005

1,本章主要内容:T-SQL的基本语法存储过程(难点)触发器(难点),第5章Transact-SQL、存储过程和触发器,2,5.1Transact-SQL语言,5.1.1数据类型,数据类型见表5-1。下面做一些必要的说明。1.整型(Bigint、Int、Smallint、tinyint)整型数参与任何算术运算的结果只保留数值的整数部分。DECLAREn1int,n2int,n3int-变量的声明SETn1=14SETn2=3SETn3=n1/n2-n3=4SELECTn1ASn1,n2ASn2,n3ASn3-起别名,3,5.1.1数据类型,2.浮点型(float、real、Decimal、Numerc)float和real属于近似数据类型。特点是表示范围大,但任何一个数都仅是一个近似值。适合用于科学计算。Decimal和Numerc是精确数值类型，表示的是一个精确值。适合用于财务金融（如：工资）。,5.1.1数据类型,4,5.1.1数据类型,Decimal和Numeric的表示范围和精度及所占内存都一样，为与ANSI标准兼容SQLServer支持这两种表示。Decimal(p,s):p表示总位数(不包括小数点)，介于138之间，默认值为18。s表示小数点后的位数，默认值是0。参数间关系：0、=、,5.1.2标识符、变量和运算符,(3)字符串连接运算符：。例如：printasd+dfg+1234结果为：asddfg1234空串()作为单个空格处理。例如：abc+efg，结果为abcegf.,5.1.2标识符、变量和运算符,22,5.1.2标识符、变量和运算符,(4)逻辑运算符（见表5-6）,5.1.2标识符、变量和运算符,23,5.1.2标识符、变量和运算符,表5-6(续),5.1.2标识符、变量和运算符,24,(5)运算符优先级括号算术运算符(字符串连接符)比较运算符逻辑运算符赋值（由高到低）,5.1.2标识符、变量和运算符,5.1.2标识符、变量和运算符,25,SQLServer提供了非常丰富的函数供用户使用，同时也允许用户定义自己的函数。,5.1.3函数,5.1.3函数,26,1.Transact-SQL提供的系统函数有：,5.1.3函数,5.1.3函数,27,函数分为三大类：行集函数（也称表值函数）可以用在SQL语句中“表”出现的任何地方引用行集函数。聚合函数(第4章介绍过)：也称计合函数。,5.1.3函数,标量值函数：最常用的一类函数。通过有参或无参调用它，可以使其执行并返回单个值。,5.1.3函数,28,5.1.3函数,系统提供的函数，参考教材。不再例举。,5.1.3函数,29,5.1.3函数,2.用户自定义函数用户可以根据应用需要定义自己的函数。自定义函数分三种：标量函数、内嵌表值函数、多语句表值函数。下面我们介绍前2种。(1)标量函数标量函数指函数返回单个值(字符串数值等).语法格式：见下页。,5.1.3函数,30,5.1.3函数,CREATEFUNCTION函数名（形式参数定义表）RETURNS函数返回值数据类型ASBEGIN函数体RETURN返回值表达式END,5.1.3函数,31,5.1.3函数,例3创建一个标量函数，返回某学生的平均分数。学号作为函数参数。,5.1.3函数,CREATEFUNCTIONget_avg(snochar(6)RETURNSintASBEGINDECLAREtempintSELECTtemp=AVG(成绩)FROM选课WHERE学号=snoRETURNtempEND,32,5.1.3函数,可以用下列语句调用get_avg函数：SELECTdbo.get_avg(030101)AS030101平均成绩注意：调用时必须给出用户名dbo。它是函数创建者。,(2)内嵌表值函数内嵌表值函数指函数返回值是一个表.语法格式：见下页。,5.1.3函数,33,5.1.3函数,CREATEFUNCTION函数名（形式参数定义表）RETURNSTABLEASRETURN(SELECT查询语句),函数返回值是一个查询表。,5.1.3函数,34,5.1.3函数,例4创建一个表值函数，返回平均分数大于或等于指定分数的学生学号和平均分数。函数参数为给定的一个“分数”。,5.1.3函数,CREATEFUNCTIONget_all_avg(scoreint)RETURNSTABLEASRETURNSELECT学号,AVG(成绩)AS平均成绩FROM选课GROUPBY学号HAVINGAVG(成绩)=score,注意：表达式AVG(成绩)必须给出别名。,35,5.1.3函数,函数的调用：查询平均成绩大于80分的学生信息。SELECT学生.学号,姓名,平均成绩FROMget_all_avg(80),学生WHEREget_all_avg.学号=学生.学号,5.1.3函数,36,5.1.3函数,(3)删除用户自定义函数DROPFUNCTION函数名(4)修改用户自定义函数ALTERFUNCTION函数名参数定义与代码,5.1.3函数,37,流程控制语句用于控制SQL语句、语句块、存储过程或触发器的执行流程。主要的流程控制语句有：见下页表。,5.1.4流程控制语句,5.1.4流程控制语句,38,5.1.4流程控制语句,5.1.4流程控制语句,39,其他Transact-SQL语句还有：(1)/*/:注释语句,用于多行注释(2)-(注释语句):用于单行或嵌套注释(3)DECLARE：变量声明语句。(4)EXECUTE：存储过程执行语句。(5)PRINT:终端输出语句,5.1.4流程控制语句,5.1.4流程控制语句,40,1.BEGINEND语句该语句将多条SQL语句封装在一起，构成一个语句块。主要语句块就可以在IF/ELSE、WHILE等语句中作为一个整体来执行。语法格式：,5.1.4流程控制语句,BEGIN若干SQL语句END,5.1.4流程控制语句,41,5.1.4流程控制语句,2.IFELSE语句语法格式：,IF条件语句|语句块1ELSE语句|语句块2,5.1.4流程控制语句,42,5.1.4流程控制语句,例5查询选修0001课的学生成绩，如有大于90分以上的，则将其姓名显示出来；若无人大于90分，则显示“成绩优秀者为0个”。,IFEXISTS(SELECT*FROM选课WHERE课号=0001AND成绩=90)SELECT姓名FROM学生JOIN选课ON学生.学号=选课.学号WHERE课号=0001AND成绩=90ELSEPRINT成绩优秀者为0个,5.1.4流程控制语句,43,3.WHILE语句语法格式：,5.1.4流程控制语句,5.1.4流程控制语句,44,5.1.4流程控制语句,例6引用已建函数get_all_avg，分别求出平均成绩大于60、70、80、90的学生成绩信息。,5.1.4流程控制语句,45,5.1.4流程控制语句,DECLAREjintSETj=60WHILEj100BEGINSELECT*,jas平均成绩大于FROMget_all_avg(j)SETj=j+10END,5.1.4流程控制语句,46,*4.WAITFOR语句WAITFOR语句可以指定在某一时间点或时间间隔后执行SQL语句、语句块、存储过程或事务。,5.1.4流程控制语句,语法格式：WAITFORDELAYtime|TIMEtime,time格式为：hh:mm:ss。,5.1.4流程控制语句,47,例7对学生选课关系(1)设置在9:00执行一次查询操作；(2)再设置在2小时以后再执行一次查询操作，查看学生选课情况。,5.1.4流程控制语句,BEGINWAITFORTIME9:00-等到9点SELECT学号,课号FROM选课ENDGOBEGINWAITFORDELAY2:00-延迟2小时SELECT学号,课号FROM选课END,5.1.4流程控制语句,48,5.RETURN语句RETURN无条件退出语句。可在任何时候用于从过程、批处理或语句块中退出。不执行位于RETURN之后的语句。,5.1.4流程控制语句,格式：RETURN整数表达式,RETURN语句可以返回整数值。一般用于表示存储过程或应用程序的执行状态。如，所有系统存储过程返回0值表示成功，返回非零值则表示失败。注意：当用于存储过程时，RETURN不能返回空值。,5.1.4流程控制语句,49,6.CASE表达式CASE表达式用于多分支结构，有两种语法格式。(1)简单CASE表达式,5.1.4流程控制语句,CASE表达式WHEN表达式1THEN结果表达式1WHEN表达式nTHEN结果表达式nELSE结果表达式n+1END,5.1.4流程控制语句,50,说明：可以是常量、属性名、函数、子查询和算术运算符、字符串运算符等组合的有意义的式子。是在多个中选择一个表达式，它的值作为“CASE表达式”的结果值。当所有匹配都不成立，CASE则返回NULL。可以只有一个WHEN子句。是一个表达式，可以用在可以出现的任何地方。,5.1.4流程控制语句,5.1.4流程控制语句,51,5.1.4流程控制语句,5.1.4流程控制语句,52,例8将百分制分数按优、良、中、及格和不及格五个等级记分制的形式输出。,5.1.4流程控制语句,SELECT学号,课号,成绩=CASE成绩/10WHEN6THEN及格WHEN7THEN中WHEN8THEN良WHEN9THEN优WHEN10THEN优ELSE不及格ENDFROM选课,说明：整数除整数得到整数（系统自动取整）验证：Select10/2,5.1.4流程控制语句,53,(2)搜索型CASE表达式,5.1.4流程控制语句,CASEWHEN条件表达式1THEN结果表达式1WHEN条件表达式nTHEN结果表达式nELSE结果表达式n+1END,功能：得到一个满足条件的值。如下图所示。,5.1.4流程控制语句,54,5.1.4流程控制语句,5.1.4流程控制语句,55,例9将百分制分数按优、良、中、及格和不及格五个等级记分制的形式输出。,5.1.4流程控制语句,SELECT学号,课号,成绩=CASEWHEN成绩/10=6THEN及格WHEN成绩/10=7THEN中WHEN成绩/10=8THEN良WHEN成绩/10=9THEN优WHEN成绩/10=10THEN优WHEN成绩/10ISNULLTHEN无成绩ELSE不及格ENDFROM选课,5.1.4流程控制语句,56,7.批处理和脚本(1)批处理批处理是作为一个组一起提交并执行的若干Transact-SQL语句。批处理可以交互地运行，或作为脚本的一部分运行。一个脚本可以包含多个Transact-SQL批处理。,5.1.4流程控制语句,5.1.4流程控制语句,57,使用GO语句定义一个批处理。使用GO语句表示一个批处理的结束。GO不是通用的Transact-SQL语句，是只有SQLServer查询分析器和osql实用程序接受的语句。,5.1.4流程控制语句,5.1.4流程控制语句,58,SQLServer如何处理批处理SQLServer统一优化、编译和执行一个批处理中的语句，但是，这些语句并不构成一个事务，不必作为一个可恢复单元执行。用户定义的变量作用域限制在一个批处理，所以变量不能在GO语句后引用。,5.1.4流程控制语句,5.1.4流程控制语句,59,例如：USE教学数据库GOCREATEVIEW成绩_V1(学号,平均成绩)ASSELECT学号,AVG(成绩)FROM选课GROUPBY学号GOSELECT*FROM成绩_V1GO,5.1.4流程控制语句,5.1.4流程控制语句,60,批处理规则CREATEPROCEDURE、CREATEVIEW、CREATETRIGGER、CREATERULE、CREATEDEFAULT这些语句不能在一个批处理中联合使用，并且也不能与其他语句联合使用。即在一个批处理中，只能有一个CREATE语句，其他语句都只能视为该语句的的一部分。,5.1.4流程控制语句,5.1.4流程控制语句,61,不能在一个批中修改表结构，然后在同一个批中引用刚修改的列。如果EXECUTE语句是批处理的第1句，则可以省略，否则执行任何不是批处理中第1句的存储过程都必须包含EXECUTE关键字。一个批处理中用到的变量必须在其中定义，不能引用其他批处理中定义的变量。,5.1.4流程控制语句,5.1.4流程控制语句,62,(2)脚本脚本是作为一个文件保存的一个或多个Transact-SQL语句。脚步可以在SQLServer分析器或任何文本编辑器中编写和保存，以.sql为扩展名。脚本必须在SQLServer分析器中执行。脚本主要用于：重新创建数据库或数据对象，或重复执行一些语句。,5.1.4流程控制语句,5.1.4流程控制语句,63,5.2.1存储过程的概念,1.存储过程的定义,存储过程是存放在服务器上的预先编译好的一组完成特定功能的Transact-SQL语句集。,5.2存储过程,5.2.1存储过程的概念,64,5.2.1存储过程的概念,2.存储过程的特点使用存储过程可以减少网络数据流量。,用户不是在网络上发送几百条SQL语句，而是通过发送一条调用过程语句来执行一个复杂的操作，这样减少了服务器和客户机之间传递的请求数。,5.2.1存储过程的概念,增强代码的重用性和共享性。存储过程可以被多个应用程序多次调用，实行代码的重用性和共享性。,65,5.2.1存储过程的概念,使用存储过程可以加快系统的运行速度。存储过程第一次执行后，执行计划就被存放在高速缓存中。以后执行时，不必再执行优化处理，从而加快了系统运行速度。,5.2.1存储过程的概念,存储过程提高了数据的安全性。系统管理员可以只授权用户访问存储过程的权限，而不授予用户访问存储过程中涉及的到的表的权限。从而有效地保护了数据表中存储过程涉及不到的数据的安全性。,66,5.2.1存储过程的概念,3.存储过程的分类系统存储过程系统存储过程(由前缀sp_标识)存放在master数据库中。可以作为命令直接执行。其中大部分存储过程都可以在任何用户数据库中执行。用户存储过程：用户自定义的存储过程。,5.2.1存储过程的概念,67,5.2.1存储过程的概念,扩展存储过程扩展存储过程(主要由前缀xp标识)是指在SQLServer环境外部执行的DLL。但是，它们可以被加载到SQLServer系统中，并按照存储过程方式执行。远程存储过程远程存储过程是指从远程服务器上调用的存储过程。,5.2.1存储过程的概念,68,5.2.2存储过程创建和执行,1.创建存储过程语句,CREATEPROCEDURE存储过程名;num参数1数据类型=默认值OUTPUT,其他参数WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASBEGINSQL语句系列END,5.2.2存储过程创建和执行,69,5.2.2存储过程创建和执行,说明;num：可选的整数。用来对同名若干过程编号，如proc_s;1,proc_s;2,proc_s;3等。采用相同名字加编号区别若干过程的优点是删除时方便，如，DROPPROCEDUREproc_s一条命令把3个过程都删了。,5.2.2存储过程创建和执行,70,5.2.2存储过程创建和执行,default：参数的默认值。如果定义了默认值，不必指定该参数的值就可调用。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符(%、_、)。OUTPUT：表示该参数是返回参数。参数可将信息返回给调用过程。Text、ntext、Image参数可用作OUTPUT参数。,5.2.2存储过程创建和执行,71,5.2.2存储过程创建和执行,RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONRECOMPILE：表明该过程在运行时重新编译。ENCRYPTION：表示系统加密syscomments表中包含CREATEPROCEDURE语句文本的条目。加密后即使是过程创建者本人也无法查看过程定义文本。,5.2.2存储过程创建和执行,72,5.2.2存储过程创建和执行,例如：USE教学数据库GOCREATEPROCc_sASSELECT*FROM学生WHERE所属系LIKE计算机%GO,5.2.2存储过程创建和执行,73,5.2.2存储过程创建和执行,2.执行存储过程语句,EXECUTEreturn_status=存储过程名;number|存储过程名变量参数1=值1,参数n=值n|变量OUTPUT|DEFAULTWITHRECOMPILE,5.2.2存储过程创建和执行,74,5.2.2存储过程创建和执行,参数说明:return_status,为一整型变量,用于保存存储过程的返回状态值。存储过程名变量：表示存储过程名称的变量。,5.2.2存储过程创建和执行,变量outputdefault:用来保存过程中的OUTPUT参数的返回值。DEFAULT提供参数的默认值,75,5.2.2存储过程创建和执行,3.创建无参数的存储过程例10创建一个存储过程。完成显示所有学生的平均成绩。,CREATEPROCSC_proc;1ASSELECT学号,AVG(成绩)as平均成绩FROM选课GROUPBY学号,5.2.2存储过程创建和执行,76,5.2.2存储过程创建和执行,4.创建带有输入参数的存储过程例11创建一个存储过程。显示指定学号的学生的平均成绩。学号作为参数.,CREATEPROCSC_proc;2snochar(6)ASSELECT学号,AVG(成绩)as平均成绩FROM选课WHERE学号LIKEsnoGROUPBY学号,5.2.2存储过程创建和执行,77,5.2.2存储过程创建和执行,调用存储过程sc_proc;2方法1:declaretemp1char(6)settemp1=030101exec教学数据库.dbo.sc_proc;2temp1方法2:execsc_proc;2030101,5.2.2存储过程创建和执行,78,5.2.2存储过程创建和执行,5.创建带有输出参数的存储过程例12创建存储过程。完成求指定学号的学生的平均成绩，并将该成绩保存在变量avg中。,CREATEPROCSC_proc;3snochar(6),avgintOUTPUTASSELECTavg=AVG(成绩)FROM选课WHERE学号=snoGROUPBY学号,5.2.2存储过程创建和执行,79,5.2.2存储过程创建和执行,执行存储过程SC_proc;3:DECLAREtempCHAR(6),avg_outINTSETtemp=030101EXECSC_proc;3temp,avg_outOUTPUTPRINT030101的平均成绩为:+CAST(avg_outASCHAR(3),5.2.2存储过程创建和执行,80,5.2.2存储过程创建和执行,6.返回存储过程的执行状态每个存储过程的执行,都将自动返回一个整数状态值,用于告诉用户调用存储过程的执行情况。调用程序可以根据返回值作相应的处理。用户可以用大于0或-1-99的来定义自己的返回状态值，以表示不同的执行结果。一般而言，0表示存储过程执行成功，-1-99之间的数表示过程执行失败。,5.2.2存储过程创建和执行,81,5.2.2存储过程创建和执行,例13检查给定学号的学生有无不及格的记录，有则返回5,无则返回0并输出该学生的学习记录。如果没有提供参数学号，则返回15.,CREATEPROCSC_PROC;4snoCHAR(6)=NULLASIFsnoISNULLRETURN15/*未给出该生学号*/,创建返回执行状态的存储过程,5.2.2存储过程创建和执行,82,5.2.2存储过程创建和执行,ELSEIFEXISTS(SELECT*FROM选课WHERE学号=snoAND成绩60)RETURN5/*该生有不及格成绩时*/ELSEBEGINSELECT*FROM选课WHERE学号=snoRETURN0/*该生无不及格，返回其选课信息*/END,ELSEIFEXISTS(SELECT*FROM选课WHERE学号=snoAND成绩60)RETURN5/*该生有不及格成绩时*/ELSEBEGINSELECT*FROM选课WHERE学号=snoRETURN0/*该生无不及格，返回其选课信息*/END,ELSEIFEXISTS(SELECT*FROM选课WHERE学号=snoAND成绩60)RETURN5/*该生有不及格成绩时*/ELSEBEGINSELECT*FROM选课WHERE学号=snoRETURN0/*该生无不及格，返回其选课信息*/END,5.2.2存储过程创建和执行,83,5.2.2存储过程创建和执行,执行：（学号取030101或060111，各执行一次；学号未给出，再执行一次）DECLAREreturn_statusINTEXECreturn_status=sc_proc;4030101IFreturn_status=15PRINT缺少输入参数-学号ELSEIFreturn_status=5PRINT该生有不及格成绩记录!ElsePRINT该生无不及格成绩!,5.2.2存储过程创建和执行,84,5.2.3存储过程的处理,存储过程的处理,5.2.3存储过程的处理,85,5.2.3存储过程的处理,存储过程的初次处理包括：创建存储过程和预编译。创建存储过程：存储过程被创建时，查询处理器对该过程进行语法分析，检查是否合乎语法规范，通过语法检查后将该存储过程的源代码存放到当前数据库的系统表syscomments中，同时在sysobjects中登记该存储过程的名字。注：绝不要直接删除系统表syscomments中的项。,5.2.3存储过程的处理,86,5.2.3存储过程的处理,2.预编译(或再编译)存储过程第1次执行时，SQLServer查询优化器分析存储过程中的SQL语句，并创建含有快速访问数据的计划。在查询优化器把编译的计划放在存储过程高速缓存中后，系统执行存储过程。,5.2.3存储过程的处理,87,5.2.3存储过程的处理,重编译在某些应用中,数据库的逻辑结构可能发生改变（如属性列被修改）或新增了索引等，这样存储过程的查询计划就可能不是最优的，有必要重新编译，以便获得新的优化的查询计划。重编译有三种方法。(1)在创建存储过程时采用RECOMPILECREATEPROCWITHRECOMPILE这样，每次执行时，系统都会重新编译优化。,5.2.3存储过程的处理,88,5.2.3存储过程的处理,(2)在执行存储过程时采用RECOMPILEEXEC存储过程名参数表WITHRECOMPILE在执行存储过程时，重新编译优化。(3)调用系统存储过程sp_recompile命令:sp_recompile表名运行该命令的结果是指定表的存储过程在下一次运行时将被重新编译.,5.2.3存储过程的处理,89,5.2.4存储过程的查看、修改和删除,1.查看存储过程方法1：可以通过系统存储过程来查看用户定义的存储过程。,Sp_helptext存储过程名,5.2.4查看、修改和删除存储过程,90,5.2.4存储过程的查看、修改和删除,方法2：查询分析器中，找到所要查看的存储过程，单击鼠标右键，打开快捷菜单，选择“编辑”菜单命令。,5.2.4查看、修改和删除存储过程,91,5.2.4存储过程的查看、修改和删除,方法3：如果想查看存储过程的一般信息，可使用：Sp_help存储过程名,5.2.4查看、修改和删除存储过程,92,5.2.4存储过程的查看、修改和删除,2.修改存储过程（建议可视化操作完成）,在查询分析器中使用下列命令：ALTERPROCOWNER.存储过程名,可视化操作：选定存储过程，在其上打开快捷菜单，选择“编辑”菜单命令，打开编辑窗口。修改完成后，再重新执行一次即可。,或,5.2.4查看、修改和删除存储过程,93,*5.2.5系统存储过程,常用的系统存储过程如下表,5.2.5系统存储过程,94,*5.2.5系统存储过程,5.2.5系统存储过程,95,*5.2.5系统存储过程,例：查看与选课表相关的所有对象。sp_depends选课,5.2.5系统存储过程,96,5.3触发器,SQLServer提供了两种主要机制来强制实施数据库的完整性：约束和触发器。触发器可以实现比check约束更为复杂的完整性约束。,5.3触发器,5.3触发器,97,触发器：是一种特殊的存储过程，在用户试图更新触发器保护的数据时自动执行。触发器:(1)总是与一个表相连。触发器是在单个表或视图上定义，这个表称为触发器表。每个表上可以建若干触发器。(2)是自动激活的。当对一个表中数据执行插入、删除、修改操作时，如果对该表上这个操作定义了触发器，则该触发器自动执行，这是不可阻挡的。,5.3.1什么是触发器,5.3触发器,98,(3).不能直接调用与标准的存储过程不同，触发器不能直接调用，也不能传递或接受参数。(4).是一个事务触发器和激活它的语句作为一个事务处理，可以从触发器中的任何位置撤销。触发器可以包括ROLLBACKTRANSACTION语句，激活触发器的语句可以看成隐含事务的开始。,5.3.1什么是触发器,5.3触发器,99,Deleted和inserted表:当用户对触发器表执行更新操作时，SQLServer自行为每个触发器创建和管理这两个表。它们是逻辑表，存放在内存中，用户不能直接对这两个表进行修改。这两个表的结构与触发器表的结构相同。触发工作完成后，与触发器相关的这两个表将被删除。,5.3.1什么是触发器,5.3触发器,100,1.inserted表inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。inserted表中的行是触发器表中新行的副本。,5.3.1什么是触发器,5.3触发器,101,2.deleted表deleted表用于存储DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传送到deleted表，deleted表和触发器表一般没有相同的行。,5.3.1什么是触发器,5.3触发器,102,5.3.2创建触发器,1.语法格式CREATETRIGGER触发器名ON表-给定建立触发器的表WITHENCRYPTION-加密存储触发器定义FOR|AFTER|INSTEADOF触发时间INSERT,DELETE,UPDATE-触发事件AS触发器要完成的操作-触发后的动作,5.3.1创建触发器,103,5.3.2创建触发器,说明：AFTEER：在触发操作执行完后触发。FOR关键字：与AFTER相同。INSERTEDOF：代替触发动作(触发语句)进行操作，并在处理约束之前触发。WITHENCRYPTION：加密存储触发器存放在syscomments中的文本。,5.3.1创建触发器,104,5.3.2创建触发器,注意：创建触发器的权限默认为表的所有者，且不能将该权限转让给其它用户。不能在临时表上创建触发器，但在触发器中可引用临时表。触发器允许嵌套。最大嵌套数32。,5.3.1创建触发器,105,触发器中不允许有下列语句：ALTERDATABASE,CREATEDATABASE,DISKINIT,DISKRESIZE,DROPDATABASE,LOADDATABASE等等。,5.3.2创建触发器,5.3.1创建触发器,106,5.3.2创建触发器,2.INSERT触发器插入触发器的执行过程：首先执行INSERT语句的插入操作。将新行插入到触发器表和inserted表中。然后执行触发器中的语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。,5.3.1创建触发器,107,5.3.2创建触发器,例14建立一个INSERT触发器。每当在“选课”表中插入一条记录时，检查学号在学生表中是否存在，若不存在，拒绝插入，否则允许。,5.3触发器,108,5.3.2创建触发器,CREATETRIGGERsc_insertON选课FORINSERTASBEGINDECLAREsnoCHAR(6)Selectsno=学号FROMinsertedIFNOTEXISTS(SELECT*FROM学生WHERE学号=sno)BEGINPRINT该生不存在！ROLLBACKTRANENDEND,5.3触发器,109,5.3.2创建触发器,测试：INSERTINTO选课(学号,课号,成绩)VALUES(000001,011,88),5.3触发器,110,5.3.2创建触发器,createtriggers_sno_numericondbo.学生forinsert,updateasbegindeclaresnochar(6)selectsno=inserted.学号frominsertedifisnumeric(sno)=0beginprint学号必须是数字构成，撤销此插入deletefrom学生where学号likesnoendend,例15:建立一个触发器,学号必须是数字字符.,5.3触发器,111,5.3.2创建触发器,createtriggersc_counton选课forinsertasbegindeclarecnochar(6)selectcno=课号frominsertedif160(selectcount(*)from选课where课号=cno)beginPrint选课人数已满rollbacktransactionendend,例16:建立一个触发器,一门功课的选课人数不能大于160.,5.3触发器,112,5.3.2创建触发器,3.delete触发器执行过程:首先执行DELETE语句的删除操作。将要删除的记录存放到deleted表中,然后删除触发器表中相应的行。执行触发器中语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。,5.3触发器,113,5.3.2创建触发器,例17建立一个DELETE触发器，每当在学生表中删除一条记录时，也将在选课表中删除相应的记录。,CREATETRIGGERS_deleteON学生FORDELETEASBEGINDECLAREsnoCHAR(6)Selectsno=学号FROMdeleted,5.3触发器,114,5.3.2创建触发器,IFEXISTS(SELECT*FROM选课WHERE学号=sno)BEGINDELETEFROM选课WHERE学号=snoPRINT成功删除!ENDEND,测试：(在学生表加111111学生,练习)DELETEFROM学生WHERE学号=111111,5.3触发器,115,5.3.2创建触发器,4.UPDATE触发器执行过程：首先执行UPDATE语句。将修改前的旧行插入到deleted表中,再修改触发器表中该行信息,将修改后的新行插入到inserted表。执行触发器中的语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。,5.3触发器,116,5.3.2创建触发器,例18建立一个UPDATE触发器，每当在选课表中修改一条记录时，也将在c_avg表中更新相应的记录。,Createtablec_avg(课号char(4),平均成绩int)InsertIntoc_avgSelect课号,Avg(成绩)As平均成绩From选课GroupBy课号,c_avg(课号,平均成绩),5.3触发器,117,5.3.2创建触发器,CREATETRIGGERsc_updateON选课FORUPDATE,INSERTASDECLAREcnoCHAR(4)Selectcno=inserted.课号FROMinserted,5.3触发器,118,5.3.2创建触发器,IFNOTEXISTS(SELECT*FROM选课WHERE课号likecno)BEGININSERTINTOC_avgSELECT课号,AVG(成绩)FROM选课WHERE课号=cnoGROUPBY课号PRINT新增加了一条课程记录！ENDELSE,5.3触发器,119,5.3.2创建触发器,BEGINUPDATEC_avgSET平均成绩=(SELECTAVG(成绩)FROM选课WHERE课号=cnoGROUPBY课号)WHERE课号=cnoPRINT成功更新课