利用触发器自动记录数据的变化.doc

利用触发器自动记录数据的变化 一、 需求的产生在以数据为主的企业应用中，有时需要记录敏感数据的变化。比如有关职工的个人信息，需要保证信息的准确性和安全性，不能够随意更改，每次的更改都要详细的记录下修改时间，修改前后字段的内容，操作员等。下面让我们看看如何在SQL SERVER数据库上实现这个功能。我的应用环境是WinForm的C/S结构，没有测试其他环境。二、 如何有效的保存数据变化产生的信息 如果另外新建一个和原数据表结构一样的数据表用来保存数据的变化，这显然不是一个好主意，数据冗余量大，不利于以后的查看，并且不能记录额外的信息。我们可以设计一个通用的数据表结构来保存数据的变化信息：CREATE TABLE dbo.DataRecorder( ID int IDENTITY(1,1) NOT NULL, TableName nvarchar(50) NOT NULL, KeyValue varchar(50) NOT NULL, FieldName nvarchar(50) NOT NULL, OldValue nvarchar(500) NULL, NewValue nvarchar(500) NULL, RecordDate smalldatetime NOT NULL CONSTRAINT DF_LIIInsuInfo_RecordDateDEFAULT (getdate(),Recorder nvarchar(20) NOT NULL)字段的说明：TableName指存在数据变化的数据表名称，既然是通用的表，就要区分是记录的哪个表。KeyValue 指存在数据变化的数据表中的关键字段值，用于知道记录的是哪条记录。FieldName指哪个字段存在数据变化。OldValue记录数据变化前的值，重要，如果想改回以前的值，这个必须要记录。NewValue记录数据变化后的新值。Recorder记录是谁操作的。嘿嘿，想偷偷提高自己的工资标准？小样，后台记着呢。三、 如何不受人为影响自动记录，降低程序设计的复杂性，提高通用性 既然自动记录，首先想到的当然是利用触发器了。 首先遇到的第一个难点是，如何记录操作员。参考前人做法，建一个记录用户登录的数据表(UserLogin)，只需要三个字段，记录用户名(UserName)、登录机器的名称(HostName)和登录时间(LoginTime).这儿假定数据操作是由同一台PC最近登录的用户进行的。第二个问题是字段名，我们有时需要直观的字段名称，如果直接给出缩写后的字段名，可能会引起混淆，这儿可以利用列属性中的说明来解决，在创建数据表时在每个列属性中的说明中指定直观有意义的名称，只要取出这个说明就可以保存有意义的字段名称。第三个问题是有些字段不需要进行记录，比如一些内容较多的说明性的大字段。需要在程序中排除。好了，下面让我们看看触发器的主要部分：CREATETRIGGERdbo.tr_UpdateONAFTERUPDATEASBEGINSETNOCOUNTON;SELECTmid=IDENTITY(int,1,1),*INTO#iFROMinsertedIFrowcount=0RETURNSELECTmid=IDENTITY(int,1,1),*INTO#dFROMdeletedDECLARETableNamenvarchar(10),Recordernvarchar(20),snvarchar(4000)SELECTTOP1TableName=职工信息表,Recorder=UserNameFROMUserLoginWHEREHostName=host_name()ORDERBYLoginTimeDESCDECLARE#tbCURSORLOCALFORSELECTINSERTDataRecorder(TableName,KeyValue,FieldName,OldValue,NewValue,Recorder)SELECTTableName,CAST(i.EmployeeIDASnvarchar),+CAST(ISNULL(b.value,)ASchar(50)+,CAST(d.++ASnvarchar),CAST(i.++ASnvarchar),RecorderFROM#dd,#iiWHEREi.mid=d.midANDi.++d.++FROMsyscolumnsaLEFTJOINsys.extended_propertiesbONa.id=b.major_idANDa.colid=b.minor_idWHEREa.id=object_id(Employee)AND(Substring(columns_updated(),(a.colid-1)/8+1,1)&power(2,(a.colid-1)%8)=power(2,(a.colid-1)%8)ANDNOTIN(Remarks,Address)ORDERBYa.colidOPEN#tbFETCH#tbINTOsWHILEfetch_status=0BEGINEXECsp_e