Can I pass arguments to an external (CLR) SQL Server trigger?

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

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

Answer 2

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.