Archive data before deletion

Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
ChrisThe two-database approach is a good way of doing this. Instead of using the
Deleted flag, you can now issue actual delete statements to remove old
records, but not before you design an ON DELETE trigger to propagate the
deleted rows to the archive database.
So, yes - you do need all those queries... :) This is what being a database
designer is all about.
Lookup CREATE TRIGGER in Books Online. Designing these triggers is easy -
simply use the 'deleted' table.
Example:
insert archive_db.dbo.table1
(...columns...)
select ...columns...
from deleted
Don't forget to include appropriate error-handling, so no delete goes
unnoticed.
ML|||I have an example here: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<devccon@.gmx.de> wrote in message
news:1122542261.075469.237440@.f14g2000cwb.googlegroups.com...
Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
Chris|||Thanks to you all for the directions. I'm still struggling with the
trigger but it shouldn't be that of a problem.
I really hoped there would be some other way, but, hey anything that
does the job is good...
Thx again,
Chris
devccon@.gmx.de wrote:
> Hi all,
> how can I copy all dependant child records into duplicate tables before
> deleting them.
> The situation is that I have a master table "customer" with 20 other
> tables that depend on this master table.
> Foreign keys are all set up correctly and cascading delete is enabled.
> Now when a customer wants to cancel his subscription, I don't want to
> delete all referenced data immediatly without saving, because I need
> them for possible future references, like billing addr. etc.
> Currently I'm setting a "Deleted" flag so that in any query this
> customer doesn't show up.
> What would be the best approach to archive all dependent data before
> deleting the parent and the child records.
> I'm thinking of duplicate tables and/or a duplicate database.
> I've also played around with triggers but can't get the automatic
> insert of the child records working.
> Do I really have to do something like this for every child table?
> Insert into dupAddr (select * from addr where customerid = 1)
> Insert into dupTrx (select * from trx where customerid = 1)
> Insert into dupCustomer (select * from customer where customerid = 1)
> etc.
> I'm using SQL2000/W2K3.
> Any help would be appreciated.
> thx in advance,
> Chris