I have a table called customers that store information about the particular customer. I would like to have a table called Archive so that when I delete a Customer that have not been active for a specific time then the deleted information will be automatically be inserted into the Archive table. Do I need to create the archive table with the same numbers of columns as exactly as the customer table?
I need some basic idea about how this should be implemented.
Do you use foreign keys? If so - you have to move customer activity history to archive table too. Structure of table could be the same, unless you want to know when data were moved to archive or any other additional information. And do not use removed ids again.....
Thank you snail for the reply
I have just created the Archive table. The primary key of the Customer Table is a foreign key in the archive table. I have added three more columns in the Archive Table namely Comments,ArchiveDate and EmployeeName, these three columns are not in the customer Table.Can this be possible? as this colums are not in the Customer Table. These columns are important because when a customer is deleted I want to be able to enter the reason why a specific customer was deleted. What did u mean by ids, are you talking about Identity Seed or?
Again I have trouble formulating the query that will insert the deleted columns into the Archive Table. Need additional Info
Thanks for the help so far
well, if you got two identical tables you could write a trigger that fires on a deleted record from the customers table you can use
INSETED OF DELETE trigger
or if you got two different tables you can write a store procedure for deleting so it can insert the required record into the archive table with
the supplied fields first you have to insert to the achive table
insert into achive(field1,field2)
select field1,field2 from customers where customerid = @custID
then update the record in the achive using
update achive set field6 = @employeeName ....
where customerid = @custID
to it then delete the record from the customers table.