SQL Server equivalent of Oracle context variables
Posted on 2005-05-12
I've been charged with mirroring functionality, which exists in my company's Oracle db, to SQL Server, and have come to realize that Oracle and Sql Server are nowhere near as similar as I was led to believe (or maybe wanted to believe)!
Anyway, my latest in a series of problems is trying to find a SQL Server equivalent of Oracle's CONTEXT VARIABLES.
Here's the situation:
We have a web app which is used by many users (as one wuold expect).
The app interacts with an Oracle db, to store and retrieve user and app-related data.
Certain operations performed on the database (eg. Inserts into certain tables) are logged using triggers.
These triggers also log information SPECIFIC to the user & session that caused the modification of data.
The current methodology for this is as follows:
1) Web app stores current user's info and session info (eg. what task the user is in the middle of) in Oracle context variables.
2) Web app calls stored procedure to perform insert/update/delete on a table.
3) Trigger on that table fires, and reads values from context variables and puts these values in the logging table.
4) Web app clears context variables.
So, does anyone have any ideas/suggestions for achieving the same functionality in SQL Server?
We currently have a solution in place, but I personally don't think it's the best possible... and so am looking for other ideas.
I will post the current solution if you would like me to.
Thanks in advance,