Can I pass arguments to an external (CLR) SQL Serv

Question

I have a trigger in SQL Server, but I need to pass arguments to the CLR code, i.e., information not provided in the trigger context.

Is something like this even possible?

CREATE TRIGGER MyTrigger ON MyTable FOR INSERT
AS EXTERNAL NAME MyAssembly.MyNamespace.MyTriggerHandler("Foo", "Bar")

These arguments would be static, of course.

The number of argument permutations is discrete, but creating a separate class or function in the CLR assembly for each would be unwieldy, and would require a compile / deploy step I want to avoid every time another trigger is needed.

Answer 1

After a little research, it appears what I want to do, can't be done.

Methods passed using the trigger "external name" clause must be parameterless, and the SqlTriggerContext cannot be modified in the CREATE TRIGGER statement.

I ended up going the permutation route and just having fewer variations of supported arguments. Perhaps .NET integration will be a little more robust next time around.

Answer 2

yeah you can use the sp_OA procedures for this: http://msdn.microsoft.com/en-us/library/aa238863(SQL.80).aspx

Answer 3

In a sense you can pass information to a Trigger (whether T-SQL or SQLCLR), just not directly as you are showing in the question. Still, you have 2 or 3 options for passing information that is not part of the DML operation itself to any Triggers in chain of events:

1. Local temporary table
2. SET CONTEXT_INFO / CONTEXT_INFO
3. On SQL Server 2016 or newer: sp_set_session_context / SESSION_CONTEXT

In all cases you would get the values by executing a SqlCommand (use "Context Connection = true;" as the connection string) with an output SqlParameter to pull the value into the .NET code.