Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Unanswered: Help_Creating Archive Table

    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.

    Any input will be appreciated.
    Thanks in advance

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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.....

  3. #3
    Join Date
    Oct 2003
    Posts
    18
    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

  4. #4
    Join Date
    Mar 2004
    Location
    sa
    Posts
    4
    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
    using
    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.

  5. #5
    Join Date
    Oct 2003
    Posts
    18
    Thank u elamor for the reply. I am going to use Store Procedure, the idea is clear, I think with these info I know where to start, definately I might be posting my query here for deburg if ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •