Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53

    Unanswered: 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 advance
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    Because 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.
    Johan

  3. #3
    Join Date
    Dec 2003
    Location
    Lowlands
    Posts
    53
    thnx for your reply,

    I 'll think I'll go with the suggestion to create a table per each existing table.
    If Perfection really is an Illusion. Then I want to get as close as possible to that Illusion.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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:

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    29

    Arrow

    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

Posting Permissions

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