I can appreciate a get-it-done-today workaround, but wouldn't a better approach be to rewrite the trigger/application layers to provide better tier isolation? I assumed a trigger should be encapsulated such that it can provide integrity validation of data based solely on RI rules; it should not need information provided by the user in the SP scope. Likewise the business logic probably should be enforced at the interface between data and the application (aka SP). Pushing this requirement all the way back to the UI, the application should not even call for a delete without proper authentication and "reason" coding.

I also wonder what kind of performance you can expect from the xml->CONTEXT_INFO->xml happening per transaction. Granted deletes may be infrequent but transactional overhead should be considered before it becomes a performance problem.

I guess I don't understand why the auditing is done in the trigger rather than directly in the SP. I have seen where the trigger provides a single place to hook actions on the data when multiple SP already exist that act on the table. This solution would still require visiting each SP to ensure the context_info is properly set.

Thanks for the article. I don't think I would have learned about the context_info myself.