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 <name>_all<br />create a view called the same as the table (select * from <name>_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 linkhttp://www.sql-server-performance.com/q&a48.asp which is similar and MSDN linkhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_refintegrity.asp for further assesment. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>