By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Moving Data for Audit - Best Practice

P: n/a
Here is the situation:

Tables:
Account
AccountDocument
AccountTest
Then we have the following "Activity" table refencing "Account" table
above:
Table: Activity
Columns: ActivityID, AccountNo,...

when "Activity" with accountno is created, we'd like to able to take
snapshop or copy all Account related data records from tables: account,
accountdocument, accounttest
and store it somewhere.
This way even if account, accountdocument and accounttest tables change
later, then "Activity" accountno integrity in maintained.

Do we need to create or replicate similar tables to store data for
audit?

or Which other ways can this be achieved?

Oct 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
(gr*********@gmail.com) writes:
Here is the situation:

Tables:
Account
AccountDocument
AccountTest
Then we have the following "Activity" table refencing "Account" table
above:
Table: Activity
Columns: ActivityID, AccountNo,...

when "Activity" with accountno is created, we'd like to able to take
snapshop or copy all Account related data records from tables: account,
accountdocument, accounttest
and store it somewhere.
This way even if account, accountdocument and accounttest tables change
later, then "Activity" accountno integrity in maintained.

Do we need to create or replicate similar tables to store data for
audit?

or Which other ways can this be achieved?


Wait until Nov 7th when SQL 2005 launches. :-)

This is really easy to solve in SQL 2005, thanks to the native xml
data type. You can use an FOR XML query to capture the data into an
xml variable, and then stored that data into an untyped xml column.
Really neat.

In SQL 2000 you can still use FOR XML, but you need a co-operating
client that gets the output from FOR XML and sends it back into a
text document.

Without XML and with the requirement that even if the table changes,
and without a co-operating client, this sounds extremely painful.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 17 '05 #2

P: n/a
If this is for an audit, have you considered a third party tool that is
approved for SOX, et al?

Oct 18 '05 #3

P: n/a
Help me! Please indicate which 3rd party tools are you suggesting.

Oct 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.