(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 
