T-SQL触发器调用SQLCLR存储过程与SQLCLR触发器

在我看来，这不需要SQLCLR。但是，这取决于“被通知”的含义。如果可能，我会使用常规触发器和SQL代理作业来进行通知。     

从常规触发器调用SQLCLR存储过程是否有任何好处，而不是立即部署SQLCLR触发器？ 嗯，有一些差异。这些差异如何与利弊相关取决于每种情况的具体情况。 调用SQLCLR对象的T-SQL触发器（存储过程或函数） T-SQL触发器可以通过类似于以下的查询来确定它们附加到哪个表：

SELECT ss.[name] AS [SchemaName], so.[name] AS [TableName]
FROM [sys].[objects] so
INNER JOIN [sys].[schemas] ss ON ss.[schema_id] = so.[schema_id]
WHERE so.[object_id] = (SELECT so2.[parent_object_id]
                        FROM [sys].[objects] so2
                        WHERE so2.[object_id] = @@PROCID);

从T-SQL触发器调用的SQLCLR对象不能直接访问

INSERTED

和

DELETED

伪表（对于T-SQL存储过程甚至

EXEC()

调用也是如此）。这些表可以复制到本地临时表中，然后可以从SQLCLR对象（或T-SQL存储过程或

EXEC()

调用）中读取，但这是创建表的时间和I / O，从中读取伪表，并再次写入

tempdb

。 SQLCLR触发器 SQLCLR触发器可以通过动态SQL与

INSERTED

和

DELETED

伪表进行交互（T-SQL触发器无法做到这一点）。当然，如果SQLCLR代码首先需要来自伪表的

SELECT

，那么你几乎需要使用SQLCLR触发器（否则执行愚蠢的copy-pseudo-tables-to-local-temp-tables技巧）。 SQLCLR触发器无法访问

@@PROCID

（即使使用

Context Connection = true;

创建

SqlConnection

），因此实际上没有简单的方法来确定哪些表被修改导致触发器被触发。我强调“简单”因为有可能（虽然我没有测试过）使用T-SQL触发器，设置为表格上的“第一”触发器，用表名设置

CONTEXT_INFO

但是你不能使用

CONTEXT_INFO

做其他事情。可能还有另一种方法，我几乎已经解决了，但尚未测试它（当我有机会测试时，如果它有效，我将尝试记住在这里更新指令的链接）。   我需要在非常大的表中通知特定的列更改（StatusID）。目前使用了许多Windows服务。每个都监视自己的StatusID，即查询特定StatusID的db：SELECT a，b，c FROM t WHERE StatusID = @status。 这绝对可以更好地处理，而且不使用SQLCLR。 SQLCLR可能很棒，但必要时应该使用它。在这种情况下，您只需要一个队列表，该表记录了表的PK列，以便更改行。

CREATE TABLE dbo.TableChangeQueue
(
  TableChangeQueueID INT IDENTITY(-2140000000, 1)
                     NOT NULL
                     CONSTRAINT [PK_TableChangeQueue] PRIMARY KEY,
  [a]                datatype_a,
  [b]                datatype_b,
  [c]                datatype_c
);

然后使用常规T-SQL触发器将

INSERT

放入该队列。有点像：

INSERT INTO dbo.TableChangeQueue ([a], [b], [c])
    SELECT [a], [b], [c]
    FROM INSERTED;

然后每个Windows服务都可以从队列表（而不是大表）中读取。处理完记录后，根据其

TableChangeQueueID

值从队列表中删除它们。您可以使用

DELETE

语句中的

OUTPUT

子句同时读取和删除它们，但如果该过程失败，您将不希望失去重新尝试处理它们的能力。 当然，这是一个简单的实现，如果这些行在处理之前多次更新，则允许重复的键条目。如果这是一个问题（即您需要键值是唯一的），那么在触发器中有处理方法。请注意，触发器不会更改Windows服务正在处理的数据，除非这不会产生问题。 这里有很多选项，但如果不知道这些过程是如何工作的，则无法具体。但无论如何，主要的一点是将更改的

StatusID

s与DML语句的处理分开，因为触发器在系统生成的内部事务中运行。这就是为什么您不希望立即处理更改的行作为此触发器的一部分，因为它可能很容易增加对表的阻塞，因为事务将在触发器完成之前完成;如果触发错误，那么DML语句将被回滚。     

如果直接从CLR调用服务，则必须使用标准SOAP绑定，因为CLR仅支持.NET 2.0样式的Web引用。 使用SQL Server Service代理将是一个更强大的解决方案，但正如您所指出的那样复杂。使用SQLCLR将支付的价格是，如果从触发器中抛出未处理的错误，您的更新事务将被回滚。如果您在每次更新时阻止服务调用，那么事务吞吐量肯定会受到影响。