Best way to Share Data Between Stored Procedure and trigger

but one problem when my store proc will create a temp table with a name #user_data then another user who can create temp table with same name or another user may execute that same store proc as a result store proc will create a temp table with same name so then there will be a clash &

No, there will be no clash. A temp table is local to the process that created it, so two users can create two temp tables #user_data with no problem.

But the solution stinks. Having a trigger that assumes that a certain temp table exists is asking for trouble.

But I don't understand what you need the temp table for. The only piece of data youa are talking about is the user name, and that is exactly what SET CONTEXT_INFO is designed for.

---

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se