存储过程、触发器.ppt

《存储过程、触发器.ppt》由会员分享，可在线阅读，更多相关《存储过程、触发器.ppt（30页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 第6章 存储过程、触发器6.1存储过程存储过程6.2触发器触发器 存储过程的类型(1)系统存储过程系统存储过程系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库统存储过程定义在系统数据库master中，其前缀是中，其前缀是sp_，例如常用的显示系统对，例如常用的显示系统对象信息的象信息的sp_help存储过程，它们为检索系统表的信息提供了方便快捷的方法。存储过程，它们为检索系统表的信息提供了方便快捷的方法。系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可以在系统存储过程允许系统

2、管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。常用的系统存储过程，请见附录。任何一个数据库中执行。常用的系统存储过程，请见附录。(2)本地存储过程本地存储过程本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以定数据库操作任务，其名称不能以sp_为前缀。为前缀。(3)临时存储过程临时存储过程临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能

3、在一个用户会，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。话中使用。如果本地存储过程的名称前有两个如果本地存储过程的名称前有两个“#”，该过程就是全局临时存储过程，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。这种存储过程可以在所有用户会话中使用。(4)远程存储过程远程存储过程远程存储过程指从远程服务器上调用的存储过程。远程存储过程指从远程服务器上调用的存储过程。(5)扩展存储过程扩展存储过程在在SQLServer2000环境之外执行的动态链接库称为扩展存储过程，其前缀环境之外执行的动态链接库称为扩展存储过程，其前缀是是sp_。使用时需要先加载到

4、。使用时需要先加载到SQLServer2000系统中，并且按照使用存储过程的系统中，并且按照使用存储过程的方法执行。方法执行。用户存储过程的创建与执行用户存储过程只能定义在当前数据库中，可以使用用户存储过程只能定义在当前数据库中，可以使用SQL命令语句或命令语句或SQLServer的企业管理器创建存储过程。缺省情况下，用户创建的存储过程归数据库的企业管理器创建存储过程。缺省情况下，用户创建的存储过程归数据库所有者拥有，数据库的所有者可以把许可授权给其他用户。所有者拥有，数据库的所有者可以把许可授权给其他用户。1创建存储过程创建存储过程语法格式语法格式:CREATEPROCEDUREproced

5、ure_name;number/*定义过程名定义过程名*/parameterdata_type/*定义参数的类定义参数的类型型*/VARYING=defaultOUTPUT/*定义参数的属性定义参数的属性*/,.n1WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION/*定义存储过程的处理方定义存储过程的处理方式式*/FORREPLICATIONASsql_statement.n2/*执行的操作执行的操作*/说明：说明：参数参数procedure_name用于指定存储过程名，必须符合标识符规则，且对于数用于指定存储过程名，必须符合标识符规则，且对于数据库及

6、其所有者必须唯一；创建局部临时过程，可以在据库及其所有者必须唯一；创建局部临时过程，可以在procedure_name前面加前面加一个一个“#”；创建全局临时过程，可以在；创建全局临时过程，可以在procedure_name前加前加“#”。参数参数number为可选的整数，用于区分同名的存储过程，以便用一条为可选的整数，用于区分同名的存储过程，以便用一条DROPPROCEDURE语句删除一组存储过程；语句删除一组存储过程；用户存储过程的创建与执行FORREPLICATION用于说明不能在订阅服务器上执行为复制创用于说明不能在订阅服务器上执行为复制创建的存储过程，该选项不能和建的存储过程，该选项

7、不能和WITHRECOMPILE一起使用。参数一起使用。参数sql_statement代表过程体包含的代表过程体包含的T-SQL语句，参数语句，参数n2说明一个存储过程说明一个存储过程可以包含多条可以包含多条T-SQL语句。语句。对于存储过程要注意下列几点：对于存储过程要注意下列几点：(1)用户定义的存储过程只能在当前数据库中创建（临时过程除外，用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在临时过程总是在tempdb中创建）。中创建）。(2)成功执行成功执行CREATEPROCEDURE语句后，过程名称存储在语句后，过程名称存储在sysobjects系统表中，而系统表中

8、，而CREATEPROCEDURE语句的文本存储在语句的文本存储在syscomments中。中。(3)自动执行存储过程自动执行存储过程QLServer启动时可以自动执行一个或多个存储过程。这些存储过启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在程必须由系统管理员在master数据库中创建，并在数据库中创建，并在sysadmin固定服务固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。器角色下作为后台过程执行。这些过程不能有任何输入参数。(4)sql_statement的限制的限制除了除了SETSHOWPLAN_TEXT和和SETSHOWPLAN_ALL外，其外

9、，其它它SET语句均可在存储过程内使用。语句均可在存储过程内使用。用户存储过程的创建与执行如下语句必须使用对象所有者名对数据库对象进行限定：如下语句必须使用对象所有者名对数据库对象进行限定：CREATETABLE、ALTERTABLE、DROPTABLE、TRUNCATETABLE、CREATEINDEX、DROPINDEX、UPDATESTATISTICS及及DBCC语句。语句。权限。权限。CREATEPROCEDURE的权限默认授予的权限默认授予sysadmin固定服务器固定服务器角色成员、角色成员、db_owner和和db_ddladmin固定数据库角色成员。固定数据库角色成员。sysa

10、dmin固定服务器角色成员和固定服务器角色成员和db_owner固定数据固定数据库角色成员可以将库角色成员可以将CREATEPROCEDURE权限转让给其他用户。权限转让给其他用户。注意：存储过程的定义不能跨越批处理。注意：存储过程的定义不能跨越批处理。2存储过程的执行存储过程的执行通过通过EXEC命令可以执行一个已定义的存储过程。命令可以执行一个已定义的存储过程。语法格式：语法格式：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITH

11、RECOMPILE用户存储过程的创建与执行说明：说明：参数参数return_status为可选的整型变量，保存存储过程的返回状为可选的整型变量，保存存储过程的返回状态，态，EXECUTE语句使用该变量前，必须对其定义。参数语句使用该变量前，必须对其定义。参数procedure_name和和number用于调用已定义的一组存储过程中的某一用于调用已定义的一组存储过程中的某一个，个，procedure_name代表了存储过程的组名，代表了存储过程的组名，number用于指定组中的用于指定组中的存储过程。定义存储过程组的目的是以便用一条存储过程。定义存储过程组的目的是以便用一条DROPPROCEDU

12、RE语句删除一组存储过程，对过程分组后，不能删除组中的单个过程。语句删除一组存储过程，对过程分组后，不能删除组中的单个过程。参数参数procedure_name_var代表存储过程名。代表存储过程名。parameter为为CREATEPROCEDURE语句中定义的参数名；语句中定义的参数名；value为存储过程的实参；为存储过程的实参；variable为变量，用于保存为变量，用于保存OUTPUT参数返回的值。参数返回的值。DEFAULT关键关键字表示不提供实参，而是使用对应的默认值。字表示不提供实参，而是使用对应的默认值。n：表示实参可有多个。：表示实参可有多个。关键字关键字WITHRECOM

13、PILE指定强制编译。指定强制编译。存储过程的执行要注意下列几点：存储过程的执行要注意下列几点：(1)如果存储过程名的前三个字符为如果存储过程名的前三个字符为sp_，SQLServer会在会在Master数据库中寻找该过程。如果没能找到合法的过程名，数据库中寻找该过程。如果没能找到合法的过程名，SQLServer会寻找所有者名称为会寻找所有者名称为dbo的过程。的过程。(2)参数可以通过参数可以通过value或或parameter_name=value提供。提供。(3)执行存储过程时，若语句是批处理中的第一个语句，则不一定执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定要指定EXE

14、CUTE关键字。关键字。用户存储过程的创建与执行3存储过程的几种情况存储过程的几种情况(1)不使用任何参数的存储过程不使用任何参数的存储过程【例【例6.1】从】从XSCJ数据库的三个表中查询，返回学生学号、姓名、数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、课程名、成绩、学分。该存储过程不使用任何参数。学分。该存储过程不使用任何参数。/*创建存储过程创建存储过程*/CREATEPROCEDUREstudent_infoASSELECTa.学号学号,姓名姓名,课程名课程名,成绩成绩,t.学分学分FROMXSaINNERJOINXS_KCbONa.学号学号=b.学号学号INNERJOIN

15、KCtONb.课程号课程号=t.课程号课程号student_info存储过程可以通过以下方法执行：存储过程可以通过以下方法执行：EXECUTEstudent_info或者或者EXECstudent_info如果该过程是批处理中的第一条语句，则可使用：如果该过程是批处理中的第一条语句，则可使用：student_info用户存储过程的创建与执行(2)使用带参数的存储过程使用带参数的存储过程【例【例6.2】从】从XSCJ数据库的三个表中查询某人指定课程的成绩和学数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。分。该存储过程接受与传递参数精确匹配的值。CREATEP

16、ROCEDUREstudent_info1namechar(8),cnamechar(16)ASSELECTa.学号学号,姓名姓名,课程名课程名,成绩成绩,学分学分FROMXSaINNERJOINXS_KCbONa.学号学号=b.学号学号INNERJOINKCtONb.课程号课程号=t.课程号课程号WHEREa.姓名姓名=nameandt.课程名课程名=cnamestudent_info1存储过程有多种执行方式，下面列出了一部分：存储过程有多种执行方式，下面列出了一部分：EXECUTEstudent_info1王林王林,计算机基础计算机基础或者或者EXECUTEstudent_info1nam

17、e=王林王林,cname=计算机基础计算机基础或者或者EXECUTEstudent_info1cname=计算机基础计算机基础,name=王林王林或者或者EXECstudent_info1王林王林,计算机基础计算机基础或者或者EXECau_infocname=计算机基础计算机基础,name=王林王林用户存储过程的创建与执行(3)使用带有通配符参数的存储过程使用带有通配符参数的存储过程【例【例6.3】从三个表的联接中返回指定学生的学号、姓名、所选课程】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有名称及该课程的成绩。该存储过程在参

18、数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。提供参数，则使用预设的默认值。CREATEPROCEDUREst_infonamevarchar(30)=刘刘%ASSELECTa.学号学号,a.姓名姓名,c.课程名课程名,b.成绩成绩FROMXSaINNERJOINXS_KCbONa.学号学号=b.学号学号INNERJOINKCcONc.课程号课程号=b.课程号课程号WHERE姓名姓名LIKEnamest_info存储过程可以有多种执行形式，下面列出了一部分：存储过程可以有多种执行形式，下面列出了一部分：EXECUTEst_info/*参数使用默认值参数使用默认值*/或者或者EXEC

19、UTEst_info王王%/*传递给传递给name的实参为的实参为王王%*/或者或者EXECUTEst_info王张王张%(4)使用带使用带OUTPUT参数的存储过程参数的存储过程用户存储过程的创建与执行【例【例6.4】用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参】用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参数。数。CREATEPROCEDUREtotalcreditnamevarchar(40),totalintOUTPUTASSELECTtotal=SUM(KC.学分学分)FROMXS,XS_KC,KCWHERE姓名姓名=nameANDXS.学号

20、学号=XS_KC.学号学号andXS_KC.课程号课程号=KC.课课程号程号GROUPBYXS.学号学号注意：注意：OUTPUT变量必须在创建表和使用该变量时都进行定义。变量必须在创建表和使用该变量时都进行定义。定义时的参数名和调用时的变量名不一定要匹配，不过数据类型和参数位置必须匹。定义时的参数名和调用时的变量名不一定要匹配，不过数据类型和参数位置必须匹。DECLAREt_creditchar(20),totalintEXECUTEtotalcredit王林王林,totalOUTPUTSELECT王林王林,total(5)使用使用OUTPUT游标参数的存储过程游标参数的存储过程OUTPUT游

21、标参数用于返回存储过程的局部游标。游标参数用于返回存储过程的局部游标。【例【例6.5】在】在XSCJ数据库的数据库的XS表上声明并打开一个游标。表上声明并打开一个游标。CREATEPROCEDUREst_cursorst_cursorCURSORVARYINGOUTPUTASSETst_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMXSOPENst_cursor用户存储过程的创建与执行在如下的批处理中，声明一局部游标变量，执行上述存储过程过程并在如下的批处理中，声明一局部游标变量，执行上述存储过程过程并将游标赋值给局部游标变量，然后通过该游标变量读取

22、记录。将游标赋值给局部游标变量，然后通过该游标变量读取记录。DECLAREMyCursorCURSOREXECst_cursorst_cursor=MyCursorOUTPUTWHILE(FETCH_STATUS=0)BEGINFETCHNEXTFROMMyCursorENDCLOSEMyCursorDEALLOCATEMyCursor(6)使用使用WITHENCRYPTION选项选项WITHENCRYPTION子句对用户隐藏存储过程的文本。子句对用户隐藏存储过程的文本。【例【例6.6】创建加密过程，使用】创建加密过程，使用sp_helptext系统存储过程获取关于加系统存储过程获取关于加密过

23、程的信息，然后尝试直接从密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。表中获取关于该过程的信息。CREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT*FROMXS用户存储过程的创建与执行通过系统存储过程通过系统存储过程sp_helptext可显示规则、默认值、未加密的存储过程、可显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。用户定义函数、触发器或视图的文本。执行如下语句：执行如下语句：EXECsp_helptextencrypt_this结果集为提示信息：对象备注已加密。结果集为提示信息：对象备

24、注已加密。(7)创建用户定义的系统存储过程创建用户定义的系统存储过程【例【例6.7】创建一个过程，显示表名以】创建一个过程，显示表名以xs开头的所有表及其对应的索引。如开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以果没有指定参数，该过程将返回表名以kc开头的所有表及对应的索引。开头的所有表及对应的索引。USEmasterGOCREATEPROCEDUREsp_showtableTABLEvarchar(30)=kc%ASSELECTtab.nameASTABLE_NAME,inx.nameASINDEX_NAME,indidASINDEX_IDFROMsysindexesin

25、xINNERJOINsysobjectstabONtab.id=inx.idWHEREtab.nameLIKETABLEGOUSEXSCJEXECsp_showtablexs%GO用户存储过程的创建与执行4利用企业管理器编辑用户存储过程利用企业管理器编辑用户存储过程如果要通过企业管理器界面定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩，步骤如下：第第1步步在SQLServer企业管理器窗口中，选择相应的服务器、数据库和存储过程图标（本例选择XSCJ数据库），单击鼠标右键，出现如图6.1所示的快捷菜单。第第2步步选择新建存储过程，则出现编辑存储过程的属性窗口，在窗口中输入定义的存储过

26、程，如图6.2所示，然后选择“确定”按钮。图图6.1存储过程的快捷菜单存储过程的快捷菜单图图6.2编辑存储过程的属性窗口编辑存储过程的属性窗口 用户存储过程的修改使用ALTERPROCEDURE命令可修改已存在的存储过程并保留以前赋予的许可。语法格式：语法格式：ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING0=defaultOUTPUT,.n1WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n2说明：说明：各参数含义与C

27、REATEPROCEDURE相同。如果原来的过程定义是用WITHENCRYPTION或WITHRECOMPILE创建的，那么只有在ALTERPROCEDURE中也包含这些选项时，这些选项才有效。ALTERPROCEDURE权限默认授予sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员和过程的所有者且不可转让。用户存储过程的修改用ALTERPROCEDURE更改后，过程的权限和启动属性保持不变。【例【例6.8】对存储过程student_info1进行修改。USEXSCJGOALTERPROCEDUREstudent_info1namechar(8),c

28、namechar(16)ASSELECTa.学号,姓名,课程名,成绩,t.学分FROMXSaINNERjoinXS_KCbONa.学号=b.学号INNERJOINKCtONb.课程号=t.课程号WHEREa.姓名=nameandt.课程名=cnameGO【例【例6.9】创建名为select_students的存储过程，默认情况下，该过程可查询所有学生信息，随后授予权限。当该过程需更改为能检索计算机专业的学生信息时，用ALTERPROCEDURE重新定义该存储过程。用户存储过程的修改USEXSCJGOCREATEPROCEDUREselect_students/*创建存储过程*/ASSELECT

29、*FROMXSORDERBY学号GO修改存储过程select_studentsALTERPROCEDUREselect_studentsWITHENCRYPTIONASSELECT*FROMXSWHERE专业名=计算机ORDERBY学号GO用户存储过程的删除当不再使用一个存储过程时，就要把它从数据库中删除。使用DROPPROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。语法格式：语法格式：DROPPROCEDUREprocedure,.n说明：说明：procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除。默认情况下，将

30、DROPPROCEDURE权限授予过程所有者，该权限不可转让。db_owner和db_ddladmin固定数据库角色成员和sysadmin固定服务器角色成员可以通过在DROPPROCEDURE内指定所有者删除任何对象。若要查看过程名列表，可使用sp_help系统存储过程。若要显示过程定义（存储在syscomments系统表内），可使用sp_helptext。【例【例6.10】删除XSCJ数据库中的student_info1存储过程。USEXSCJGODROPPROCEDUREstudent_info16.2.1 利用SQL命令创建触发器SQL命令创建触发器命令创建触发器语法格式：语法格式：CR

31、EATETRIGGERtrigger_nameONtable|view/*指定操作对象*/WITHENCRYPTION/*说明是否采用加密方式*/FOR|AFTER|INSTEADOFINSERT,UPDATEWITHAPPENDNOTFORREPLICATION/*说明该触发器不用于复制*/ASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.n/*两个IF子句用于说明触发器执行的条件*/sql

32、_statement.n/*一条或若干条SQL语句*/6.2.1 利用SQL命令创建触发器说明：说明：参数trigger_name用于指定触发器名。触发器名必须符合标识符规则，并且在数据库中必须唯一，可以包含触发器所有者名。Table|view指在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以包含表或视图的所有者名。使用关键字WITHENCRYPTION可防止将触发器作为SQLServer复制的一部分发布。bitwise_operator为用于比较运算的位运算符。updated_bitmask为整型的位屏蔽码，与实际更新或插入的列对应，例如：表t包含列C0、C1、C2、C3和C4

33、。假定该表上有UPDATE触发器，若要检查列C0、C2、C4是否都有更新，可指定updated_bitmask的值为00010101=0 x15；若要检查是否只有列C1有更新，可指定updated_bitmask的值为000000102。comparison_operator为比较运算符；column_bitmask为列屏蔽码，用来检查是否已更新或插入了对应列。参数sql_statement为触发器的T-SQL语句，当执行DELETE、INSERT或UPDATE操作时，对应的触发器操作将生效。n表示触发器中可以包含多条T-SQL语句。2触发器中使用的特殊表触发器中使用的特殊表执行触发器时，系统

34、创建了两个特殊的逻辑表inserted表和deleted表，下面介绍一下这两个表的内容。inserted逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。6.2.1 利用SQL命令创建触发器deleted逻辑表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。修改一条记录等于插入一新记录，同时删除旧记录。当对定义了UPDATE触发器的表记录修改时，表中原记录移到deleted表中，修改过的记录插入到inserted表中。触发器可检查deleted表、inserted表及被修改的表。例如，若

35、要检索deleted、inserted表中的所有记录，可使用如下语句：SELECT*FROMdeletedSELECT*FROMinserteddeleted、inserted逻辑表的查询方法与数据库表的查询方法相同。3使用触发器的限制使用触发器的限制使用触发器有下列限制：(1)CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。(2)触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。(3)如果指定触发器所有者名限定触发器，要以相同的方式限定表名。(4)在同一CREATETRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的

36、触发器操作。6.2.1 利用SQL命令创建触发器(5)如果一个表的外键在DELETE、UPDATE操作上定义了级联，则不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。(7)在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。(8)触发器中不允许包含以下T-SQL语句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFI

37、GURE(9)触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用SETNOCOUNT语句以避免返回任何结果集。4权限权限CREATETRIGGER权限默认授予定义触发器的表所有者、sysadmin固定服务器角色成员、db_owner和db_ddladmin固定数据库角色成员，并且不可转让。6.2.1 利用SQL命令创建触发器【例【例6.11】对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。/*使用带有提示消息的触发器*/USEXSCJGOCREATETRIGGE

38、RreminderONXSFORINSERT,UPDATEASRAISERROR(4008,16,10)GO消息4008是sysmessages中的用户定义消息。有关创建用户定义消息的方法请参考附录的sp_addmessage存储过程。6.2.1 利用SQL命令创建触发器【例【例6.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。USEXSCJGOCREATETRIGGERcheck_trigONXS_KCFORINSERTASSELECT*FROMinsertedaWHEREa.学

39、号NOTIN(SELECTb.学号FROMXSb)ORa.课程号NOTIN(SELECTc.课程号FROMKCc)BEGINRAISERROR(违背数据的一致性.,16,1)ROLLBACKTRANSACTIONEND6.2.1 利用SQL命令创建触发器【例【例6.13】在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。通过调用COLUMNS_UPDATED()函数，可快速测试对学号列和课程号列修改所做的更改。USEXSCJGOCREATETRIGGERupdate_trigONXS_KCFORupdateAS/*检查学号列（C0）和课程号

40、列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。*/IF(COLUMNS_UPDATED()&3)0BEGINRAISERROR(违背数据的一致性.,16,1)ROLLBACKTRANSACTIONENDGO6.2.1 利用SQL命令创建触发器5.INSTEADOF触发器的设计触发器的设计如果视图的数据来自于多个基表，则必须使用INSTEADOF触发器支持引用表中数据的插入、更新和删除操作。例如，若在一个多表视图上定义了INSTEADOFINSERT触发器，视图各列的值可能允许为空也可能不允许为空，若视图某列的值不允许为空，则INSERT语句必须为该列提供相应的值。如果视图的列为以

41、下几种情况之一：（1）基表中的计算列。（2）IDENTITYINSERT为OFF的基表中的标识列。（3）具有timestamp数据类型的基表列。该视图的INSERT语句必须为这些列指定值，INSTEADOF触发器在构成将值插入基表的INSERT语句时会忽略指定的值。【例【例6.14】在XSCJ数据库中创建表、视图和触发器，以说明INSTEADOFINSERT触发器的使用。6.2.1 利用SQL命令创建触发器直接引用books表的INSERT语句不能为BookKey字段和.ComputedCol.字段提供值，例如：-正确的INSERT语句INSERTINTObooks(BookName,Colo

42、r,Pages)VALUES(计算机辅助设计,红色,100)-查看INSERT语句的执行结果:SELECT*FROMbooks-不正确的INSERT语句INSERTINTObooksVALUES(2,计算机辅助设计,红色,绿色,100)但对于引用View2视图的INSERT语句视图的每一列都指定值，例如：-对于视图View2，正确的INSERT语句INSERTINTOView2(BookKey,BookName,Color,ComputedCol,Pages)VALUES(4,计算机辅助设计,红色,绿色,100)-查看INSERT语句的执行结果SELECT*FROMView2在执行视图的插入语

43、句时，虽然将BookKey和ComputedCol字段的值传递到了InsteadTrig触发器，但触发器中的INSERT语句没有选择inserted表BookKey和ComputedCol字段的值。6.2.2 利用企业管理器创建触发器步骤如下：第第1步步展开服务器组，然后展开服务器。第第2步步展开“数据库”文件夹，展开将定义触发器的表所属的数据库，然后单击“表”文件夹。第第3步步选择将在其上创建触发器的表右击，出现快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，如图6.3所示。执行该命令后，进入如图6.4所示的界面。图图6.3企业管理器中定义触发器的快捷菜单企业管理器中定义触发器的

44、快捷菜单图图6.4企业管理器中定义触发器的界面企业管理器中定义触发器的界面第第4步步在“名称”中，单击“新建”，在“文本”框中输入触发器文本。若要检查语法，单击“检查语法”命令。触发器的修改1利用利用SQL命令修改触发器命令修改触发器语法格式：语法格式：ALTERTRIGGERtrigger_nameON(table|view)WITHENCRYPTION(FOR|AFTER|INSTEADOF)DELETE,INSERT,UPDATENOTFORREPLICATIONASsql_statement.n|(FOR|AFTER|INSTEADOF)INSERT,UPDATENOTFORREPLI

45、CATIONASIFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n触发器的修改【例【例6.15】修改XSCJ数据库中在XS表上定义的触发器reminder。USEXSCJALTERTRIGGERreminderONXSFORUPDATEASRAISERROR(“执行的操作是修改”,16,10)GO2.通过企业管理器修改触发器通过企业管理器修改触发器进入企业管理器，修

46、改触发器的步骤与创建的步骤相同，进入图6.2的界面后在“名称”对应的下拉表中选择要修改的触发器名即可进入触发器修改状态。触发器的删除【例【例6.16】删除触发器reminder。USEXSCJIFEXISTS(SELECTnameFROMsysobjectsWHEREname=reminderANDtype=TR)DROPTRIGGERreminderGO2通过企业管理器删除触发器通过企业管理器删除触发器进入图6.2的界面后在“名称”对应的下拉表中选择要删除的触发器名，然后选择“删除”按钮。习习题题1.试说明存储过程的特点及分类。2.举例说明存储过程的定义与调用。3.举例说明触发器的使用。1.利用利用SQL命令删除触发器命令删除触发器语法格式：语法格式：DROPTRIGGERtrigger,.n说明：说明：trigger：指要删除的触发器名称，包含触发器所有者名。n：表示可以指定多个触发器。