访问与SQL对记录更改的审计跟踪？ [英] Access vs. SQL for audit trail of record changes?
问题描述
我一直在玩Allen Browne的审计代码，发现它非常有用。我需要跟踪
几个表的记录插入，删除和编辑。我打算用我的后端替换Access与Microsoft SQL
服务器，但继续使用Access作为前端。
我明白我可以创建在表级别的SQL中记录更改的审计跟踪，而不是在Access中的表单级别。
自1.0天以来，我一直在使用Access，但是一个带有SQL的新手
。我的应用程序后端相当容易移动到SQL -
除了重命名很多表格。
我的问题：
1）在
SQL后端与Access.mdb前端中实现记录级审计功能有多难？由于我有很多表格，
与我的每张表互动，似乎记录级审核
会简化我的工作。
2）在后端审核与Access
前端的性能方面是否有优势？
3）我读过关于使用''触发器''或''复制''在SQL中创建
审核。有没有首选方法？
提前感谢您的建议。
如果您使用SQL Server作为后端，您应该能够编写
触发器，它会在任何数据更改后自动为您记录。即使人们直接更新表格，而不是通过你的应用程序，它也会工作，这意味着它会工作，这意味着它根本没有代码
你的前端。
-
Doug Steele，Microsoft Access MVP
http://I.Am/DougSteele
（请不要发电子邮件！）
" Jim M" <毫安***** @ rci.rutgers.edu>在消息中写道
news：11 ********************** @ g49g2000cwa.googlegr oups.com ...我一直在玩Allen Browne的审计代码，发现它非常有用。我需要跟踪几个表的记录插入，删除和编辑。我打算用我的后端替换Access与Microsoft SQL
服务器，但继续使用Access作为前端。
我知道我可以在SQL中创建记录更改的审计跟踪在表级别，而不是在Access中的表单级别。
我从1.0开始一直在使用Access，但是一个新手用SQL。我的应用程序后端很容易转移到SQL -
除了重命名很多表格。
我的问题：
1）实施起来有多困难SQL后端与Access.mdb前端的记录级审计功能？由于我有很多表格可以与我的每个表格进行交互，因此记录级别的审核似乎可以简化我的工作。
2）后面的审计是否有优势在性能方面结束与Access
前端的对比？
3）我已经阅读过使用触发器或复制来创建
审核在SQL中。是否有首选方法？
提前感谢您的建议。
Per Douglas J. Steele：如果您使用SQL Server作为后端，您应该能够编写
触发器，它会在任何数据更改后自动为您记录。这样做的好处是
代码可以变得足够聪明：
1）仅记录已经改变的字段（即不是整个记录）
我猜这可能是一个明智的选择。
2）记录''在价值之前''和''之后？
我不太确定这个。知道它可以做到会让我在下一个项目中重新考虑我的日志记录例程。
-
PeteCresswell
Doug，
谢谢，这听起来很有趣。我很感激帮助。
我假设我设置审计表的方式与Allen Browne在他的
审计代码中所做的相同，即每个表的审计表我想用所有
审核相同的字段以及日期/时间戳，用户/机器标记，
以及可能是新索引的空间。我可能还需要标记是否删除，追加或编辑了
。我假设触发器类似于Access
事件（我真的是这个新手......）有没有办法直接
将参数传递给SQL Server访问，例如表单中的值，
或要在审计表中写入的全局变量？我可以把它写在
后端表中，如果这是所有SQL都可以阅读的......再次感谢！
Jim
I''ve been playing with Allen Browne''s audit code and found it very
useful. I need to track record insertions, deletions, and edits for
several tables. I am planning to replace Access with Microsoft SQL
server for my back end, but continue to use Access for the front end.
I understand I can create an audit trail of record changes in SQL at
the table level, instead of at the form level in Access.
I have been playing with Access since the days of 1.0, but an a newbie
with SQL. My application backend was fairly easy to move to SQL -
except for renaming a lot of tables.
My Questions:
1) How difficult is it to implement a record level audit capability in
SQL backend vs. Access.mdb front end? Since I have many forms that
interact with each of my tables, it seems that record level auditing
would simplify my work.
2) Are there advantages to auditing in the back end vs. the Access
front end in terms of performance?
3) I have read about using both ''triggers'' or ''replication'' to create
audits in SQL. Is there a preferred approach?
Thanks in advance for the advice.
If you''re using SQL Server as the backend, you should be able to write
Triggers that will automatically log for you after any data change. This has
the advantage that it''ll work even if people update the tables directly,
rather than going through your application, and it means no code at all in
your frontend.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Jim M" <ma*****@rci.rutgers.edu> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...I''ve been playing with Allen Browne''s audit code and found it very
useful. I need to track record insertions, deletions, and edits for
several tables. I am planning to replace Access with Microsoft SQL
server for my back end, but continue to use Access for the front end.
I understand I can create an audit trail of record changes in SQL at
the table level, instead of at the form level in Access.
I have been playing with Access since the days of 1.0, but an a newbie
with SQL. My application backend was fairly easy to move to SQL -
except for renaming a lot of tables.
My Questions:
1) How difficult is it to implement a record level audit capability in
SQL backend vs. Access.mdb front end? Since I have many forms that
interact with each of my tables, it seems that record level auditing
would simplify my work.
2) Are there advantages to auditing in the back end vs. the Access
front end in terms of performance?
3) I have read about using both ''triggers'' or ''replication'' to create
audits in SQL. Is there a preferred approach?
Thanks in advance for the advice.
Per Douglas J. Steele:If you''re using SQL Server as the backend, you should be able to write
Triggers that will automatically log for you after any data change. This has
the advantage that
Can the code be made smart enough to:
1) Log only fields that have changed (i.e. not the whole record)
I''m guessing this is probably a no-brainer.
2) Log both ''before'' and ''after'' values?
I''m not so sure about this one. Knowing it can be done would cause me to
re-think my logging routines on the next project I do.
--
PeteCresswell
Doug,
Thanks, this sounds interesting. I appreciate the help.
I assume I set up audit tables the same way Allen Browne did in his
audit code, i.e. an audit table for each table I want to audit with all
the same fields plus room for a date/time stamp, user/machine stamp,
and perhaps a new index. I probably also need to flag whether it is
deleted, appended, or edited. I assume the triggers are akin to Access
events (I really am a newbie at this...) Is there any way to directly
pass a parameter to SQL Server from Access, e.g. a value from a form,
or a global variable to write in the audit table? I could write it in a
Backend Table if that is all SQL can read... Thanks again!
Jim
这篇关于访问与SQL对记录更改的审计跟踪？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
