浅析数据库中触发器的应用.doc

嘉兴南洋职业技术学院毕业设计题目浅谈数据库中触发器的应用姓 名 陈慧俊 学号 _0622023 专业班级 电子信息工程06221 指导教师 _姚翠平 2009 年 5月 12 日1.摘要：客户服务器的应用逻辑设计是否合理，直接影响到客户服务器方式应用系统的处理性能。该文以SQL2000服务器平台为例，提出了通过合理划分的应用逻辑，以提高客户服务器系统处理性能的方法。2.关键词：客户服务器；触发器；结构化查询语言；数据完整性3.目录前言 1触发器的的定义1.1触发器的类型1.2触发器介绍1.3 触发器的优点1.4 触发器的应用领域15用友ERP U8数据库触发器的应用2触发器的实现2.1触发器的原理2.2触发器的语句2.3触发器在用友的案例3慎用触发器总结致谢参考文献1.前言80年代末到90年代初，许多应用系统从主机终端方式、文件共享方式向客户服务器方式过渡。客户服务器系统比文件服务器系统能提供更高的性能，因为客户机和服务器能将应用和处理分开，同时又共同实现其处理要求（即“分布式应用处理”）。服务器为多个客户管理数据库，而客户发送请求并分析从服务器接受到的数据集。在一个客户服务器应用中，数据库服务器是智能化的，它只封锁和返回一个客户请求的那些行，保证了并发性，使网络上的信息传输减到最少，因而可以改善系统性能。在客户服务器系统中，应用的处理是分布在网络上的，所以在设计客户方数据库应用程序时，若把过多的负担加在网络上，而没有充分利用数据库的存储过程，没有把网络访问最小化，那么应用程序要执行过多的网络IO，使网络饱和，从而降低了整个系统的性能。要开发好客户服务器应用系统，必须搞清如何在组成系统的各部分之间分布应用功能。 现代数据库管理系统广泛应用于办公自动化，其安全性越来越重要。虽然各种数据库系统提供了相当多的安全机制，比如访问控制、备份恢复、日志与审计等，但是黑客对数据库的攻击入侵事件还是很多，很常见的一种情况：通过缓冲区溢出等途径突破数据库自身提供的访问控制功能，提升用户权限，然后入侵数据库。SQL Server 2000数据库触发器在数据库系统中得到应用，提高了数据库系统的安全性。 触发器在系统开发上有着很广泛的用途，虽然触发器的用途很多，但是过多的使用触发器或者触发器写得不好，都将影响整个数据库的性能。因此，使用触发器的时候必须权衡各方面的因素，在适当的时候使用恰当的触发器。1触发器的的概述1.1触发器的的定义 在SQL中，触发器是一种特殊类型的存储过程，它不同于SQL的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：（1）强化约束（Enforce restriction） 触发器能够实现比CHECK 语句更为复杂的约束。 （2）跟踪变化（Auditing changes） 触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。（3）级联运行（Cascaded operation）。触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作（如删除，更新，插入）而该操作又导致该表上触发器被触发。（4）存储过程的调用（Stored procedure invocation）。为了响应数据库更新触，发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS（ 数据库管理系统）本身之外进行操作。 由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外一个表的同一类型（INSERT、 UPDATE、 DELETE）的多个触发器能够对同一种数据操作采取多种不同的处理。总体而言，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。1.2触发器的类型 SQL Server 2000 支持两种类型的触发器：AFTER 触发器和INSTEAD OF 触发器。其中AFTER 触发器即为SQL Server 2000 版本以前所介绍的触发器。该类型触发器要求只有执行某一操作（INSERT UPDATE DELETE） 之后，触发器才被触发，且只能在表上定义。可以为针对表的同一操作定义多个触发器。对于AFTER 触发器，可以定义哪一个触发器被最先触发，哪一个被最后触发，通常使用系统过程来完成此任务INSTEAD OF 触发器表示并不执行其所定义的操作（INSERT、 UPDATE、 DELETE），而仅是执行触发器本身。既可在表上定义INSTEAD OF 触发器，也可以在视图上定义INSTEAD OF 触发器，但对同一操作只能定义一个INSTEADOF触发器。按构成触发器的基本器件不同分为：双极型触发器和MOS型触发器。1.3触发器的优点 触发器可通过数据库中的相关表实现级联更改；不过，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。 数据库触发器的使用完全性约束，数据完整性是指服务器上的数据正确并且完整。所有客户端应用程序必须遵循一系列预先定义的数据完整性规则和业务规则，以保证所有数据库数据是合法的。可以使用2种方法来实施一个简单的完整性规则：让应用程序执行完整性检查；使用数据库的完整性约束。 （1）让应用程序执行完整性检查任何一个订单的顾客号必须是客户机表中的客户机号。这是一个最简单的应用完整性，可以用应用程序本身执行完整性检查。 1.4触发器的应用领域现代数据库管理系统广泛应用于办公自动化，其安全性越来越重要。虽然各种数据库系统提供了相当多的安全机制，比如访问控制、备份恢复、日志与审计等，但是黑客对数据库的攻击入侵事件还是很多，很常见的一种情况：通过缓冲区溢出等途径突破数据库自身提供的访问控制功能，提升用户权限，然后入侵数据库。SQL Server 2000数据库触发器在数据库系统中得到应用，提高了数据库系统的安全性。 15用友ERP U8数据库触发器的应用 用友软件ERP U8是以CS（服务器与客户端）架构为基础建立的一整套软件系统。用友ERP U8系列以数据库为基础，通过在前台软件的操作，把数据直接附加到数据库中，当前台无法对错误操作进行修改时就可以再数据库中对相应的表，字段信息进行修改或使用SQL语句进行修改。但是在有些客户的数据资料很大的时候，比如秦山核电站，正大青春宝，浙江绿城等大型的集团公司，当他们的数据量达到上百G之后如果其中的数据丢失导致无法在前台在拥有U8软件惊醒正常的操作的时候就必须使用到触发器，使用触发器可以更好更快的保持数据的完整性，对数据的缺失的表进行修改，通过触发器SQL语句，把很多表的信息进行添加，删除，修改，因为工作量大，普通的使用数据库已无法满足如此多的信息量的修改，所以使用数据库能更好更快更效率的完成保持数据的完整性。2触发器的实现2.1触发器的原理触发器一种特殊类型的存储过程，当使用Insert、Delete、Update语句对数据库表中的数据进行操作时，触发器将自动执行。触发器的工作原理，它是触发器与表紧密联系在一起，该表也称为触发器表。在对表进行插入、删除和更新操作时，若该表有相应操作类型的触发器，则触发器会自动触发执行。程序开发人员利用其工作原理来设计了一种新的机制，就是把数据库系统提供的访问控制功能作为第一层的安全保护，再构建一层访问控制机制作为第二层安全控制机制。我们的主要思想是当用户突破第一层安全保护进入数据库时，再启用第二层的安全保护机制，第二层的安全保护就是控制用户对数据库的操作权限。也就是说，你可以提升权限进入数据库，但是你要操作数据库还不行，你得突破第二层安全保护取得操作权限。因此，这种机制也就能够有效的防止用户的恶意篡改数据。触发器是绑定在表上的表达式，当表中的任何记录被指定的操作命令修改时，触发器被激活。当数据修改时，触发器可执行数据库应用程序要求的任何副操作。2.2触发器的语句数据库作为存放数据的仓库，在许多情况下，是为了提供信息检索服务的。通常的做法是通过为用户设定Select权限来保证数据库表中的数据不被修改，这对没有相应权限的用户可以起到保护作用。但是，如果用户通过攻击手段提升权限后就可以进行非法的修改操作。在实际的系统设计中，通过使用触发器，把对数据库表中的记录的修改操作屏蔽掉。在下面的触发器中，只给出了提示信息，省去了触发器中定义的具体操作的语句。CREATE TRIGGER tr_ScoreQueryON ScoreQueryINSTEAD OF INSERT，DELETE，UPDATEAS PRINT非法用户，禁止修改操作!Begin Insert into log_table(log_user,db_name,event_name,log_time) values(sys.login_user,sys.database_name,sys_sysevent,sysdate);-触发器定义的其他具体操作End;GOGO2.插入删除更新操作频繁的数据库表如果要对数据库中插入、删除或更新记录，就必须先删除触发器，这给数据库管理员带来许多不便。因此，对于考试系统中用户管理子系统这一类型的修改操作比较频繁的数据库，我们不能像上面那样，不管什么用户的修改操作一律都屏蔽掉，而是应该根据用户权限有所不同。可以通过自定义用户权限表来判断用户操作的合法性，对于不合法的予以屏蔽。自定义用户权限表包括四个字段：user_id、Insert_of、Delete_of、Update_of，它们的数据类型分别为：smallint、bit、bit、bit。其中bit类型中1表示用户有权限，0表示用户没有权限。CREATE TABLE UserRight( user_id smallint,Insert_of bit,Delete_of bit,Update_of bit)在数据库系统运行过程中，可以使用全局函数CURRENT_USER()来获取当前的用户名，赋值给currentuser,之后再使用全局函数USER_ID(currentuser)获取当前用户的ID。一个屏蔽非法操作的DELETE触发器设计如下：CREATE TRIGGERtr_UserManageON UserManageAFTER DELETEAS IF (SELECT Delete_of from UserRight WHERE user_id=USER_ID(CURRENT_USER)!=1)ROLLBACK TRANSACTION其他的操作GO一些表的某些字段、记录值被莫名修改啦，删除啦，可以用这种触发器来记录具体操作的机器啊、程序名称啊！可以界定到底是谁干的“坏事”！呵呵sql触发器获取机器名、IP和程序名称方法declare host as nchar(30) declare program_name as nchar(30)declare ip as nchar(30)select host=hostname,program_name=program_name from master.sysprocesses where spid=spid create table #temp_ip(id int identity(1,1),re varchar(200) declare s varchar(1000) set s=ping email=+host+/email -a -n 1 -l 1 insert #temp_ip(re) exec master.xp_cmdshell s select ip=stuff(left(re,charindex(,re)-1),1,charindex(,re),) from #temp_ip where id=2 drop table #temp_ip print hostprint program_nameprint ip 2.3触发器在用友的案例一、业务背景利用触发器同步增加两个帐套的供应商档案最近有客户咨询自己有多个帐套，并且每个帐套的基础档案都是一样的，是不是可以在一个帐套里面增加或修改基础档案（比如：供应商档案、客户档案等）信息后，可以在另外一个帐套中自动增加、修改。提高客户在软件使用时的工作效率我想了一个方法，就是利用SQL数据库的触发器功能来实现.1、首先利用两个帐套，ufdata_006_2008,ufdata_888_2006，并且在两个帐套里面填写相同的供应商分类2、在ufdata_006_2008帐套中对供应商表建立触发器，当006帐套增加或修改时，可以同步对888帐套中供应商档案进行增加或修改1、向会计科目、供应商、客户分类不建议用此功能，因为涉及到末级标志的问题 2、这个语句里面没有包含同步删除基础档案的部分，主要考虑各个帐套引用基础档案的情况不一样，所以没有加入 3、当建立新年度帐后，要在新年度帐里面重新建立此语句，因为年度帐建立时肯定不会把语句带到新年度 4、当用友软件升级时，建议把语句先删除在对用友软件升级 5、建议这个语句是否给客户，还要看客户的具体数据和客户操作数据库水平而定3慎用触发器慎用触发器 触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用呢。触发器本身没有过错，但由于我们的滥用会造成数据库及应用程序的维护困难。在数据库操作中，我们可以通过关系、触发器、存储过程、应用程序等来实现数据操作 同时规则、约束、缺省值也是保证数据完整性的重要保障。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程序.总结 使用触发器能保证客户端与服务器数据的完整性，客户服务器应用系统性能的方法，主要是采用合理分布处理逻辑于客户端与服务器端，并充分利用数据库服务器来提高执行速度。该方法的有效性已得到证实。此外，在提高性能的措施和方法中，SQL语句的查询优化问题也不可忽视。综合上述两种方法，才能从根本上提高客户服务器应用系统的性能。致谢我真诚地感谢所有在我完成论文的过程中给予我帮助,支持和鼓励的老师们,同学们.非常感谢我的指导老师姚老师,是他在忙碌的教学工作中挤出时间来修改、批阅我的论文,一针见血地指出我的不足,并给我提供了很多参考意见。.在我搜集查阅资料时给我的帮助