1

I have created one small application in C# .NET where I initialize and assign some public variable/static variable (e.g. my login userid). Now some database operations is happens within this application. I have created CLR trigger in a separate project (.dll) which records database changes doing by logged in user - that bit works fine. Now I want that userid who is doing the operation on this database, and want to store it in a public variable in my running application.

Is there any way to access a .NET application variable in the CLR trigger class?

Improve this question
10
  • I don't think you can. You need to add UserId column to table that you monitor and that's how trigger can know which user did this operation. Commented Oct 7, 2016 at 8:31
  • @Andrey, That I can do, but for that I have to add column in all the necessary tables. What happen If I declare static variable (e.g. _userid) in CLR trigger class and assign a value to that from my application? Does it remains and available for trigger? or sql-server create new instance of CLR class each and every time when it is called. Commented Oct 7, 2016 at 8:36
  • 1
    If you want to act on the fact that user modified a table then you better record it. Imagine your CLR trigger fails or you decide to apply it retroactively - you will irrevocably lose important information (who edited table). You can't pass stuff via static variables between app and trigger because they live in separate processes (app is app, trigger lives in MSSQL server process). Commented Oct 7, 2016 at 8:41
  • @Andrey, I am totally agree with you. But my requirement is little different. I want to log each and every changes made by user. like CDC provided by sql-server itself. which is may be not possible through above logic. Commented Oct 7, 2016 at 8:53
  • 1
    If you want to log history of changes to certain tables - it's common practice to record both user id and time of change in the table itself (something like EditorID and Version), and then use triggers to populate history table (that you already do). Commented Oct 7, 2016 at 9:28

1 Answer 1

Reset to default
1

Is there any way to access a .NET application variable in the CLR trigger class?

No. Nor is there any way to access local variables between regular T-SQL Stored Procedures / Functions / Triggers. If you want to pass info from the app layer to a Trigger, you have the same options that are available to T-SQL stored procedures / ad hoc queries:

  1. Store the value in the session via SET CONTEXT_INFO, which is a VARBINARY(128) datatype. You can read the value via the CONTEXT_INFO() function. This value is also readable in the sys.dm_exec_sessions DMV. If a process doesn't set the value, it will be NULL. This approach does not work if you are already using CONTEXT_INFO to store other information.

  2. Create a local temp table (keyed to each session_id / SPID) and store the value in it. Then just SELECT from it in each Trigger. If a process doesn't create the local temp table, you will get an error on the SELECT query (so maybe you would wrap that in an IF OBJECT_ID(N'tempdb..#tempTableName') IS NOT NULL construct.

  3. If using SQL Server 2016 (or newer), you can use the new SESSION_CONTEXT hash table.

In all three cases, after opening the SqlConnection in the app layer, execute a SQL statement to do the above via ExecuteNonQuery() and then do your other operation(s). Then, in the SQLCLR Trigger, the first step would be to connect to the current process / session using Context Connection = true; and grab the value.

Also, all three approaches noted above allow for T-SQL code to also pass this audit information to Triggers, whether they are T-SQL or SQLCLR. This is important because it is doubtful that the app will be responsible for 100% of the changes that occur. Usually people have SQL Agent Jobs and/or ad hoc support / release queries that makes changes that wouldn't have been able to get tracked if that required assigning a variable in the app layer.

I had assigned a value in CLR trigger class static variable (e.g _userid), but this value not getting while calling another static method in same class (which is called by me from trigger). why? If CLR class is called and assess by MSSQL server process, same class is also assessable with my app too.

Not only is SQL Server a different App Domains than your app is using, they are also entirely different CLR hosts.

Either way, you wouldn't have wanted the static variable to work since all SQL Server sessions will share a static variable, hence overwriting other sessions running at the same time ;-).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.