Backup my deleted records

Hi all,

I have a db with 15 tables and I want to keep records that have been deleted.

Now I don't know where to start:
Must I keep the same structure as the main db or can I also dump all the data in one table?

what are the advantages and disatvantages of the named possibilities.

If someone knows anything else please help me out.

Thnx in advanceBecause the structure of the 15 tables will be different per table, you cannot dump them into one table. What you could do is create a new table per existing table and fill the new table with a trigger. Disadvantage: takes time and your database size will grow. Advantage: you can keep your records and have a good overview. Second possibility is not to actually delete the records, but to use a column to indicate whether a record is deleted or not. Disadvantage: another column, possibly problems with unique constraints, optimizers use incorrect data. Advantage: less administration as the first solution.|||thnx for your reply,

I 'll think I'll go with the suggestion to create a table per each existing table.|||This is a common concept...not only deletes but updates...

Script the base...add 3 additional columns and add a trigger to the base...something like:

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END|||Another way of doing this is.

Add a column called Deteted to all tables (it will have value of 0 by default)
Whenever u delete a record, just ser deleted to 1.
Then after a few months, when you want to archieve the db, copy all those records with deleted=1 to your archive tables.

This is a change that has to be done at the application level.
If your application is already built, then you can handle this issue only at the database level, thus you will have to go for triggers.

Regards

Benny