是否可以将参数传递给外部（CLR）SQL Server触发器？ [英] Can I pass arguments to an external (CLR) SQL Server trigger?

问题描述

我在SQL Server中有一个触发器，但是我需要将参数传递给CLR代码，即触发器上下文中未提供的信息。

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.

类似于

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

这些参数当然是静态的。

These arguments would be static, of course.

参数排列的数量是离散的，但是在CLR程序集中为每个参数创建单独的类或函数将很麻烦，并且需要我想要的编译/部署步骤避免每次都需要另一个触发器。

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.

推荐答案

经过一番研究，看来我想做的事情不能做

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

使用触发器外部名称子句传递的方法必须是无参数的，并且SqlTriggerContext不能在CREATE TRIGGER语句中进行修改。

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

我最终选择了排列路线，并且受支持的参数变型较少。也许下次.NET集成会更强大。

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.

这篇关于是否可以将参数传递给外部（CLR）SQL Server触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！