Moving Data for Audit - Best Practice

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?

(grichardomi@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, esquel@sommarskog.se

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

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

Help me! Please indicate which 3rd party tools  are you suggesting.