跟踪SQL Server 2005数据库中的更改 [英] Tracking changes in a SQL server 2005 database

问题描述

我的任务是开发一种跟踪数据库更改的解决方案.

I have been tasked with developing a solution that tracks changes to a database.

对于更新，我需要捕获:

For updates I need to capture:

• 更新日期
• 旧值
• 新价值
• 受影响的领域
• 做改变的人
• 记录ID
• 表记录在
中

对于删除:

• 删除日期
• 正在删除的人
• 记录的标题/说明/标识已删除.我要跟踪的表都具有标题或描述字段.我想在删除记录之前捕获它.
• 表记录在

对于插入物:

• 插入日期
• 做改变的人
• 记录ID
• 表记录在
中

我已经想到了几种方法来做到这一点:

I've thought of a few ways to do this:

• 我正在使用存储过程进行任何更新/删除/插入.我将创建一个通用的跟踪"表.它会有足够的字段来捕获所有数据.然后，我将在每个存储的过程中添加另一行，以达到将记录插入到跟踪表中"的效果.
  • 缺点:所有更新/删除/插入都混在同一张表中
  • 很多空字段
  • 如何跟踪批量更新/删除/插入? < ----这可能不是问题.我在应用程序中实际上不做任何类似的事情.
  • 如何捕获进行更新的用户.数据库只看到一个帐户.
  • 编辑许多现有代码进行编辑.
  • I am using stored procedures for any updates/deletes/inserts. I would create a generic "tracking" table. It would have enough fields to capture all the data. I would then add another line in each stored proc to the effect of "Insert record into tracking table".
    • downside: all updates/deletes/inserts are all jumbled in the same table
    • lots of NULLed fields
    • how do I track batch updates/deletes/inserts? <---- this might not be an issue. I don't really do any thing like this in the application.
    • how do I capture the user making the update. The database just sees one account.
    • edit a lot of existing code to edit.

    我正在使用asp.net，C#，SQL Server 2005，iis6，Windows2003.我没有预算，因此可惜我无法购买任何东西来帮助我.

    I am using asp.net, C#, sql server 2005, iis6, windows 2003. I have no budget so sadly I can't buy anything to help me with this.

    感谢您的回答！

    推荐答案

    由于多种原因，触发器不会获得您需要的所有信息-但没有用户ID是关键.

    A trigger wouldn't have all the information you need for a bunch of reasons - but no user id is the clincher.

    我想说，您位于正确的轨道上，可以在有任何更改的地方插入通用sp.如果您针对接口的sp进行标准化，那么您将处于领先地位-很难潜入未被跟踪的更改.

    I'd say you're on the right track with a common sp to insert wherever a change is made. If you're standardizing on sp's for your interfaces then you're ahead of the game - it will be hard to sneak in a change that isn't tracked.

    将其视为等同于会计应用程序中的审计跟踪-这是日记本-记录了每笔交易的单个表.他们不会为存款，取款，调整等实施单独的日记帐，这是相同的原则.

    Look at this as the equivalent of an audit trail in an accounting application - this is the Journal - a single table with every transaction recorded. They wouldn't implement separate journals for deposits, withdrawals, adjustments, etc. and this is the same principle.

    这篇关于跟踪SQL Server 2005数据库中的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！