Hello,

We are trying to implement a logging mechanism over the Strataframe RBS. We have written triggers on the RBS tables and in order to pass application values (like the username) to the triggers we are setting the context_info variable of StrataFrame.

In program.cs:

sSql = "declare @context_info varbinary(50);set @context_info = cast('" + MicroFour.StrataFrame.Security.SecurityBasics.CurrentUser.UserPK.ToString();

sSql +="@" + sFullIP + "' as varbinary(50));set context_info @context_info;";

DataLayer.DataSources["MySecurityKey"].ExecuteNonQuery(sSql);

We used SQL Server profiler and saw that context_info is properly set with this command.

When the program loads and I click on the security editor a number of SQL statements are executes with sp_reset and the context_info variable is also cleared.

We would like to find a method so that context_info is not cleared and our triggers use this information for logging.

Could you please help us?

What command are you trying to execute?  I see that you are setting variables, but when and where are you trying to use those variables?  Also, this seems a bit precarious in regards to the reliability here because so many other things can get executed.  Most likely, the variables are just losing scope by the time they get to you.  I see that the profiler caught sp_reset, but this isn't something that we execute directly.

Hi Trent,

Let me clarify our problem :

All we want is simply pass some variables (such as user name) to MS SQL Triggers for some logging/auditing purposes.

We try to achieve this by setting context_info variable.

But this setting is lost with sp_reset.

What is your suggestion about passing varibles to triggers in MSSQL?

Would you like share your experience in your Medical Application?

Thanks in advance...

Kind Regards

We took a different approach in our medical application on several different levels.  To begin, every table, has a field that stored the user that created the record.  So when the trigger fires, we have everything we needed as part of the new record to query the SFSUsers and get other details if necessary.  We also have some client side auditing for the more complex elements.  You don't want to do this application wide, but for the most important auditing implementations, we will use BO events and method overrides to capture and log very detailed changes.  But like I said, this is not application wide, only in very select and precise areas.

Just as a word of caution, if you have to be somewhat careful on triggers too.  If you over complicate your triggers you can really slow down update and deletion performance which as your application grows can become very noticeable and irritating to the end-user.  So just make sure that things are optimized.

There are 100 ways to work through this, but since I don't know your app, I am just trying to throw out some suggestions.  I would abandon the manual setting of the variable though, this can become an issue in other ways depending on how an end-user SQL Server environment is setup.

One other thought, every time a user logs, a unique session ID is created in our app.  This let's us track a complete session for a user from start to finish.  This is very handy for calculating many things including up-time, etc.  But you could do something similar and use this session ID as part of a compound key for a table that you create that stores the UserPK by session, table, etc.  Just be sure to think about mutli-user sessions and what is unique to your session.  You could then store this off in a work table that is accessed by the trigger.

These are just ideas....trying to help get the juices flowing.

Hi Trent,

Thanks a lot for your detailed explanation.

we tried to narrow the problem and we believe that  if we could catch a event after a new connection open we would solve our problem.

Can u suggest a way to catch the event after a new connection opened in Strataframework?

Kind regards...

On the client side?

Yes.

No reply?