记录用户完成的数据更改 [英] Logging data changes done by user

问题描述

我正在c＃winforms和sql server 2008中开发ERP解决方案。我们的要求是用户所做的所有更改，例如销售价格的变更，需要记录谁完成，何时完成以及什么是旧/新值。

由于我们不使用Windows身份验证，因此无法捕获已登录的用户详细信息，因此我已阅读了更改跟踪所做的大量解决方案。

之前我使用过触发器，但是我们如何将登录用户传递给触发器？



我尝试过：



我尝试过使用更改跟踪，但上面已经说过问题。

I am developing an ERP solution in c# winforms and sql server 2008. Our requirement is that all changes done by a user like for example change of sales price needs to be logged with who has done , when it was done and what was the old / new value.
I have read a lot of solutions done by change tracking by we cannot capture the logged in user details there since we don't use windows authentication.
I have used triggers before but how do we pass the logged in user to the trigger?

What I have tried:

I have tried using change tracking but have issue stated above.

推荐答案

你无法得到用户来自sql事务的登录详细信息，除非每个人都使用不同的sql登录到服务器。根据你的问题的声音，你不使用个人登录（这是一件好事，顺便说一句，但不是很有帮助）



第1步：你必须登录a'修改者'，数据中的日期和类型。您可以轻松地将这样的列添加到现有表中：

SQL：ALTER TABLE Statement [ ^ ]

我的数据中还包含一个创建者，但这对你来说太过分了。



步骤2：根据更新和删除创建触发器（如果适用）。

您不需要插入触发器。而是记录任何更新触发器的已删除侧。这样，您的日志表将包含以前的值，而您的实际表格中包含更改为的值。

删除语句也是如此。这将记录从表中删除之前的最后一个值。



步骤3 ????

步骤4利润（开玩笑：P）





所以你的数据将经历这个生命周期

You can't get the user login details from the sql transaction unless every individual uses a different sql login to the server. By the sound of your question, you don't use individual logins (This is a good thing btw, but not very helpful)

Step 1: You have to log a 'modified by', date and type in your data. You can easily add such a column to an existing table:
SQL: ALTER TABLE Statement[^]
I also include a "created by" in my data, but that would be overkill for you.

Step 2: Create your triggers based on Update and Delete (if applicable).
You don't need an insert trigger. Instead log the "deleted" side of any update trigger. This way your 'log' table will contain the previous values and your actual table has the 'what is was changed to' values.
The same is true for the delete statements. This will log the last value before it was removed from the table.

Step 3 ????
Step 4 Profit (Just kidding :P)


So your data will go through this life-cycle

Insert:
Table                             Log table
[values1],modified-by, date1      null

Update 1
Table                             Log table
[values2],modified-by, date2      [values1],modified-by, date1, 'update'

Update 2 
Table                             Log table
[values3],modified-by, date3      [values1],modified-by, date1, 'update'
                                  [values2],modified-by, date2, 'update'

Delete
Table                             Log table
null                              [values1],modified-by, date1, 'update'
                                  [values2],modified-by, date2, 'update'
                                  [values3],modified-by, date3, 'update'
                                  [values3],deleted-by, datenow, 'delete'

如您所见，Values3有2个条目。这是因为我们必须记录我们通常在主表中找到的原始修改，我们必须记录删除操作。



我希望这很清楚

如果您需要进一步澄清，请告诉我。

Andy ^ _ ^

---

As you can see there are 2 entries for Values3. This is because we must log the original modification that we would normally find in the main Table and we must log the delete action.

I hope that's clear
Let me know if you need any further clarification.
Andy ^_^

---

这篇关于记录用户完成的数据更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！