Acessing deleted records

Hi

First i'd like to say that i dont really know if it is possible to acess this, but since i've been told that some database system sometimes only "flag" record as deleted to delete them later and that i would need to possibly acess this data for a work im doing, it would be interesting for me if somebody had the asnwer i seek.

Do you know if there is a way under sql server 2000/2005 acess those data?

In SQL Server 2005 you could use OUTPUT clause (http://msdn2.microsoft.com/en-us/library/ms177564.aspx):

Code Snippet

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* ;

In SQL Server 2000 you could create on delete trigger and use "virtual table" deleted

|||

Ok, i was not very clear in the first post, my bad.

Is it possible to acess deleted data, after it was deleted, and by that i mean : Someguy delete a record, and at the end of the week i want to know how much or what data has been deleted if it has not yet been deleted physically.

And i would prefer not to use trigger since i would have to make one for each and every hundreds table of the same number of databases.

|||

No, data is deleted physically in SQL Server as soon as the statement is committed. You *can* look in the logs to see what has been deleted using a third party tool like http://www.apexsql.com/sql_tools_log.asp, but I would only suggest that as a detective tool, not a common surveilance method, especially since you should be dumping your log fairly often as a method of recovery. It will also not record who delete the row, only that it has been deleted. If you want deletes to be logical and not physical, you would have to prepare this in your design and implement it yourself.

A trigger is really your best option for logging changes. Creating a trigger for all tables is a pain, but you might be able to build them without much trouble by using the information_schema and system views (or system tables in 2000). You can find out the tablename, the columns, etc and build the triggers using a standard template without a tremendous amount of work.

For just what data is different, you could try something like http://www.red-gate.com/products/SQL_Data_Compare/index.htm. It might be too slow for you if you have a lot of data. It also cannot tell you what has been deleted.

|||

For those who would like to know i found the solution to get what i wanted :

The command sp_spaceused give a lot of information about a table or a database including the unused space and row count.

Knowing that i was able to get the number of deleted record by comparing the size taken by undeleted row.