|
Home > Archive > Microsoft SQL Server forum > October 2005 > Moving Data for Audit - Best Practice
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Moving Data for Audit - Best Practice
|
|
| grichardomi@gmail.com 2005-10-27, 9:24 am |
| 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?
| |
| Erland Sommarskog 2005-10-27, 9:24 am |
| (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
| |
| --CELKO-- 2005-10-27, 9:24 am |
| If this is for an audit, have you considered a third party tool that is
approved for SOX, et al?
| |
| grichardomi@gmail.com 2005-10-27, 9:25 am |
| Help me! Please indicate which 3rd party tools are you suggesting.
|
|
|
|
|