June 16, 2003 at 5:56 am
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.
June 16, 2003 at 6:30 am
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.
June 16, 2003 at 7:52 am
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
June 16, 2003 at 8:45 am
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.
June 16, 2003 at 12:03 pm
Hello.
How about using trigger(s) to write the deletes to archive table(s).
Everett
Everett Wilson
ewilson10@yahoo.com
June 17, 2003 at 1:12 am
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.?
June 17, 2003 at 1:38 am
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
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