Backing up deleted Data from several tables

  • I have an application which when it creates a case stores the information in several tables across more than one db. The application has no provision for undeleting data. Sometimes users mistakenly delete a case and want it to be restored. Is there any way I can have the deleted cases backed up to a table. Or put another way, is there any way of monitoring all deletes on a server and have these deleted data kept for future retrieval.

    Thanks for any suggestions.

  • How is the data being deleted? If it is done by a single proc then you can add code in the proc to store the data to be deleted in other table(s). Other than that you could use trigger(s).

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Other's have suggested this and it's worked well for me;I take no credit for the idea itself: The question is can you change the way the data is "deleted" .

    That is, instead of letting the data actually get deleted, add a bit column( ALTER TABLE YourTable Add IsHidden bit default(0)) to the table with a default value of zero to signify it is hidden or not (same as deleted for end users) and change the SQL that gets the data in the WHERE statement to WHERE ... AND ISHIDDEN <> 1;

    then when they need to restore a case, you just flip the bit instead of gathering fragments of the data from other sources with a complicated cross database stored procedure..

    That works great if you have access to all the sqls that select against your case table.

    However, sometimes that is just too much trouble. because of that, I've also made an on delete trigger before that copies the deleted data to a identical but different table so I could restore it if need be; here's an example of the delete trigger.

    I simply copied the table i wanted to save data from, but without any constraints.

    then the delete trigger was this:

    Create Trigger TR_tblJobs_Delete on dbo.tblJobs

    as

    set xact_abort on

    begin tran

    insert into tblJobs_del

    select * from deleted

    commit tran

    set xact_abort off

    hope that helps a bit

    lowell@stormrage.com

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • The company that supplied the application built the queries into the front end. The company says it does not want anyone to have an idea of how its application works by looking at the SPs. A request for them to include a delete bit has been rejected as it was claimed that it involves rewritng the application all over which they are unwilling to do.So I am left with devising my own way.This has become an important issue becuase a recently deleted case was required in a court action and I had to restore an old backup to retrieve this. I just believe that there must be a better way.

    Thanks for your suggestions anyway. I hope more people will post their thoughts on this.

  • Hello.

    How about using trigger(s) to write the deletes to archive table(s).

    Everett



    Everett Wilson
    ewilson10@yahoo.com

  • One thing I have done is to delete a case using the test server and use profiler to find all the tables involved in the delete. There was a total of 19. This means I have to write triggers for all the 19 tables to store the deletes. What will be the effect on performance.?

  • I have some comments

    1- the triger will not affesct the performane (as I know) ... and you can use Stored procedure in stead of it (in case you call stroed procedure in deleting a Row )

    2- there is another solution, "Linked Servers", so u can make a table in another server and see it through your server .. and update it through Trigers or in Stored procedure

    Alamir_mohamed@yahoo.com

    Alamir Mohamed


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply