sadair (6/9/2010)
Great article, thank you.This will all work well when the delete is accomplished via the proc, but what happens when the delete is ad hoc from Enterprise Manager or other? Will the trigger fail or will it pull data from an existing CONTEXT_INFO and put incorrect values in the audit table?
Just my thoughts,
Steve
Depending upon how the code in the trigger is written as it relates to CONTEXT_INFO, it will either fail or succeed. In our situation, we have code in the trigger to validate that the value in CONTEXT_INFO is correct.
As for ad hoc row deletions via SSMS, we don't do it against our production systems. When the situation is really needed, we use a T-SQL script that properly sets CONTEXT_INFO before calling the stored procedure or performs the actual row deletion. The audit trail is then correct as the information will record that someone other than a customer user performed the deletion.