做百度推广会送网站吗,免费的活动策划网站,网站建设氵金手指下拉十二,新乡集团网站建设一、提出问题 在开发一个企业级 应用的时候#xff0c;尤其在一个涉及到敏感数据的应用#xff0c;比如财务系统、物流系统#xff0c;我们往往有这样的需求#xff1a;对于数据库中每一笔数据的添加、修改和删除#xff0c;都需要有一个明确的日志#xff0c;以便我们可…一、提出问题 在开发一个企业级 应用的时候尤其在一个涉及到敏感数据的应用比如财务系统、物流系统我们往往有这样的需求对于数据库中每一笔数据的添加、修改和删除都需要有一个明确的日志以便我们可以追踪每一笔记录的来龙去脉——数据的更新是被谁、在什么时候执行的该操作还涉及到哪些具体的Table原来的数据是什么新的数据又是什么 本Blog的目的就是基于上面提出的要求设计一个Audit Logging的解决方案。 二、分析问题 基于上面提出的要求我们进行具体的分析 A如何确定Log的粒度 对于一个企业级 应用数据的每一项操作应该被纳入一个Transaction中以保证数据的完整性。所以Transaction可以看作是数据操作的基本单元我们的解决方案是 以Transaction为单位的Log。 B如何确定记录的信息 正如一开始我们提出的要求我们记录的不仅仅包括Transaction本身的一些基本信息比如执行该操作的User执行的时间等。由于一个Transaction会涉及到对多个相关Table中的一个或者多个记录的增、删、改的操作所以下面一些信息也需要纳入我们的Logging范畴Transaction涉及的Table每条记录的数据的变化:对于Insert操作需要记录添加的新记录的数据对于Update操作需要记录原来的数据和更新后的数据而对于Delete操作需要记录Delete之前的数据。 C如何设计记录的数据结构 基于我们提取出的需要进行Log的信息我们为决绝方案设计了下面的数据结构:两个具有主子关系的Table。主表T_AUDIT_LOG记录了一个Transaction的基本的信息Transaction的标识执行的用户帐号和操作的具体时间子表T_AUDIT_LOG_DETAIL则记录了Transaction涉及的每条记录数据改变相关的信息该记录对应的Table名称操作的类型和具体的数据的变化。T_AUDIT_LOG和T_AUDIT_LOG_DETAIL通过Transaction的唯一标识TRANSACTION_NO关联在一起。 主表T_AUDIT_LOG的结构 TRANSACTION_NO[CHAR36]一个GUID代表的字符串唯一表示一个Transaction。 OPERATION_DATE[DATETIME]Transaction真正执行的时间Filed name应该改为OPERATION_TIME才对 USER_ID [VARCHAR] 执行该Transaction的用户帐号。 子表T_AUDIT_LOG_DETAIL的结构 AUDIT_DETAIL_IDINT一个自增长的Field用作该表的主键。 TRANSACTION_NO [CHAR36] 同主表T_AUDIT_LOG的TRANSACTION_NO字段Transaction的唯一标识一个GUID。 TABLE_NAME [VARCHAR] 操作涉及的具体的Table的名称。 OPERATION_TYPE [VARCHAR] 操作的类型——InsertUpdateDelete。 DATA_CHANGE [XML] 该字段采用了SQL Server 2005新的数据类型——XML用于存储操作引起的数据的改变。beforeElement封装了Update 操作之前的数据其中每个XML attribute代表的是对用的Filedafter包含的则是Update执行之后的数据。这是Update操作对应的XML schema如果操作对应的是向某个表中Insert一个记录则只有封装了新添加记录数据的after element同样的Delete操作对应的XML只有包含被Delete记录的before Element。 For Update dataChange before order_id30 order_dateApr 21 2007 12:00AM supplierHP / after order_id30 order_dateJan 1 2005 12:00AM supplierDell Corporation //dataChange For Insert dataChange after order_id30 order_dateJan 1 2005 12:00AM supplierDell Corporation //dataChange For Delete dataChange before order_id30 order_dateApr 21 2007 12:00AM supplierHP //dataChange D如何添加Log记录 从T_AUDIT_LOG_DETAIL的结构上可以很清楚地看出该表记录的是基于某个具体的Table的每个记录数据变化。所以我们会首先想到的是通过Trigger来添加这些Logging数据——当完成对相关Table的增、删、改操作后通过出发我们为Audit Logging编写的Trigger来自动添加这些信息。所以我的这个Audit Logging的解决方案是一个基于Trigger的解决方案我将在下面一节中讲述如何编写这个Trigger。由于我们的Logging数据表采用的是一个具有Parent-Child关系的两个Table在通过Trigger为子表T_AUDIT_LOG_DETAIL添加Log记录之前我们必须保证主表T_AUDIT_LOG中包含相应的记录所以在进行与逻辑相关的数据操作之前我们必须在把Log的总体信息插入T_AUDIT_LOG之中。 E如何保证Logging操作和实际的操作纳入同一个Transaction中 由于我们实际的商业逻辑的数据操作是一个基于Database的操作而我们的Audit Logging也是一个基于Database的操作。而Audit Logging是基于这个具体商业逻辑的数据操作的。所以为了使用Logging的数据能够100%地反映真实执行了的数据操作Logging操作和实际的数据操作应该纳入同一个Transaction中避免造成Audit Logging记录一个执行失败的操作或者数据操作执行成功而Logging操作执行失败。 F权衡利弊 到现在为止这个解决方案在功能上能够成功解决我们开篇提出的Logging要求但是他在下面两个方面引起的不足必须引起足够的重视不然会彻底毁掉你的应用。 引起T_AUDIT_LOG_DETAIL表中的数据的急剧上升由于对于需要进行Audit Logging的每个Table它的每个记录的操作都会在T_AUDIT_LOG_DETAIL增加一条记录如果这样Table或者对这样的Table的操作过于频繁将会造成该表中的记录急剧上升近而影响整个应用的性能。 性能问题由于对需要进行Audit Logging的Table的每项操作都会出发Trigger这会在一定程度影响数据操作的性能。 通过对上面的分析我们大体知道整个解决方案的整体思路现在我们来具体地在编程方面来进一步实现这个解决方案。 三、 解决方案 A表的结构设计 对于一个涉及到敏感数据的企业级应用对数据表的设计很重要为了能够追踪每一笔数据的来龙去脉能够确定每一笔记录被谁创建什么时候创建被谁最后一次修改什么时候作的修改如何处理并发操作如何进行我们的Audit Logging基于这些需求我对每一个Table添加了下面7个Common 的字段 CREATED_BYVARCHAR创建该记录的User ID。 CREATED_ONDATETIME纪录的创建时间。 LAST_UPDATED_BYVARCHAR记录最后一次被修改对应的User ID。 LAST_UPDATED_ONDATETIME记录最后一次修改的时间。 VERSION_NOTIMESTAMP表明该记录的版本号用于并发操作。 TRANSACTION_NOCHAR36该记录最后一次修改的对用的Transaction的ID也就是我们今天进行Audit Logging对应的那个Transaction的ID。 NEED_AUDITbit这个将在后面的部分介绍它的用途。 当我们进行任何涉及到数据库的操作为了保证数据的完整性我们会把所有的操作纳入一个Transaction之中。为了有利于Auditing我们在开始 这个Transaction之前会生成一个基于GUID的Transaction No, 并把它更新到该Transaction涉及的每个记录的TRANSACTION_NO字段。如果某条记录是新添加的那么我们会把CREATED_BY和LAST_UPDATED_BY赋值为当前的User把CREATED_ON和LAST_UPDATED_ON赋值为当前的时间。如果我们要修改或者删除某条记录我们通过获取记录的VERSION_NO和数据库中对应的数据进行比较来判断该记录时候在被当前Session取出后又被别的User修改了从而有效地处理并发操作。 B 整个数据处理流程 ADO.NET为我们在.NET平台下提供了简单而直接的数据操作机制。此外通过Dataset、DataAdapter、DbCommand等一系列的Component实现我们常用的离线的方式来操作数据库我们通过DataAdapter获取数据填充到我们的Dataset对象并断开Db Connection。我们通过Dataset来构建一个内存中的数据库来mapping真正Db中的数据结构最终我们通过DataAdapter把对Dataset中的数据更新递交到Db中。我们的Audit Logging就以这样一种机制来介绍。我们通过这种离线操作模式来介绍我们的整个Log的操作流程当然这个Audit Logging解决方案同样适合基于Connection的数据操作。 正如上图所描述的我们首先从Db中获取数据并填充到Dataset中然后我们把 Audit Log的基本的数据添加到一个Audit Log Dataset中并生成对应T_AUDIT_LOG表一个Transaction的一个ID我们称之为Transaction No然后我们根据我们具体的业务逻辑来对我们用来承载获取数据的Dataset作相应的修改并把我们生成的Transaction更新到该Dataset每个需要更新的Data Row中。然后我们把基于商业逻辑的更新和添加的Log数据向Db提交所有的这些操作被纳入到一个单独Transaction中。当这些更新通过最终调用SQL或者Stored procedure更新到Db中后对应的Trigger被触发基于某个Table的数据改变的信息被添加到T_AUDIT_LOG_DETAIL中。 C Programming 上面我们通过文字介绍了Audit logging 的整个流程我们现在已我们最擅长的编程的角度来进一步了解这个过程。 首先我们定义了一个AuditLoggingDataSet的强类型的Dataset该Dataset包含一个TableT_AUDIT_LOG映射DB中的同名T_AUDIT_LOG表。 然后我们定义了一个专门用于Audit Logging操作的Helper类AuditLoggingHelper 该Helper包含连个Public成员一个PropertyAuditLoggingData返回对应的Log数据。一个方法AuditLog添加Log信息并以GUID的形式返回一个Transaction No。 using System;using System.Collections.Generic;using System.Text;namespace Artech.AuditLogging.ConsoleApp{ public class AuditLoggingHelper { private AuditLoggingDataSet _auditLoggingData; /**//// summary /// A strongly typed dataset to used to store the general auditoing inforamtion. /// /summary public AuditLoggingDataSet AuditLoggingData { get { return _auditLoggingData; } set { _auditLoggingData value; } } /**//// summary /// Log the general auditoing information according with the current transaction. /// /summary /// returnsA guid which identifies uniquely a transaction/returns public Guid AuditLog() { if (this._auditLoggingData null) { this._auditLoggingData new AuditLoggingDataSet(); } Guid transactionNo Guid.NewGuid(); AuditLoggingDataSet.T_AUDIT_LOGRow auditRow this._auditLoggingData.T_AUDIT_LOG.NewT_AUDIT_LOGRow(); auditRow.BeginEdit(); auditRow.TRANSACTION_NO transactionNo.ToString(); //TODO: The user id is generally the account of the current login user. auditRow.USER_ID testUser; auditRow.OPERATION_DATE DateTime.Now; auditRow.EndEdit(); this._auditLoggingData.T_AUDIT_LOG.AddT_AUDIT_LOGRow(auditRow); return transactionNo; } }} 我还定义了一个专门定义了用于Data Access操作的DataAccessHelper的另一个Helper类。这个Helper类帮助我以一种简单的方式向Db获取、提交数据。我将现在下面一节中简单介绍这个DataAccessHelper。 现在我们简单地来模拟这样一个场景我们有一个简单的处理Order的应用, 从Db中获取某个Order ID的Order信息对获取的数据进行相应修改后被最终被提交到Db中。 我们简化了Order数据的复杂度假设DB中对应的Table如下通过这些是我们Dataset的结构我将在下面一节已Sample的形式来一步一步来介绍这个场景现在我们这些简单地通过程序来了解整个处理的流程。 我们现在来看我们的code: using System;using System.Collections.Generic;using System.Text;using System.Data;namespace Artech.AuditLogging.ConsoleApp{ class Program { static string USER_ID testUser; static void Main(string[] args) { UpdateOrderData(); } static void UpdateCommonField(DataRow row) { row[LAST_UPDATED_BY] USER_ID; row[LAST_UPDATED_ON] DateTime.Now; if (row.RowState DataRowState.Detached || row.RowState DataRowState.Added) { row[CREATED_BY] USER_ID; row[CREATED_ON] DateTime.Now; } } static OrderDataSet GetAllOrderData() { OrderDataSet orderData new OrderDataSet(); using (DataAccessHelper dataAccessHelper new DataAccessHelper()) { orderData.EnforceConstraints false; dataAccessHelper.FillData(orderData.T_ORDER, CommandType.Text, SELECT * FROM dbo.T_ORDER, new Dictionarystring, object()); dataAccessHelper.FillData(orderData.T_ORDER_DETAIL, CommandType.Text, SELECT * FROM dbo.T_ORDER_DETAIL, new Dictionarystring, object()); orderData.EnforceConstraints true; } return orderData; } static void UpdateOrderData() { OrderDataSet orderData GetAllOrderData(); AuditLoggingHelper auditLoggingHelper new AuditLoggingHelper(); Guid transactionNo auditLoggingHelper.AuditLog(); OrderDataSet.T_ORDERRow orderRow orderData.T_ORDER[0]; orderRow.ORDER_DATE new DateTime(2005, 1, 1); orderRow.SUPPLIER Dell Corporation; orderRow.TRANSACTION_NO transactionNo.ToString(); UpdateCommonField(orderRow); using (DataAccessHelper dataAccessHelper new DataAccessHelper()) { dataAccessHelper.BeginTransaction(); try { dataAccessHelper.UpdateData(auditLoggingHelper.AuditLoggingData.T_AUDIT_LOG); dataAccessHelper.UpdateData(orderData.T_ORDER); dataAccessHelper.UpdateData(orderData.T_ORDER_DETAIL); dataAccessHelper.Commit(); } catch (Exception ex) { dataAccessHelper.Rollback(); Console.WriteLine(ex.Message); } } } }} 这个程序执行的流程很简单这里应该不需要再作进一步的说明。通过向Db提交auditLoggingHelper.AuditLoggingData.T_AUDIT_LOG整个Audit Logging实际上只做了一半。通过前面对Logging数据的介绍我们知道需要Log 是基于两张表T_AUDIT_LOG和T_AUDIT_LOG_DETAIL.我现在仅仅添加了T_AUDIT_LOG这个主表的数据具体的Log信息实际上存储在T_AUDIT_LOG_DETAIL这个子表中而这个表中的数据是通过Trigger写入的。我们现在就来看看这个Trigger如何写。 D定义Trigger 我们已表T_Order为例由于对它的添加、修改和删除都需要把 对应的数据的改变记录到T_AUDIT_LOG_DETAIL中我们需要为这3种操作类型定义Trigger。 For Inserttr_order_i IF EXISTS (SELECT * FROM sysobjects WHERE type TR AND name tr_order_i) BEGIN DROP Trigger tr_order_i ENDGOCREATE Trigger tr_order_i ON dbo.T_ORDER AFTER INSERTASIF UPDATE(VERSION_NO)BEGIN INSERT [dbo].[T_AUDIT_LOG_DETAIL] ([TRANSACTION_NO] ,[TABLE_NAME] ,[OPERATION_TYPE] ,[DATA_CHANGE]) SELECT INSERTED.TRANSACTION_NO , T_ORDER ,Insert ,dataChange after order_id CONVERT(VARCHAR,INSERTED.ORDER_ID) order_date CONVERT(VARCHAR,INSERTED.ORDER_DATE) supplierINSERTED.SUPPLIER //dataChange FROM INSERTED END GO For Updatetr_order_u IF EXISTS (SELECT * FROM sysobjects WHERE type TR AND name tr_order_u) BEGIN DROP Trigger tr_order_u ENDGOCREATE Trigger tr_order_u ON dbo.T_ORDER AFTER UPDATEASIF UPDATE(VERSION_NO)BEGIN INSERT [dbo].[T_AUDIT_LOG_DETAIL] ([TRANSACTION_NO] ,[TABLE_NAME] ,[OPERATION_TYPE] ,[DATA_CHANGE]) SELECT INSERTED.TRANSACTION_NO , T_ORDER ,Update ,dataChange before order_id CONVERT(VARCHAR,DELETED.ORDER_ID) order_date CONVERT(VARCHAR,DELETED.ORDER_DATE) supplierDELETED.SUPPLIER / after order_id CONVERT(VARCHAR,INSERTED.ORDER_ID) order_date CONVERT(VARCHAR,INSERTED.ORDER_DATE) supplierINSERTED.SUPPLIER //dataChange FROM DELETED INNER JOIN INSERTED ON DELETED.ORDER_ID INSERTED.ORDER_ID WHERE INSERTED.NEED_AUDIT 1 END GO 我知道对于一个Trigger来说我们可以通过两个表INSERTED和DELETED获取原来的数据和当前的数据。所以我们可以通过INSERTED.TRANSACTION_NO获取对应的Transaction No。这个对于Insert和Update操作没有任何问题但是对于Delete操作INSERTED表中没有数据我们如何获取这个必须的Transaction No呢我们的做法的是在数据被真正被Delete之前先对它进行Update操作把Transaction No赋值给它的TRANSACTION_NO字段。那么在真正触发Delete Trigger的时候就可以通过 DELETED. TRANSACTION_NO来获得这个Transaction No。但是这又带来了一个新的问题我们通过为一个即将被Delete的记录修改Transaction No的时候他会触发我们上面定义的Update Trigger那么一些错误的信息会添加到T_AUDIT_LOG_DETAIL之中这显然是不允许的。如何来解决这个问题呢这就要借助要的NEED_AUDIT 这个字段了。这个字段的默认值为1true在Delete之前我们不但修改TRANSACTION_NO我们还将NEED_AUDIT 字段赋为0。那么Update trigger就会根据这个字段判断该Update操作是否是真正意义上的Update。这也是我们在上面的Trigger中加入了一个条件WHERE INSERTED.NEED_AUDIT 1的原因。 下面我们来看Delete Triggertr_order_d IF EXISTS (SELECT * FROM sysobjects WHERE type TR AND name tr_order_d) BEGIN DROP Trigger tr_order_d ENDGOCREATE Trigger tr_order_d ON dbo.T_ORDER AFTER DELETEASBEGIN INSERT [dbo].[T_AUDIT_LOG_DETAIL] ([TRANSACTION_NO] ,[TABLE_NAME] ,[OPERATION_TYPE] ,[DATA_CHANGE]) SELECT TRANSACTION_NO , T_ORDER ,Delete ,dataChange before order_id CONVERT(VARCHAR,DELETED.ORDER_ID) order_date CONVERT(VARCHAR,DELETED.ORDER_DATE) supplierDELETED.SUPPLIER //dataChange FROM DELETEDENDGO [原创] 如何追踪每一笔记录的来龙去脉一个完整的Audit Logging解决方案—Part II转载于:https://www.cnblogs.com/artech/archive/2007/04/23/723627.html
