T-SQL程序设计基础1.ppt

第4章T-SQL程序设计基础,4.1SQL语言和T-SQL语言4.2T-SQL的标识符4.3T-SQL的数据类型4.4T-SQL的运算符及表达式4.6T-SQL中的语句4.5T-SQL中的变量4.7T-SQL函数,SQL与T-SQL,SQL(结构化查询语言）-数据库能够识别指令、执行对应的操作并为程序提供数据的指令集。T-SQL是SQL的加强版。T-SQL由数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）组成。T-SQL语句的语法格式规定：（1）语句中的字母大小写均可；（2）关键字有特殊用途，定义变量名时不得使用关键字；（3）语句中的日期型常量和字符型常量必须用单引号括起来；（4）语句中的标点符号必须用英文标点，即半角符号；（5）一条语句可以分行写，一行也可以写多条语句，语句末尾不写任何标点。,标识符,标识符是指用户在SQLServer中定义的服务器、数据库、数据库对象、变量和列等对象名称。SQLServer标识符分为规则标识符和定界标识符两类。规则标识符规则标识符是严格按标识符命名规则定义的标识符，不需要任何定界符号括起来。定界标识符定界标识符是使用定界符号或“”将标识符括起来的标识符。使用了定界标识符，既可以遵守标识符的命名规则，也可以不遵守标识符命名规则。,标识符,标识符命名规则1标识符必须以字母、_(下划线)、或#开头；2标识符可以是字母、数字、_、#或$的组合；3标识符不允许使用保留字；4标识符不允许使用除_、#或$之外的其他符号，例如：不能使用空格符号；5标识符大小写等价。特殊标识符某些以特殊符号打头的标识符在T-SQL中具有特殊意义。例如：以#开头的标识符表示临时表；以开头的标识符表示局部变量；以开头的标识符表示全局变量；,提示：全局变量由系统提供，用户不要定义以开头的标识符。,T-SQL数据类型,T-SQL数据类型,T-SQL数据类型,T-SQL数据类型,T-SQL数据类型,T-SQL数据类型,T-SQL数据类型,T-SQL数据类型,T-SQL运算符和表达式,算术运算运算符：+，-，*，/、%字符串运算运算符：+（表示两个字符串的连接）关系运算运算符：、=、!、!andor位运算运算符：（取反）、例：EXECSQLDROPTABLEStudent;以COBOL作为主语言的嵌入式SQL语句的一般形式：EXECSQLEND-EXEC例：EXECSQLDROPTABLEStudentEND-EXEC,6.5.2嵌入式SQL语句与主语言之间的通信,将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句SQL语句描述性的面向集合的语句负责操纵数据库高级语言语句过程性的面向记录的语句负责控制程序流程,1.SQL通信区,SQLCA：SQLCommunicationAreaSQLCA是一个数据结构SQLCA的用途SQL语句执行后，DBMS反馈给应用程序信息描述系统当前工作状态描述运行环境这些信息将送到SQL通信区SQLCA中应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句,SQLCA的内容,与所执行的SQL语句有关与该SQL语句的执行情况有关例：在执行删除语句DELETE后，不同的执行情况，SQLCA中有不同的信息：违反数据保护规则，操作拒绝没有满足条件的行，一行也没有删除成功删除，并有删除的行数无条件删除警告信息由于各种原因，执行出错,SQLCA的使用方法,定义SQLCA用EXECSQLINCLUDESQLCA加以定义使用SQLCASQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错应用程序每执行完一条SQL语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理,主变量,在SQL语句中使用的主语言程序变量简称为主变量。输入主变量：由应用程序对其赋值，SQL语句引用；输出主变量：由SQL语句结对其赋值，返回给应用程序。一个主变量既可以是输入主变量，也可以是输出主变量。利用输入主变量，可以和数据库中插入数据，修改数据库中的数据，执行指定的操作；利用输出主变量，可以得到SQL语句的结果数据和状态。,主变量的用途,输入主变量指定向数据库中插入的数据将数据库中的数据修改为指定值指定执行的操作指定WHERE子句或HAVING子句中的条件输出主变量获取SQL语句的结果数据获取SQL语句的执行状态,指示变量,一个主变量可以附带一个指示变量（IndicatorVariable）什么是指示变量整型变量用来“指示”所指主变量的值或条件指示变量的用途输入主变量可以利用指示变量赋空值输出主变量可以利用指示变量检测出是否空值，值是否被截断,在SQL语句中使用主变量和指示变量的方法,1)说明主变量和指示变量BEGINDECLARESECTION.(说明主变量和指示变量).ENDDECLARESECTION2)使用主变量说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志3)使用指示变量指示变量前也必须加冒号标志必须紧跟在所指主变量之后,在SQL语句之外(主语言语句中)使用主变量和指示变量的方法可以直接引用，不必加冒号,3.游标（cursor,为什么要使用游标SQL语言与主语言具有不同数据处理方式SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录主语言是面向记录的，一组主变量一次只能存放一条记录仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式,什么是游标,游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果每个游标区都有一个名字用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理,小结,在嵌入式SQL中，SQL语句与主语言语句分工非常明确SQL语句：直接与数据库打交道主语言语句1.控制程序流程2.对SQL语句的执行结果做进一步加工处理,SQL语句用主变量从主语言中接收执行参数，操纵数据库SQL语句的执行状态由DBMS送至SQLCA中主语言程序从SQLCA中取出状态信息，据此决定下一步操作如果SQL语句从数据库中成功地检索出数据，则通过主变量传给主语言做进一步处理SQL语言和主语言的不同数据处理方式通过游标来协调,例：带有嵌入式SQL的一小段C程序,.EXECSQLINCLUDESQLCA;/*(1)定义SQL通信区*/EXECSQLBEGINDECLARESECTION;/*(2)说明主变量*/CHARtitle_id(7);CHARtitle(81);INTroyalty;EXECSQLENDDECLARESECTION;main()EXECSQLDECLAREC1CURSORFORSELECTtit_id,tit,royFROMtitles;/*(3)定义游标*/*从titles表中查询tit_id,tit,roy*/EXECSQLOPENC1;/*打开游标*/,for(;)EXECSQLFETCHC1INTO:title_id,:title,:royalty;/*(5)游标操作（将当前数据放入主变量并推进游标指针）*/if(sqlca.sqlcodeSUCCESS)/*(6)利用SQLCA中的状态信息决定何时退出循环*/break;printf(TitleID:%s,Royalty:%d,:title_id,:royalty);printf(Title:%s,:title);/*打印查询结果*/EXECSQLCLOSEC1;/*(7)游标操作（关闭游标）*/,6.5.2不用游标的SQL语句,1.几种不需要使用游标的SQL语句(1)用于说明主变量的说明性语句SQL的说明性语句主要有两条：EXECSQLBEGINDECLARESECTION；EXECSQLENDDECLARESECTION；(2)数据定义和数据控制语句(3)查询结果为单记录的查询语句(4)数据的插入语句和某些数据删除、修改语句独立的数据删除和修改语句不需要使用游标；与查询语句配合，删除或修改查询到的当前记录的操作，与游标有关。,2.不用游标的查询语句,EXECSQLSELECTALL|DISTINCT目标列表达式，nINTO主变量指示变量，nFROM表名或视图名，nWHERE条件表达式；1)在语句开始前要加EXECSQL前缀。2)该查询语句中又扩充了INTO子句。3)在WHERE子句的条件表达式中可以使用主变量。4)由于查询的结果集中只有一条记录，该语句中不必有排序和分组子句。5)INTO子句中的主变量后面跟有指示变量时：结果列值为NULL，指示变量为负值，结果列不向该主变量赋值；传递正常，指示变量的值为0；主变量宽度不够，则指示变量的值为数据截断前的宽度。,例子,【例5-41】查询学号为主变量givesno、课号为主变量givecno的值的学生选课记录。EXECSQLSELECT学号，课程号，成绩INTO:Sno，:Cno，:grade:gradeidFROM选课WHERE学号=:givesnoAND课程号=:givecno；3.不用游标的数据维护语句(1)不用游标的数据删除语句【例5-42】删除学号由主变量Sno决定的学生记录。EXECSQLDELETEFROM学生WHERE学号=:Sno；,(2)不用游标的数据修改语句,【例5-43】将计算机系所有学生的年龄都加上主变量Raise。EXECSQLUPDATE学生SET年龄=年龄+:RaiseWHERE所在系=计算机系；【例5-44】将计算机系学生的年龄置空。Raiseid=-1EXECSQLUPDATE学生SET年龄=年龄+:Raise:Raiseid；,(3)不用游标的数据插入语句,【例5-45】将学号为主变量Sno、课程号为Cno的选课记录，插到库中。EXECSQLINSERTINTO选课VALUES(:Sno，:Cno)；,6.5.3使用游标的SQL,必须使用游标的SQL语句有:查询结果为多条记录的SELECT语句(1)定义游标命令EXECSQLDECLARE游标名CURSORFOR子查询FORUPDATEOF字段名1，n；删除和修改数据的语句中，WHERE为：WHERECURRENTOF游标名,【例5-46】定义按主变量DEPT查询系里学生的游标。EXECSQLDECLARESXCURSORFORSELECT*FROM学生WHERE所在系=:DEPT；,(2)打开、推进和关闭游标命令,EXECSQLOPEN游标名；执行对应的查询语句，并将游标指向结果集的第一条记录前。打开的游标处于活动状态，可以被推进。EXECSQLFETCH游标名INTO主变量组；将游标下移一行，读出当前的记录，将当前记录的各数据项值放到INTO后的主变量组中。EXECSQLCLOSE游标名；关闭游标,【例5-47】打开SX游标。DEPT=计算机系EXECSQLOPENSX；【例5-48】将打开的指向系的游标向前推进。EXECSQLFETCHSXINTO:Sno，:Sname，:Sage，:Sdept；,例1查询某个系全体学生的信息（学号、姓名、性别和年龄）。要查询的系名由用户在程序运行过程中指定，放在主变量deptname中.EXECSQLINCLUDESQLCA;.EXECSQLBEGINDECLARESECTION;./*说明主变量deptname,HSno,HSname,HSsex,HSage等*/.EXECSQLENDDECLARESECTION;.gets(deptname);/*为主变量deptname赋值*/.,EXECSQLDECLARESXCURSORFORSELECTSno,Sname,Ssex,SageFROMStudentWHERESDept=:deptname;/*说明游标*/EXECSQLOPENSX/*打开游标*/WHILE(1)/*用循环结构逐条处理结果集中的记录*/EXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;/*将游标指针向前推进一行，然后从结果集中取当前行，送相应主变量*/if(sqlca.sqlcodeSUCCESS)break;/*若所有查询结果均已处理完或出现SQL语句错误，则退出循环*/*由主语言语句进行进一步处理*/.;EXECSQLCLOSESX;/*关闭游标*/.,6.6数据控制机制和语句,6.6.1数据控制机制1.授权定义具有授权资格的用户，如DBA或DBO，通过数据控制语言DCL，将授权决定告知数据库管理系统。2.存权处理DBMS把授权的结果编译后存入数据字典中。3.查权操作当提出操作请求时，系统在数据字典中查找该用户的数据操作权限，当拥有该操作权时执行其操作，否则系统将拒绝其操作。,6.6.2数据控制语句,1.授权语句GRANT系统特权组To用户组|PUBLICWITHGRANTOPTION；1)PUBLIC指数据库的所有用户。2)WITHGRANTOPTION:获得权限的用户可以把该权限再授予别的用户。GRANTALLPRIVILIGES|对象特权组ON对象名TO用户组|PUBLICWITHGRANTOPTION；1)ALLPRIVILIGES指所有的对象特权。2)对象名指操作的对象标识，如表名、视图名和过程名等。,例子,【例6-51】把修改学生学号和查询学生表的权力授予用户王平。GRANTUPDATE(学号),SELECTON学生TO王平;【例6-52】把建立数据库和备份数据库的权力赋给用户王平。GRANTCREATEDATABASE，BACKUPDATABASETO王平；,2.收权语句,REVOKE语句的一般格式为：REVOKE权限组|ALLPRIVILIGESON对象名FROM用户名组|PUBLIC；其中：ON子句用于指定被收回特权的对象；ALLPRIVILIGES指收回所有特权；PUBLIC指所有用户【例6-53】将用户王平的可以在学生表中修改学生学号的权利收回。REVOKEUPDATE(学号)ON学生FROM王平；,3.拒绝访问语句,拒绝访问语句的一般格式为：DENYALLPRIVILIGES|权限组ON对象名TO用户组|PUBLIC；其中：ON子句用于说明对象特权的对象名；对象名指的是表名、视图名、视图和表的字段名或者过程名。,6.7.创建和管理缺省,(1)创建缺省CREATEDEFAULT缺省名(2)绑定缺省EXECsp_bindfault缺省名，表名.列名系统存储过程执行时的格式为：EXEC存储过程名参数组如果参数是常量，则要加定界符。(3)解除缺省EXECsp_unbinddefault缺省名，表名.列名(4)删除缺省DROPDEFAULT缺省名组,MS格式,createdefaultas默认值createdefaultxbas男sp_binddefaul默认名,表名.列名sp_bindefaultxb,学生.性别sp_unbindefault表名.列名sp_unbindefault学生.性别dropdefault默认名dropdefaultxb,6.7.创建和管理规则,(1)创建规则CREATERULE规则名AS规则表达式规则表达式是WHERE子句中的有效表达式。createrulenum_ruleasnumbetween0and100(2)绑定规则EXECsp_bindrule规则名，表名.列名sp_bindrulenum_rule,选课.成绩(3)解除规则EXECsp_unbindrule规则名，表名.列名(4)删除规则DROPRULE规则名组,6.7.创建和管理存储过程,(1)创建存储过程和调用存储过程CREATEPROCEDURE过程名;版本号参数名参数类型=缺省值OUTPUTWITHRECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTIONASSQL语句组1)版本号是整数，它用于将有相同名字的存储过程编为不同的组。2)OUTPUT用于给调用者返回值。3)RECOMPILE为重编译。4)ENCYPTION为加密选项。5)参数前加“”为局部变量，加“”则说明为全局变量。过程的调用语句为：EXEUTE过程名参数名=参数版本号(2)删除存储过程DROPPROCEDURE存储过程名组,6.8触发器的创建与使用,6.81、触发器的概念1、触发器触发器是一段能自动执行的程序，是一种特殊的存储过程，其特殊性在于：不允许使用参数，没有返回值。不允许用户调用，当对表进行插入、删除、修改操作时由系统自动调用并执行（相当于事件方法）。触发器可以实现比较复杂的完整性约束：扩展约束、默认值和规则对象的完整性检查。自动生成数据。检查数据的修改，防止对数据不正确的修改，保证数据表之间数据的正确性和一致性。自定义复杂的安全权限。触发器作为一种数据库对象，在syscomment系统表中存储其完整的定义信息，在sysobject系统表中有该对象的记录。,2、触发器的用途及优点,实现数据库中多个表的级联修改：当修改删除某张表的数据时，其他表的相应数据能自动修改或删除，以保证数据的一致性（也可在设置外键约束时设置相应的选项，而且效率更高）。检查数据输入的正确性：CHECK约束在限制数据输入时不能参照其他表中的数据。如销售金额=数量*单价的自动计算、销售数量不允许超过库存量等，用CHECK约束是无法实现的，用触发器即可实现比CHECK更复杂的约束检查。检查数据修改的正确性：综合以上两种情况，当对表中受触发器保护的数据修改时，触发器不但会自动更新其他表与其相关的数据，还可以自动检查这些数据，只要有一个不符合条件，则修改数据失败。,3、触发器的触发方式,为数据表中某个字段设置触发器后，当该字段的数据被INSERT插入、DELETE删除、UPDATE修改更新时，触发器便被激活并自动执行。SQLServer按触发器被激活的时机可分为“后触发”和“替代触发”两种触发方式。,（1）后触发,若引发触发器执行的语句通过了各种约束检查，成功执行后才激活并执行触发器程序，这种触发方式称为“后触发”。后触发的特点：若引发触发器执行的语句违反了某种约束，该语句不会执行，则后触发方式的触发器也不被激活。后触发方式只能创建在数据表上，不能创建在视图上。一个表可以有多个后触发触发器。,(2)、替代触发发器,若激活触发器的语句仅仅起到激活触发器的作用，一旦激活触发器后该语句即停止执行，立即转去执行触发器的程序激活触发器的语句并不被执行，相当于禁止某种操作。这种触发方式称为“替代触发”。替代触发的特点：替代触发可以创建在表上，也可以创建在视图上。一个表只能有一个替代触发的触发器。,4、触发器使用的inserted和deleted临时表,不论后触发或替代触发，每个触发器被激活时，系统都自动为它们创建两个临时表：inserted和deleted表。两个表的结构与激活触发器的原数据表结构相同。用INSERT语句插入记录激活触发器时，系统在原表插入记录的同时，也自动把记录插入到inserted临时表。用SELECT语句删除记录激活触发器时，系统在原表删除记录的同时，会把删除的记录添加到deleted临时表。用UPDATE语句修改数据激活触发器时，系统先在原表删除原有记录，删除的记录被添加到deleted临时表，然后再插入新记录，并同时插入到inserted临时表。用户可以用SELECT语句查询这两个临时表，但不允许进行修改。触发器一旦执行完成，这两个表将被自动删除。,6.8.2、用CREATETRIGGER语句创建触发器,语法格式：CREATETRIGGER拥有者.触发器名ON拥有者.表名|视图名for|after|insteadofinsert,update,deleteWITHencryptionNOTFORreplicationASSETNOCOUNT-不返回给变量赋值的结果SQL语句系列ROLLBACKTRANSACTION-事务回滚,例如:,CREATETRIGGERtr_updateon学生forupdateasdeclaremsgvarchar(80)selectmsg=str(rowxount)+个记录被更新!printmsgreturnupdate学生set年龄=35where姓名=李明,参数,ON表名|视图名：指定激活触发器被操作的表或视图。for与after：指定所创建的触发器为后触发方式，for与after完全相同，for是为了与以前老版本兼容而保留。insteadof：指定所创建的触发器为替代触发方式。insert,update,delete：指定激活该触发器的具体操作，可以指定一项，也可三项同时指定，但必须以逗号隔开。,参数,WITHencryption：指定对触发器文本加密，禁止查阅修改。NOTFORreplication：指定在复制过程中不激活触发器操作。SETNOCOUNT：触发器一般不能有返回值，所以也不应有SELECT语句进行查询或给变量赋值（获得被操作数据的语句除外），如果必须使用变量赋值语句，可在开头使用该语句避免返回结果。SQL语句系列：即触发器被指定操作激活后要执行的SQL代码，其中可包含获得被操作数据的SELECT语句：后触发方式被操作数据一定在inserted或deleted临时表中。如果被操作的数据是多值的，可用IN判断是否被包含在其中：被操作数据IN(SELECT被操作字段FROM临时表)如果被操作的数据是单值的，可用以下语句获得：SELECT变量=被操作字段FROM临时表ROLLBACKTRANSACTION：事务回滚语句。对于后触发方式，语句已经执行完毕才执行触发器，如果发现操作不符合规则，可用该语句取消操作。,注意,CREATETRIGGER语句必须是一个批处理的第一条语句。创建触发器的权限默认属于表的所有者，且不能授权给其他人。触发器不能在临时表或系统表上创建，后触发也不能创建在视图上。一个触发器只能创建在一个表上；一个表可以有一个替代触发器和多个后触发器（可以是同一种操作类型，可同时触发）。由于TRUNCATETABLE语句删除记录时不被记入事务日志，所以该语句不能激活delete删除操作的触发器。如果外键所引用的父表已经创建了对子表级联修改或删除的触发器，则子表不允许创建具有相同动作的替代触发器。触发器的定义语句中不能有任何用CREATE创建、用ALTER修改数据库或各种对象的语句，不允许使用任何DROP删除语句。也不允许使用以下语句：GRANT/RESTOREDATABASE/RESTORELOGREVOKETRUNCATETABLE,示例1,假设diannaoxs数据库有一个“商品表”和“销售合同表2006”我们为“商品表”创建一个名为“删除商品”的触发器，当删除“商品表”中的某个商品时，需要把这些商品在“销售合同表2006”中的销售合同同时全部删除，实现“商品表”和“销售合同表2006”的级联删除。,CREATETRIGGER删除商品ON商品表afterdeleteASDELETE销售合同表2006WHERE货号in(SELECT货号FROMdeleted)该语句为“商品表”创建了一个由删除动作激活的“删除商品”触发器，当“商品表”中有记录被删除之后（deleted表中有被删除的记录），该触发器即会自动执行。,“删除商品”触发器的执行过程：,（1）创建inserted和deleted临时表，“商品表”被删除的记录存放在deleted表中。（2）从deleted临时表中查询并得到被删除记录的“货号”。（3）将“销售合同表2006”中所有“货号”与被删除“货号”相等的记录删除。创建触发器之后，如果对数据库有以下操作语句：DELETE商品表WHERE货号=1005-删除1005号商品记录IFnotexists(SELECT*FROM销售合同表2006WHERE货号=1005)PRINT相关记录已经从“销售合同表2006”中删除了！当第一条语句将“商品表”中第1005号商品的记录成功删除后，触发器被激活删除“销售合同表2006”中有关1005号商品的记录。IF语句找不到1005号商品的记录则会显示信息：相关记录已经从“销售合同表2006”中删除了！,示例2,【例8-5】为“销售合同表2006”创建一个名字为“统计被修改记录数”的后触发器，当对“销售合同表2006”的数据进行更新时，可以自动统计并显示修改的总行数。,创建触发器之前，可用SQL语句先在sysobjects系统表中检测是否存在名字为“统计被修改记录数”、类型为“tr”的触发器，如果存在就把它删除，避免调试时的麻烦。USEdiannaoxsIFexists(SELECTnameFROMsysobjectsWHEREname=统计被修改记录数ANDtype=tr)DROPTRIGGER统计被修改记录数GO,示例2：,CREATE统计被修改记录数ON销售合同表2006afterupdateASDECLAREmsgvarchar(100)SELECTmsg=str(rowcount)+diannaoxs被修改描述PRINTmsgRETURNGO该例题为“销售合同表2006”创建了一个由update更新动作激活的后触发器“统计被修改记录数”，当“销售合同表2006”中有记录被成功更新之后，该触发器即会自动执行：创建inserted和deleted临时表，“销售合同表2006”被删除的记录存入deleted表，新插入的记录存入inserted表。将保存有被修改记录个数的全局变量rowcount转化为字符串输出。,综合举例练习,为“员工表”创建一个名为“修改姓名”的后触发器，当修改某个员工姓名时，需要把“销售表2006”的“销售员”、“进货表2006”的“收货人”同时进行全部修改，实现“员工表”和“销售表2006”“进货表2006”的级联修改。USEdiannaoxsIFexists(SELECTnameFROMsysobjectsWHEREname=修改姓名ANDtype=tr)DROPTRIGGER修改姓名GOCREATETRIGGER修改姓名ON员工表afterupdateASDECLARExm1varchar(8),xm2varchar(8)SELECTxm1=姓名FROMdeleted-从deleted表得到被删除的原姓名SELECTxm2=姓名FROMinserted-从inserted表得到被更新的新姓名UPDATE销售表2006SET销售员=xm2WHERE销售员=xm1UPDATE进货表2006SET收货人=xm2WHERE收货人=xm1GO,【实例练习8-4】,该例题为“员工表”创建了一个由更新动作激活的“修改姓名”后触发器，当“员工表”中有记录被成功更新之后，该触发器即会自动执行。也可以不定义局部变量，在更新表达式及条件中直接使用临时表的字段，但直接在表中查询数据不如使用局部变量效率高，且不容易出错。运行代码创建触发器成功后显示：命令已成功完成。如果将公司员工“高宏”改名为“高立宏”，则可对“员工表”进行修改：UPDATE员工表SET姓名=高立宏WHERE姓名=高宏SELECT*FROM员工信息如果查询一下“销售表2006”，则所有原来的销售员“高宏”也全部被触发器自动更新为“高立宏”了。,【实例练习8-5】,为“进货表2006”创建一个名字为“进货检查”的后“触发器”，当从厂家购进某种商品添加一条新记录时，能自动执行以下操作：（1）对商品一览表中“平均进价”字段进行自动更新：平均进价=（进价*数量+平均进价*库存量）/（数量+库存量）我们可用【实例练习7-6】中定义的自定义函数“平均价格()”：平均进价=平均价格(原平均进价,库存,进价,数量)（2）对商品一览表中“库存量”字段进行自动更新：库存量=库存量+数量注意顺序：因为计算平均进价要使用原来的库存量，所以必须先更新平均进价，再更新库存（本题使用局部变量可以不考虑）。USEdiannaoxsIFexists(SELECTnameFROMsysobjectsWHEREname=进货检查ANDtype=tr)DROPTRIGGER进货检查,【实例练习8-5】,GOCREATETRIGGER进货检查ON进货表2006afterinsertASDECLAREhhchar(4),slint,jjSmallmoney,pjSmallmoney,kcbigintSELECThh=货号,sl=数量,jj=进价FROMinsertedSELECTpj=平均进价,kc=库存量FROM商品一览表WHERE货号=hhUPDATE商品一览表SET平均进价=平均价格(pj,kc,jj,sl)WHERE货号=hhUPDATE商品一览表SET库存量=kc+slWHERE货号=hhGO对字段更新的表达式中可以使用字段名，用局部变量效率更高。运行代码成功创建触发器后显示：命令已成功完成。假设公司刚从厂家代号为BJLX的“北京联想科技股份有限公司”按每台5180元的价格购进“1001”号计算机10台，由公司员工于丽负责验货入库。我们必须在“进货表2006”中插入一条记录，其中：进货日期可使用当前日期的默认值，序号、厂家名称不需要输入，只输入供货商ID即可，实际上是按进货表全部字段输入。,【实例练习8-5】,注意：先请查询“商品一览表”，并记住“商品一览表”中该商品平均进价（5275元），和原有库存量（13台）。由于对“进货表2006”的“收货人”设置了外键约束引用“员工表”的“姓名”，员工“于丽”必须与“员工表”一致。输入代码：INSERT进货表2006VALUES(default,1001,10,5180.00,BJLX,于丽)SELECT*FROM进货信息视图2006运行结果如图8-12所示。再打开“商品一览表”可以看到，1001商品的平均进价已自动修改（5233.7元），库存量也已自动修改（23台）。,【实例练习8-6】,为“销售表2006”创建一个名字为“销售检查”的后触发器，当销售某种商品添加一条新记录时，能自动执行以下操作：n自动检查销售“数量”不允许大于商品一览表中的“库存量”。n自动检查“单价”下浮或上调不允许超出商品一览表公司所制定“参考价格”的5%范围。n根据“货号”自动从商品一览表中获得相应的“货名”数据。n自动计算“金额=单价*数量”。n对商品一览表中“库存量”进行自动更新。USEdiannaoxsIFexists(SELECTnameFROMsysobjectsWHEREname=销售检查ANDtype=tr)DROPTRIGGER销售检查GOCREATETRIGGER销售检查ON销售表2006afterinsertASDECLARExhBigInt,hhchar(4),slint,djSmallmoney,hmNvarchar(8),ckjgSmallmoney,kcbigintSELECTxh=序号,hh=货号,sl=数量,dj=单价FROMinsertedSELECThm=货名,ckjg=参考价格,kc=库存量FROM商品一览表WHERE货号=hh,【实例练习8-6】,IFsl=ckjg*0.95ANDdj5BEGINPRINT销售记录已超过5天，不准修改！ROLLBACKTRANSACTION-事务回滚，撤消修改ENDGO,【实例练习8-8】,注意：条件表达式不能使用Day(getdate()-Day(rq)5,这样则月底和下个月初的日期计算不对。运行代码创建触发器成功后显示：“命令已成功完成。”如果将序号17销售记录的客户名称“济南商业电脑商城”改为“济南商业电脑城”，可使用以下语句进行修改：UPDATE销售表2006SET客户名称=济南商业电脑城WHERE序号=17SELECT*FROM销售表2006运行后可以看到数据已得到了修改，重新设置一下系统时间为5天以后，再输入以下语句：UPDATE销售表2006SET客户名称=济南商业电脑公司WHERE序号=17运行结果显示：销售记录已超过5天，不准修改！,【实例练习8-9】,设置替代触发器“禁止修改”，不允许对“供货商表”的厂家记录进行修改、删除。USEdiannaoxsIFexists(SELECTnameFROMsysobjectsWHEREname=禁止修改ANDtype=tr)DROPTRIGGER禁止修改GOCREATETRIGGER禁止修改ON供货商表insteadofupdate,delete-修改或删除激活ASPRINT请原谅,供货商表不允许对任何数据修改和删除。GO该触发器为替代触发，只要对“供货商表”进行任何修改、删除操作，则立即停止并取消该SQL语句对“供货商表”的操作，激活并执行触发器，所以不需要事务回滚语句。,【实例练习8-9】,在创建该触发器之前，可以将“供货商表”编号为SDKJ的厂家名称“山东科技市场计算机销售处”改为“山东科技市场计算机销售中心”：UPDATE供货商表SET供货商=山东科技市场计算机销售中心WHERE供货商ID=SDKJSELECT*FROM供货商表运行后可以看到数据已得到了修改，创建触发器以后，再输入修改语句结果就会显示：“请原谅,供货商表不允许对任何数据修改和删除。”注意：如果外键引用的父表已创建了对子表级联修改或删除的触发器，则子表不允许创建具有相同动作的替代触发器。例如我们已经创建了商品一览表员工表供货商表对进货表2006销售表2006的级联UPDATE触发器，则进货表2006销售表2006可以创建由UPDATE动作激活的后触发器，但不允许创建由UPDATE动作激活的替代触发器。,8.3.4、禁用/启用触发器,当某个表设置触发器禁止对某个字段进行修改而又必须对该表进行修改时，可以使用禁用触发器命令，使触发器不起作用，修改以后重新启用。例如：【实例练习8-9】对“供货商表”已设置替代触发器“禁止修改”，如果有必要修改时必须禁用触发器，修改后再启用该触发器。假设“员工表”与“销售表2006”“进货表2006”用触发器实现了级联删除，若某个员工调离需要从“员工表”中删除但是原来他所经手的销售或进货记录不能删除，则可以禁用触发器，“员工表”删除记录以后再启用该触发器。再比如：销售价格被触发器限制在公司制定的参考价格5%范围内浮动，如果遇到特殊情况做特价处理时，则可以禁用触发器，特价销售以后再启用该触发器。禁用触发器的语句格式：ALTERTABLE表名DISABLETRIGGER触发器名启用触发器的语句格式：ALTERTABLE表名ENABLETRIGGER触发器名,【实例练习8-10】,禁止使用“供货商表”创建的替代触发器“禁止修改”，允许对厂家记录进行修改、删除。ALTERTABLE供货商表DISABLETRIGGER禁止修改UPDATE供货商表SET供货商=山东科技市场计算机销售处WHERE供货商ID=SDKJSELECT*FROM供货商表运行后可以看到数据已得到了修改，可见“禁止修改”触发器已经不起作用，可以对供货商表进行修改和删除。之后使用以下语句再启用触发器。ALTERTABLE供货商表ENABLETRIGGER禁止修改,8.3.5、用企业管理器创建触发器,在企业管理器创建触发器的步骤如下：（1）在控制台根目录下展开数据库展开数据表节点，选中要创建触发器的表，右键单击鼠标，从快捷菜单中选择“所有任务”“管理触发器”命令，弹出“触发器属性”对话框。如图8-15所示。,8.3.5、用企业管理器创建触发器,（2）在“触发器属性”对话框中进行以下操作：n在“名称”下拉列表中选择“新建”（默认）。n在“文本”框中输入触发器的SQL语句代码。n单击“语法检查”按钮，可检查SQL语句的语法是否正确。（3）单击“确定”按钮完成触发器的创建。注意：u不能在系统表和临时表上创建任何触发器，不能在视图上创建后触发器。u可以在触发器中引用视图或临时表，但不能引用系统表。,8.4触发器的查看、编辑、重命名和删除,8.4.1、用企业管理器查看编辑触发器如同在企业管理器中创建触发器相同：在控制台根目录下展开数据库展开数据表节点，选中要查看、修改、删除触发器的表，右键单击鼠标，从快捷菜