Recognizing Deleted Rows | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Recognizing Deleted Rows

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?
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
Bit addup to Twan’s arrow, may refer to this link which is similar and MSDN link for further assesment. HTH _________
Satya SKJ