SQL Server Performance

Recognizing Deleted Rows

Discussion in 'General DBA Questions' started by MarketMaker, Oct 17, 2003.

  1. MarketMaker New Member

    Is there a method that I can use to figure out what rows have been deleted from a table? In the same way that I can use a timestamp to see if a new record has been inserted or an existing record has been updated.

    How could I figure out what rows have been deleted?

    I was thinking about keeping a trace going, but I didn't even see a deleted row event. Also, I could create another table with the primary key values, and do a join to see what id's have been deleted and no longer exist, but I wanted a more elegant solution that didn't required creating tables in the database. Hopefully can find a better solution than using triggers also.

    Any ideas?
  2. Twan New Member

    Hi ya<br /><br />You could perhaps<br /><br />add a column to the table called is_deleted bit default = 0<br />rename the table to &lt;name&gt;_all<br />create a view called the same as the table (select * from &lt;name&gt;_all where is_deleted = 0)<br />create an instead of delete trigger on the view which updates the table to set the bit flag to 1<br /><br /><br />I've not done this so not sure how it would work in practice, but it sounds good in theory <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  3. satya Moderator

Share This Page