如何正确审核表格中的更改？ [英] How do I properly audit changes in my tables?
问题描述
这是我第一次为PoS WPF应用程序创建审核日志，并想知道我究竟是如何实现审核系统的，因为看起来每个选项都有其起伏。到目前为止，我还没有阅读大量的文章/主题，而是根据审计日志的一些常见做法缩小了
：b
1。触发器 - 不幸的是，由于我的应用程序的性质，我无法使用触发器，因为它无法知道哪个用户已完成操作。所以我做的是创建一个存储过程，它将处理客户插入
以及客户日志插入及其详细信息。使用存储过程时，应用程序将提供Customer_Id。
2。有一个旧的和新的值 - 我的初步计划是只包括后者，因为我可以用它前面的行中的新值引用它的旧值，但是存储旧值和新值似乎更明智，更复杂 - 明智的。
3。使用单独的数据库作为日志/ 4.外键 - 这可能是我的主要关注点，如果我决定为审计表使用单独的数据库，那么我无法为客户和员工设置外键参与。
It's my first time creating an audit log for a PoS WPF application and was wondering on how exactly do I implement an auditing system because it seems like each option available has its ups and downs. So far from reading numerous articles/threads, I've narrowed
down a few common practices on audit logs:
1. Triggers - Unfortunately, due to the nature of my app, I can't make use of triggers as it has no way of knowing which user has done the action. So what I did instead was to create a Stored Procedure which will handle the customer insert
along with the customer log insert and its details. The Customer_Id will be provided by the application when using the Stored Procedure.
2. Have an old and new value - My initial plan was to only include the latter since I can reference its old value with the new value from the row before it but storing the the old and new value seemed more sensible, complexity-wise.
3. Use a separate database for the log / 4. Foreign Keys - This is probably my main concern, if I decide to use a separate database for the audit table, then I couldn't setup foreign keys for the customer and employee involved.
我创建了一个模拟器，其中包含一个主 - 详细信息表结果，显示在wpf应用程序上以显示管理员的日志，并且非常希望您对可能出现的问题有所了解可能会出现（还有一张员工表，但我忘记了）：
I created a mock-up erd with a master-detail table result to be shown on the wpf app to display the log to an admin and would really like your thoughts on possible problems that may arise (There's also an employee table but I forgot to put it):
https://ibb.co/dheaNK
https://ibb.co/dheaNK
目前这里已经很晚了，我一醒来就会回复。再次感谢。
It's currently very late here, I'll reply as soon as I wake up. Thanks again.
推荐答案
嗨MarkusTrim，
Hi MarkusTrim,
如果应用程序中的用户可以逐个映射到SQL Server中的用户，我们还可以使用SQL Server审计来审计DML或使用Profiler来审计DML。这是一篇讨论此问题的文章，请参阅此博客： https://mohammaddarab.com/create-database-audit-for-insert-update-delete-in-sql-server/ 。
If the user in the application can map to the user in SQL Server one by one, we can also use the SQL server audit to audit the DML or use the Profiler to audit the DML. Here is an article which discuss about this, please refer to this blog: https://mohammaddarab.com/create-database-audit-for-insert-update-delete-in-sql-server/.
审核这些信息后，我们可以将这些文件处理为您想要的格式，然后将它们插入到表中。如果此方法不能满足您的要求，我将选择您提供的第一种方法，创建存储过程，此存储过程
将数据插入指定的表，然后将此操作记录到审计表。
After auditing these information, we can handle these file to the format you want and then insert them to a table. If this method cannot meet your requirement, I will select the first method you provided, create a stored procedure, this stored procedure will insert the data to specified table and then record this operation to the audit table.
最好的问候，
Teige
这篇关于如何正确审核表格中的更改？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
