|
|
Posted by Erland Sommarskog on 12/05/11 11:29
(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/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|