How to keep and restore the deleted records?

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  June 2, 2003 at 12:40 am

  #459299

  Hi ljmonteiro,

  quote:

  ---

  a5xo3z1 use a "status" field ( I add also a timestamp , and a user field ). But adding a column on a production table sometimes it's not possible, but useful on critical DBs.It's also good check if this additional processing will have "side effects" in operation .

  ---

  in fact I use also a varchar field and a datefield to keep track of who deleted and when, but the main thing for GUI purposes is the bit field.

  After following this thread, it might be appropriate to add that I built my apps from the beginning with this feature implemented. Adding this to an existing app will be very time consuming and needs extensive testing.

  Cheers,

  Frank

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• scraft

  Newbie

  Points: 9

  More actions

  June 2, 2003 at 7:49 am

  #459333

  I have had very good success adding these columns to all my tables (all with simple indexes)

  cUserIns Char(24)

  tDtIns DateTime

  cUserUpd Char(24)

  tDtUps DateTime

  cUserDel Char(24)

  tDtDel DateTime

  iDel Int

  default value for iDel is 0, default value for tDtIns is getdate().

  When any user does a delete, put a 1 in iDel and put the name and datetime in cUserDel and tDtDel. Now you know when and who deleted the row. For all your SQL, append at the end of every where clause " and where iDel=0". Now no row is truly deleted and if you need to get it back, you can. Very little overhead, and if you include these columns in your table planning from the beginning there is no extra work.