I'm running an application, using a SQL Server 2000.
At some point we've implemented an error, that seems to cause the wrong data to be deleted from a table. We've tried locating the error both by browsing through the potential code likely to contain then error, and by logging different kinds of incidents in these parts of the code.
Now I wanna try a different approach. Only I'm not really sure how to implement this solution:
I want to implement a delete trigger on the table, from where my data is falsely deleted. The trigger has to write information to the transaction-log (or any other log) containing the time of the action along with the name of the Stored Procedure and its given parameters for the actual query, that causes the delete-action. Also I need to log the exact data, that is deleted from the table, so that I by those data can trace backwards to the correct transaction and SQL-query, that originally deleted the data from my table.
I expect that I'll be using the SP, xp_logevent for logging the information.
Is this the best way to get my informations logged?
And how do I get the information on all the data, deleted by the delete-action - and how do I get the SQL-statement causing the delete to perform?
Hope someone has an idea to get me going! :-)