如何向外部（SQLCLR）SQL Server触发器传递参数

Question

我创建了一个触发器，像这样调用一个程序集：

CREATE TRIGGER Testrigger ON STATION  
FOR INSERT 
AS EXTERNAL NAME assemblytest.[WriteTimeInfile.Program].Testrigger 

那个程序集中的.NET代码做了类似于这样的事情：

namespace WriteTimeInfile
{
    public class Program
    {
        [SqlTrigger(Name = @"Testrigger", Target = "[dbo].[STATION]", Event = "FOR INSERT, UPDATE, DELETE")]
        public static void Testrigger()
        {
            File.AppendAllText(@"C:\Users\Vivien\date.txt",
            DateTime.Now.ToString() + Environment.NewLine);
        }
    }
}

我希望能够将已创建的行或更新的行作为参数传递，类似于以下内容：

CREATE TRIGGER Testrigger ON STATION  
AFTER INSERT 
AS 
EXTERNAL NAME assemblytest.[WriteTimeInfile.Program].Testrigger (STATION.ID)

我在StackOverflow上发现了一个7年前的topic，其中说没有办法向CLR程序集传递参数。
我想问一下，在最近的SQL Server版本中是否已经有可能实现。

您知道是否有方法可以实现，并且如果有，如何实现吗？

- Pipo

---

你总是可以从SQLCLR触发器内部访问INSERTED和DELETED表，如此早期版本的文档所示。你不需要向触发器传递任何参数，就像你不需要向普通触发器传递任何参数一样。 - Panagiotis Kanavos

你为什么想要将参数传递给触发器？是什么类型的参数？为什么不使用存储过程呢？如果你想要实现某种带有变更检测的ETL功能，可以使用Change Tracking（而非CDC）来查找修改行的键和变更类型，并对其进行处理。 - Panagiotis Kanavos

Answer 1

不，您不能直接向SQLCLR触发器传递参数。 但是，您可以通过几种方式间接地传递值（与常规T-SQL触发器相同）：

1. 本地临时表
2. SET CONTEXT_INFO / CONTEXT_INFO
3. 在SQL Server 2016或更新版本中：sp_set_session_context / SESSION_CONTEXT

在所有情况下，您需要通过执行带有输出SqlParameter的SqlCommand来获取值，以将其引入.NET代码。（请参见有关使用情况的结尾注释）。

但是，如果您只想获取“插入”和/或“删除”表的值，则这些不会是参数。只需使用SqlCommand使用Context Connection = true作为连接字符串，并使用SqlDataReader来SELECT它们即可。您可以在CLR Triggers的MSDN页面中的Sample CLR Trigger部分看到此示例。

---

关于传递值给不属于DML操作的触发器的注意事项：

虽然这不是很常见的操作，但是确实存在将某个信息从主上下文传递到事件链中的一个或多个触发器的有效用例。我遇到的最常见的两种情况是：1）将基于应用程序的登录或用户ID（不属于SQL Server）传递给审核触发器以获得删除行的用户信息（因为该信息无法添加到DELETE操作的ModifiedBy列中），以及2）根据条件临时禁用触发器。是的，这是可能的，并且可以正常工作。请参见我在DBA.StackExchange上的以下答案：

• 如何在没有递归的情况下使用触发器更新同一行上的列？
• 从另一个触发器禁用或启用触发器

- Solomon Rutzky

---

试图将触发器用作存储过程可能意味着它们本来就不应该是触发器。 - Panagiotis Kanavos

如果这是试图将触发器用作存储过程的情况，那我会同意你的看法 :-）。但是，确实存在一些使用触发器应该使用的情况，有时仍然需要满足这种需求。 - Solomon Rutzky

@srutzky 谢谢，你正在成为我的官方问题解决者。 - Pipo

Answer 2

"

在SQLCLR触发器内，INSERTED和DELETED伪表始终可用于直接查询，如文档this 9+ years old version所示。您可以随时查询它们，例如：

"

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
    conn.Open();
    SqlCommand sqlComm = new SqlCommand();
    SqlPipe sqlP = SqlContext.Pipe;

    sqlComm.Connection = conn;
    sqlComm.CommandText = "SELECT UserName from INSERTED";

    userName.Value = sqlComm.ExecuteScalar().ToString();

    if (IsEMailAddress(userName.Value.ToString()))
    {
        sqlComm.Parameters.Add(userName);
        sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(@username)";
        sqlP.Send(sqlComm.CommandText);
        sqlP.ExecuteAndSend(sqlComm);
    }
}

最新的样本是相同的。

您不需要将它们作为参数传递，就像您不需要将它们作为普通触发器的参数传递一样。这些表始终可供查询。

我怀疑这些表没有作为上下文对象上的集合暴露出来，因为那将需要从 SQL Server 的缓冲区中复制它们（浪费 CPU 和内存）。另一个原因是集合无法有效地查询。您要么必须使用 LINQ（使用更多 CPU），要么只需迭代整个内容（内存和 CPU 浪费）。内存浪费将是更大的问题，因为这些内存可以用于缓冲更多数据和索引，从而加快访问速度。

我怀疑您链接到的问题想要问同样的问题，但 OP 假设伪表必须作为参数传递。所以他问了关于参数的问题，而不是实际问题。

- Panagiotis Kanavos

---

谢谢，这很好知道，但我想能够从更新的行或任何参数中传递参数。 - Pipo

无论如何，您都不能使用任何触发器来执行此操作，因为这没有意义。触发器由服务器执行，因此您无法在它们之外操纵值。至于行（复数），它们已经可用了。复数是重要的部分。在INSERTED或DELETED表中可以有一行或一百万行。语法会改变，但访问表的方式保持不变。 - Panagiotis Kanavos

@VivienPipo和Panagiotis：虽然这不是非常常见的做法，但肯定存在将信息从主上下文传递到事件链中的一个或多个触发器的有效用例。我遇到的最常见的两种情况是：1）传递基于应用程序的登录或用户ID（不是SQL Server的一部分），以了解谁删除了行（因为该信息无法添加到“ModifiedBy”列中的“DELETE”操作中），以及2）根据条件暂时禁用触发器。是的，正如我在我的答案中所展示的那样，这是可能的，并且确实有效。 :-) - Solomon Rutzky

@srutzky 更好的做法是完全避免使用此类触发器，并在适当的情况下使用存储过程。在INSERT/UPDATE语句中使用该登录ID。 - Panagiotis Kanavos

@PanagiotisKanavos 请重新阅读我写的内容。在这个例子中，它是一个审计触发器（不能可靠地移动到存储过程中），并且涉及到DELETE语句特别是，而不是INSERT或UPDATE。你无法在删除时向行中添加值。此外，有时您需要使用ETL触发器进行迁移，但这些触发器不是长期存在的，您可能需要出于各种原因暂时禁用它们。我曾在一些场所工作遇到过这两种情况，并且从S.O.和DBA.SE上的问题可以清楚看到其他人也遇到了这些情况。 - Solomon Rutzky

@srutzky 我曾经在一些地方工作过，他们甚至没有外键。但这并不意味着这是可以接受的。 - Panagiotis Kanavos