Error tracing using triggers

pts.
Tags:
Software Quality Assurance
SQL
SQL Server
T-SQL
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! :-) Regards Jacob

Answer Wiki

Thanks. We'll let you know when a new response is added.

Dear Jacob,
From your mail I can think of only one solution toh there can be alternatives depending upon the exact requirement.

Inorder to trace back to the transaction which has caused the delete of a particular row or all rows you need to enable the AUDIT on the database. This option is resource crazy. Hence choose it only if necessary.

Other way to search the archive logs if your database is in archive mode. You will be able to pinpoint the exact machine/time/person/event when the delete occured.

Regards,

Manjit

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following