触发器-存储过程-数据库安全.doc

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

1、基于SQL SERVER触发器技术的实现时间：2006-6-12 上午 07:51:28来源：DZY学习资料库阅读946次 在数据库管理系统中，如何保证数据库中的数据完整性是一项重要的课题。数据完整性是指存储在数据库的数据的一致性。主要体现在以下几个方面：实体完整性（EntityIntegrity）、域完整性（DomainIntegrity）、参照完整性（ReferentialIntegrity）和用户的自定义完整性（UserdefineIntegrity）。目前，已有多种方法来解决这个问题。从最基本的数据类型，到多种形式的约束条件，虽然都提出了数据完整性的解决方案，但由于这些方法较为简单，不

2、能解决比较复杂的数据完整性问题。而触发器（Trigger）作为一种高级的技术，可以轻松地解决任何有关保证数据完整性的问题。一、在SQLSERVER环境中使用触发器1触发器的工作原理触发器是一种特殊类型的存储过程，它与表紧密联系在一起，在对表进行插入、删除和更新时，如该表（也称触发器表）有相应操作类型的触发器，则触发器便会自动触发执行。触发器分为INSERT触发器、DELETE触发器和UPDATE触发器3类。当向触发器表中插入数据时，INSERT触发器将触发执行，新的记录会增加到触发器表和inseted表中；当删除触发器表中的数据时，DELETE触发器将触发执行，被删除的记录会存放到delete

3、d表中；当更新触发器表中的数据时，相当于插入一条新记录和删除一条旧记录，此时UPDATE触发器将触发执行，表中原有的记录存放到deleted表中，修改后的记录插入到inserted表中。其中inserted表和deleted表是两个逻辑表，由系统来维护，不允许用户直接对这两个表进行修改。它们存放于内存中，不存放在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器工作完成后，与该触发器相关的这两个表也会被删除。deleted表用于存储SQL语言中DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。

4、deleted表和触发器表通常没有相同的行；inserted表用于存储SQL语言中INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。inserted表中的行是触发器表中新行的副本。2触发器的实现步骤在笔者开发的教材管理系统中，建立了一个教材数据库JCSJK，其中有教材表JCB和教材进出明细表MXB，需要在对MXB进行插入、删除和修改时，动态地修改JCB中对应教材的库存数量。下面举例说明触发器的实现步骤。为减少篇幅，对表结构作了简化处理。以下操作在MicrosoftSQLSERVER环境中完成。(1)建立教材表JCB，并

5、定义主键CREATETABLEdbo.JCB(教材代码char(10)NOTNULL,教材名char(30)NOTNULL,价格decimal(18,2)NOTNULL,库存量intNULL,出版社char(20)NULL)ONPRIMARYALTERTABLEdbo.JCBWITHNOCHECKADDCONSTRAINTPKJCBPRIMARYKEYCLUSTERED(教材代码)ONPRIMARY(2)建立教材进出明细表MXB，并定义主键和外?brCREATETABLEdbo.MXB(教材代码char(10)NOTNULL,日期datetimeNOTNULL,教材进intNULL,教材出in

6、tNULL,备注char(40)NULL)ONPRIMARYALTERTABLEdbo.MXBWITHNOCHECKADDCONSTRAINTPKMXBPRIMARYKEYCLUSTERED(教材代码,日期)ONPRIMARYALTERTABLEdbo.MXBADDCONSTRAINTFKMXBJCBFOREIGNKEY(教材代码)REFERENCESdbo.JCB(教材代码)ONDELETECASCADEONUPDATECASCADE图1显示了JCB和MXB两个表的关系。(3)在MXB上建立INSERT触发器CREATETRIGGERMXBINSEONdbo.MXBFORINSERTASUP

7、DATEJCBSET库存量=库存量+(SELECT教材进-教材出FROMINSERTED)FROMJCB,INSERTEDWHEREJCB.教材代码=INSERTED.教材代码(4)在MXB上建立DELETE触发器CREATETRIGGERMXBDELEONdbo.MXBFORDELETEASUPDATEJCBSET库存量=库存量-(SELECT教材进-教材出FROMDELETED)FROMJCB,DELETEDWHEREJCB.教材代码=DELETED.教材代码HT(5)在MXB上建立UPDATE触发器CREATETRIGGERMXBUPDAONdbo.MXBFORUPDATEASBEGIN

8、UPDATEJCBSET库存量=库存量-(SELECT教材进-教材出FROMDELETED)FROMJCB,DELETEDWHEREJCB.教材代码=DELETED.教材代码UPDATEJCBSET库存量=库存量+(SELECT教材进-教材出FROMINSERTED)FROMJCB,INSERTEDWHEREJCB.教材代码=INSERTED.教材代码END通过以上步骤，设置了MXB表的3类触发器，当用户对MXB表进行插入、删除和修改时，将根据MXB中教材进出的情况动态地修改JCB中对应教材的库存量。由于在触发器中，涉及到的inserted表和deleted表均存放在内存中，因此，触发器的执行

9、速度较快。3设计触发器的考虑在写触发器代码时需要考虑的一个重要问题就是，引发触发器的语句可以是一个影响单行的语句，也可以是一个影响多行的语句。这在UPDATE和DELETE触发器中很常见，因为这些语句经常作用于多行。而这在INSERT触发器中就比较少见，因为基本的INSERT语句只添加一行。然而，由于INSERT触发器可由INSERTINTO(table_name)SELECT语句激发，所以，插入许多行可能导致单个的触发器调用。上面讨论的涉及MXB的3类触发器都是针对影响单行的语句。因此，有必要考虑影响多行的语句，这里对MXB的INSERT触发器进行讨论。(1)可处理多行的MXB上的INSER

10、T触发器如果要进行多行插入，上面示例中的触发器可能就不能正确处理，因为UPDATE语句赋值表达式右边的表达式只能是一个值，而不能是一个值列表。因此，该触发器的作用就是获取inserted表中任意一行的值，并将其添加到JCB表中特定教材代码值的已有库存量值上。如果某个教材代码值在inserted表中出现了多次，则可能无法得到预期的结果。为了正确地更新JCB表，触发器就必须适应inserted表中出现多行的可能性。这可以通过SUM函数实现，它为inserted表中每个教材代码计算教材进出的总计。SUM函数存放于相关子查询中（SELECT语句在括号内）。该子查询为inserted表中与JCB表的教材

11、代码匹配或相关的每个教材代码返回一个单一值。CREATETRIGGERMXBINSEONdbo.MXBFORINSERTASUPDATEJCBSET库存量=库存量+(SELECTSUM(教材进-教材出)FROMINSERTEDWHEREJCB.教材代码=INSERTED.教材代码)WHEREJCB.教材代码IN(SELECT教材代码FROMINSERTED)该触发器对单行插入同样适用，不过，使用该触发器时，WHERE子句中所使用的相关子查询和IN运算符需要额外处理，而这对于单行插入来说是不必要的。(2)可区分单行和多行插入的MXB上的INSERT触发器可以通过系统函数ROWCOUNT以区分单行

12、插入和多行插入，以使触发器针对不同行数使用最优方法。CREATETRIGGERMXB_INSEONdbo.MXBFORINSERTASIFROWCOUNT=1BEGINUPDATEJCBSET库存量=库存量+(SELECT教材进-教材出FROMINSERTED)FROMJCB,INSERTEDWHEREJCB.教材代码=INSERTED.教材代码ENDELSEBEGINUPDATEJCBSET库存量=库存量+(SELECTSUM(教材进-教材出)FROMINSERTEDWHEREJCB.教材代码=INSERTED.教材代码)WHEREJCB.教材代码IN(SELECT教材代码FROMINSER

13、TED)END二、结论触发器应用于支持企业级商业解决方案时，是一个功能十分强大的工具。它可以用于实现业务规则，可以检查事务，可以在同一表上创建多个触发器来分离代码的功能。另外，通过使用触发器收集的信息，可以提高数据库的性能，可以用来维护那些使用外键所不能实现的复杂参数完整性。因此，对于维护数据表之间一致性，保持数据的相关完整性的情况，触发器应作为首先考虑的技术。储过程中返回结果从存储过程中返回结果有三种方式：1、返回结果集这是客户端应用程序返回结果的最通用的方法。结果集是通过使用SELECT语句选择数据产生的。结果集可以从永久表、临时表或局部变量中产生。将结果返回到另一个存储过程不是一种有效的

14、方法。存储过程不能访问另一个存储过程建立的结果集。例如从永久表中返回结果集：USEpubsGOCREATEPROCEDUREap_CreateResultFromPermtableASSELECTau_inameFROMauthorsGO例如从局部变量中创建结果集：USEpubsGOCREATEPROCEDUREap_CreateResultFromVariableASDECLAREau_inamechar(20)SELECTau_iname=au_inameFROMauthorsWHEREau_id=172-32-1176SELECTau_idGO2、设置OUTPUT参数的值输出参数经常用来

15、从存储过程中检索出结果。如果某个参数在传输到存储过程中时被定义成OUTPUT，则对该参数的任何修改在退出存储之后仍然有效。例如：USEpubsGOCREATEPROCEDUREap_SetOutputVarcountintegerOUTPUTASSELECTcount=count(*)FROMauthorsGO从输出参数中检索出值：USEpubsGOCREATEPROCEDUREap_GetOutputVarASDECLAREnumintegerEXECUTEap_SetOutputVarnumOUTPUTPRINT“thecountis”+convert(char,num)GO将游标使用成O

16、UTPUT参数。游标可以使用OUTPUT（输出）参数，但不能使用成输入参数。也就是说，游标可以作为结果返回，但却不能传输到过程中去。当游标被用作参数时，需要限定其为OUTPUT和VARYING。VARYING关键字指出该结果集要用来支持输出参数。这样就提供了将结果集返回到调用过程的能力。例如：USEpubsGOCREATEPROCEDUREGetTitleCountcount_cursorCURSORVARYINGOUTPUTASSETcount_cursor=CURSORFORSELECTau_id,count(*)FROMtitleauthorsGROUPBYau_idOPENcount_

17、cursorGO3、通过RETURN参数返回状态这是一种从存储过程返回错误码的方法。存储过程总是返回一个状态值，用户也可以使用RETURN语句返回自己的状态。例如：USEpubsGOCREATEPROCEDUREap_SetReturnStatusASDECLAREcountintegerSELECTcount=count(*)FROMauthorsIFcount=0RETURN(1)ELSERETURN(0)GO例如检索出返回的状态：USEpubsGOCREATEPROCEDUREap_GetReturnStatusASDECLAREstatusintegerEXECUTEstatus=ap

18、_SetReturnStatusIFstatus=1PRINT“Norowsfound”ELSEPRINT“successful”GO在存储过程中进行错误处理如同其它程序一样，在存储过程中进行错误处理是非常重要的。系统变更error在执行每一个TransactSQL语句之后都会得到一个值。对于成功的执行，error的值为0，如果出现错误，则error中将包含错误信息。error系统变量对存储过程的错误处理是非常重要的。注意：为了防止错误，error所能设置的值在sysmessages表的“error”中反映了出来。在存储过程中的错误有两种类型：1、数据库相关的错误这些错误是由数据库的不一致性引

19、起的，系统使用非0的error值表示特定的数据库问题。在TransactSQL执行之后，可以通过error获得所出现的错误。如果发现error不为0，则必须采取必要的行动，大多数情况下，存储将不再继续进行处理而返回。下面的示例展示了典型的获取数据库错误的方法。该过程将错误代码放置到输出变量中，这样，调用程序就能够访问到。USEpubsGOCREATEPROCEDUREap_TrapDatabaseErrorreturn_codeintegerOUTPUTASUPDATEauthorsSETau_iname=“Jackson”WHEREau_iname=“Smith”IFerror0BEGINS

20、ELECTreturn_code=errorRETURNENDELSEreturn_code=0GO2、业务逻辑错误这些错误是由于违反了业务规则而引起的。要获取这些错误，首先需要定义业务规则，基于这些规则，需要在存储过程中增加必要的错误检测代码。人们经常使用RAISERROR语句通报这些错误。RAISERROR提供了返回用户定义错误及将error变量设置成用户定义错误号的能力。错误消息可以被动态地建立，或者基于错误号从“sysmessages”表中检索到。一旦出现了错误，错误就会以一种服务器错误消息的方式返回到客户机。下面是RAISERROR命令的语法：RAISERROR(msg_id|msg

21、_str,severity,state,argument,n)WITHoptionsMsg_id指明用户定义消息的id，该消息存储在“sysmessages”系统表中。Msg_str用于动态创建消息的消息字符串。这与C语言中的“printf”非常相似。Severity定义用户赋值的错误消息严重程度。State是从1到127的任意整数值，它表示错误的调用状态信息。负数的state值将缺省为1。OPTIONS指明错误的定制选项。OPTIONS的有效值如下：1）LOG。将错误记录到服务器错误日志和NT事件日志中。该选项需要消息带有从19到25的严重程度。而只有系统管理员才能发出这种消息。2）NOWA

22、IT。将消息立即发送到客户端服务器。3）SETERROR。不管其严重级别如何，将error的值设置为msg_id或5000。远程过程调用SQLServer提供了调用驻留在不同服务器上的存储过程的能力。调用这样的存储过程称谓远程存储过程调用。为了使得调用能从一个SQLServer转移到另一个服务器，两个服务器应该相互定义成对方的有效远程服务器。设置远程服务器的配置：扩展某个服务器的组。右击该服务器并点击“Properties”。设置选项“AllowotherSQLServerstoconnectremotelytothisSQLserverviaRPC”。设置“Querytimeout”选项的值

23、，该值指定从一个查询处理返回所能等待的秒数。缺省值为0，表示允许无限的等待时间。设置完成配置选项之后，点击“OK”。重新启动服务器之后，修改将会生效。在另一台远程服务器上重复相同的步骤。调用远程存储过程需要指明服务器的名称，后带数据库的名称和拥有者的名称。下面是在不同的服务器（Server2）上调用一个存储过程的示例。Execserver2.pubs.dbo.myproc豆豆的后话：这里只是粗浅的介绍了SQLServer常用的知识，对象也是基于SQLServer数据库编写应用程序的编程人员，而非数据库管理者。但对于应用程序编程者，了解数据库的管理也是非常有用的。建议在以后的时间自行去了解数据库

24、的管理，这对于优化程序也是相当有用的。查看锁信息createtable#t(req_spidint,obj_namesysname)declaresnvarchar(4000),ridint,dbnamesysname,idint,objnamesysnamedeclaretbcursorforselectdistinctreq_spid,dbname=db_name(rsc_dbid),rsc_objidfrommaster.syslockinfowherersc_typein(4,5)opentbfetchnextfromtbintorid,dbname,idwhilefetch_stat

25、us=0beginsetobjname=namefrom+dbname+.sysobjectswhereid=idexecsp_executesqls,Nobjnamesysnameout,idint,objnameout,idinsertinto#tvalues(rid,objname)fetchnextfromtbintorid,dbname,idendclosetbdeallocatetbselect进程id=a.req_spid,数据库=db_name(rsc_dbid),类型=casersc_typewhen1thenNULL资源（未使用）when2then数据库when3then文

26、件when4then索引when5then表when6then页when7then键when8then扩展盘区when9thenRID（行ID)when10then应用程序end,对象id=rsc_objid,对象名=b.obj_name,rsc_indidfrommaster.syslockinfoaleftjoin#tbona.req_spid=b.req_spidgodroptable#tTransact-SQL参考sysobjects在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在tempdb内，每个临时对象才在该表中占一行。列名数据类型描述name

27、sysname对象名。Idint对象标识号。xtypechar(2)对象类型。可以是下列对象类型中的一种：C=CHECK约束D=默认值或DEFAULT约束F=FOREIGNKEY约束L=日志FN=标量函数IF=内嵌表函数P=存储过程PK=PRIMARYKEY约束（类型是K）RF=复制筛选存储过程S=系统表TF=表函数TR=触发器U=用户表UQ=UNIQUE约束（类型是K）V=视图X=扩展存储过程uidsmallint所有者对象的用户ID。infosmallint保留。仅限内部使用。statusint保留。仅限内部使用。base_schema_verint保留。仅限内部使用。replinfoin

28、t保留。供复制使用。parent_objint父对象的对象标识号（例如，对于触发器或约束，该标识号为表ID）。crdatedatetime对象的创建日期。ftcatidsmallint为全文索引注册的所有用户表的全文目录标识符，对于没有注册的所有用户表则为0。schema_verint版本号，该版本号在每次表的架构更改时都增加。stats_schema_verint保留。仅限内部使用。typechar(2)对象类型。可以是下列值之一：C=CHECK约束D=默认值或DEFAULT约束F=FOREIGNKEY约束FN=标量函数IF=内嵌表函数K=PRIMARYKEY或UNIQUE约束L=日志P=存

29、储过程R=规则RF=复制筛选存储过程S=系统表TF=表函数TR=触发器U=用户表V=视图X=扩展存储过程userstatsmallint保留。sysstatsmallint内部状态信息。indexdelsmallint保留。refdatedatetime留作以后使用。versionint留作以后使用。deltrigint保留。instrigint保留。updtrigint保留。seltrigint保留。categoryint用于发布、约束和标识。cachesmallint保留。查询指定表在那些数据库中存在declaretbnamesysnameset客户资料declaredbnamesysna

30、me,sqlnvarchar(4000),rebit,sql1varchar(8000)setsql1=declaretbcursorforselectnamefrommaster.sysdatabasesopentbfetchnextfromtbintodbnamewhilefetch_status=0beginsetre=casewhenexists(select1from+dbname+.sysobjectswherextype=Uandname=+tbname+)then1else0endexecsp_executesqlsql,Nrebitout,reoutifre=1setsql1

31、=sql1+unionallselect+dbname+fetchnextfromtbintodbnameendclosetbdeallocatetbsetsql1=substring(sql1,12,8000)exec(sql1)比较两个数据库的表结构差异execp_comparestructurexzkh_model,xzkh_new-*/ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.p_comparestructure)andOBJECTPROPERTY(id,NIsProcedure)=1)dropprocedured

32、bo.p_comparestructureGOcreateprocp_comparestructuredbname1varchar(250),-要比较的数据库名1dbname2varchar(250)-要比较的数据库名2ascreatetable#tb1(表名1varchar(250),字段名varchar(250),序号int,标识bit,主键bit,类型varchar(250),占用字节数int,长度int,小数位数int,允许空bit,默认值varchar(500),字段说明varchar(500)createtable#tb2(表名2varchar(250),字段名varchar(25

33、0),序号int,标识bit,主键bit,类型varchar(250),占用字节数int,长度int,小数位数int,允许空bit,默认值varchar(500),字段说明varchar(500)-得到数据库1的结构exec(insertinto#tb1SELECT表名=d.name,字段名=a.name,序号=a.colid,标识=casewhena.status=0x80then1else0end,主键=casewhenexists(SELECT1FROM+dbname1+.sysobjectswherextype=PKandnamein(SELECTnameFROM+dbname1+.s

34、ysindexesWHEREindidin(SELECTindidFROM+dbname1+.sysindexkeysWHEREid=a.idANDcolid=a.colid)then1else0end,类型=b.name,占用字节数=a.length,长度=a.prec,小数位数=a.scale,允许空=a.isnullable,默认值=isnull(e.text,),字段说明=isnull(g.value,)FROM+dbname1+.syscolumnsaleftjoin+dbname1+.systypesbona.xtype=b.xusertypeinnerjoin+dbname1+.

35、sysobjectsdona.id=d.idandd.xtype=Uandd.namedtpropertiesleftjoin+dbname1+.syscommentseona.cdefault=e.idleftjoin+dbname1+.syspropertiesgona.id=g.idanda.colid=g.smallidorderbya.id,a.colorder)-得到数据库2的结构exec(insertinto#tb2SELECT表名=d.name,字段名=a.name,序号=a.colid,标识=casewhena.status=0x80then1else0end,主键=case

36、whenexists(SELECT1FROM+dbname2+.sysobjectswherextype=PKandnamein(SELECTnameFROM+dbname2+.sysindexesWHEREindidin(SELECTindidFROM+dbname2+.sysindexkeysWHEREid=a.idANDcolid=a.colid)then1else0end,类型=b.name,占用字节数=a.length,长度=a.prec,小数位数=a.scale,允许空=a.isnullable,默认值=isnull(e.text,),字段说明=isnull(g.value,)FR

37、OM+dbname2+.syscolumnsaleftjoin+dbname2+.systypesbona.xtype=b.xusertypeinnerjoin+dbname2+.sysobjectsdona.id=d.idandd.xtype=Uandd.namedtpropertiesleftjoin+dbname2+.syscommentseona.cdefault=e.idleftjoin+dbname2+.syspropertiesgona.id=g.idanda.colid=g.smallidorderbya.id,a.colorder)-andnotexists(select1f

38、rom#tb2where表名2=a.表名1)select比较结果=casewhena.表名1isnullandb.序号=1then库1缺少表：+b.表名2whenb.表名2isnullanda.序号=1then库2缺少表:+a.表名1whena.字段名isnullandexists(select1from#tb1where表名1=b.表名2)then库1+b.表名2+缺少字段：+b.字段名whenb.字段名isnullandexists(select1from#tb2where表名2=a.表名1)then库2+a.表名1+缺少字段：+a.字段名whena.标识b.标识then标识不同whena

39、.主键b.主键then主键设置不同whena.类型b.类型then字段类型不同whena.占用字节数b.占用字节数then占用字节数whena.长度b.长度then长度不同whena.小数位数b.小数位数then小数位数不同whena.允许空b.允许空then是否允许空不同whena.默认值b.默认值then默认值不同whena.字段说明b.字段说明then字段说明不同elseend,*from#tb1afulljoin#tb2bona.表名1=b.表名2anda.字段名=b.字段名wherea.表名1isnullora.字段名isnullorb.表名2isnullorb.字段名isnullo

40、ra.标识b.标识ora.主键b.主键ora.类型b.类型ora.占用字节数b.占用字节数ora.长度b.长度ora.小数位数b.小数位数ora.允许空b.允许空ora.默认值b.默认值ora.字段说明b.字段说明orderbyisnull(a.表名1,b.表名2),isnull(a.序号,b.序号)-isnull(a.字段名,b.字段名)gosql系统存储过程xp_cmdshell使用xp_cmdshell以操作系统命令行解释器的方式执行给定的命令字符串，并以文本行方式返回任何输出。授予非管理用户执行xp_cmdshell的权限。说明在Microsoft?Windows?95或MicrosoftWindows98操作系统中执行xp_cmdshell时，将不把xp_cmdshell的返回代码设置为唤醒调用的可执行文件的进程退出代码。返回代码始终为0。语法xp_cmdshellcommand_string,no_output参数command_string是在操作系统命令行解释器上执行的命令字符串。command_string的数据类型为varchar(255)或nvarchar