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

Question

从常规T-SQL触发器调用SQLCLR存储过程而不是直接部署SQLCLR触发器有什么好处吗？

在一个非常大的表中发生特定的列更改(StatusID)时，需要通知我。

目前使用了许多windows服务。每一个都监视它自己的StatusID，即查询db以获得特定的StatusID：SELECT a,b,c FROM t WHERE StatusID = @status。

我想尝试将逻辑从服务移动到SQLCLR程序集，并使用SQLCLR触发器调用它们。这是个好主意吗？有没有更好的主意？

Answer 1

在我看来，这不需要SQLCLR。然而，这取决于你所说的“被通知”是什么意思。如果可能，我会使用常见的触发器和SQL代理作业来完成通知。

Answer 2

从常规触发器调用SQLCLR存储过程而不是直接部署SQLCLR触发器有什么好处吗？

好吧，有一些不同之处。这些差异如何与优点或缺点相关，取决于每种情况的具体情况。

SQLCLR 触发器调用

• 对象(存储过程或函数)

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代码首先需要从伪表复制，那么您非常需要使用SQLCLR触发器(否则，使用愚蠢的SELECT -伪表- to -local-temp- tables技巧)。

SQLCLR触发器无法访问@@PROCID (即使使用Context Connection = true;创建SqlConnection )，因此确实没有简单的方法来确定哪个表被修改导致触发器被触发。我之所以强调“简单”，是因为可以使用T-SQL触发器(设置为表上的“第一个”触发器)(尽管我还没有对其进行测试)来设置带有表名的CONTEXT_INFO，但是不能将CONTEXT_INFO用于其他任何事情。可能还有另一种方法，我几乎已经解决了，但还没有测试它(当我有机会测试时，如果它工作，我将尝试记住更新这里与instructions).的链接

在非常大的表中发生特定的列更改(StatusID)时，需要通知

。当前使用多个windows服务。每一个都监视其自己的状态，即查询db以获得特定的状态:从t中选择a、b、c，其中StatusID =@ StatusID。

这肯定可以得到更好的处理，而且不需要使用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语句的处理与DML语句分开，因为触发器在系统生成的内部事务中运行。这就是为什么您不希望立即将更改的行作为此触发器的一部分进行处理的原因，因为这很容易增加表上的阻塞，因为事务在触发器完成之前不会完成；如果触发器出错，则DML语句将被回滚。

Answer 3

如果直接从CLR调用服务，则必须使用标准SOAP绑定，因为CLR仅支持.NET 2.0样式的Web引用。

使用SQL Server Service broker将是一个更健壮的解决方案，但正如您所提到的，它很复杂。使用SQLCLR的代价是，如果触发器抛出未处理的错误，更新事务将被回滚。此外，如果您在每次更新时都阻塞服务调用，那么事务吞吐量肯定会受到影响。