问题描述
我的任务是开发一种跟踪数据库更改的解决方案.
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数据库中的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！