如何知道TriggerContext's当前更新的表

本文关键字：更新 何知道 TriggerContext | 更新日期: 2023-09-27 18:11:19

我有一个数据库，其中的数据必须有条件地复制到同一服务器上不同数量的数据库。接收表将具有相同的名称，但将只有键列。

我想把数据库浏览逻辑放在SQLCLR中，并在源表中的数据更新时立即做出反应。因此，我希望重用相同的复制方法，根据当前更新的表复制键值。

internal static void ReplicateToInstances()
{
    if (!IsTableWriteTriggerContextAvailable()) throw new InvalidOperationException();
    string currentlyUpdatedTable; // = SqlContext.TriggerContext.??????        
    // rest of the code, irrelevant to the current issue.
}

是否有办法知道当前TriggerContext实例在哪个表上?

如何知道TriggerContext's当前更新的表

它应该是明显的，但不幸的是它不是。T-SQL触发器可以使用@@PROCID，然后在sys.objects中查找父对象，但对于SQLCLR就不那么容易了。下面是一个Microsoft Connection项目建议修复这个问题:

SQLCLR触发器应该被赋予SQLTriggerContext中的父对象

这里有一个关于重复问题的变通方法:

检索clr

中触发触发器的sqlobject

该解决方案(即使用CONTEXT_INFO)的问题是，如果以下任何一个条件为真，则它不起作用:

  1. 一个触发器可以更新一个表，它也有一个SQLCLR触发器需要知道它的父对象的名称。

然而，使用sp_settriggerorder过程将订单设置为First是正确的。

我确实有一个修复这个可能工作，即使在嵌套的触发场景，但我只是没有时间测试它(请参阅下面的UPDATE部分)。不过，这个概念是在INSERT, UPDATE, DELETE触发器中执行以下操作(以及将触发顺序设置为First):

CREATE TRIGGER [SchemaName].[tr_SetTriggerInfo]
ON    [SchemaName].[TableName]
AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
  CREATE TABLE #TriggerInfo (TriggerObjectID INT, TriggerName sysname,
                             ParentObjectID INT, ParentName sysname, TriggerNestLevel INT);
  INSERT INTO #TriggerInfo (TriggerObjectID, TriggerName,
                            ParentObjectID, ParentName, TriggerNestLevel)
      SELECT @@PROCID,
             trggr.[name] AS [TriggerName],
             trggr.[parent_object_id] AS [ParentObjectID],
             OBJECT_NAME(trggr.[parent_object_id]) AS [ParentName],
             TRIGGER_NESTLEVEL(@@PROCID, 'AFTER', 'DML') AS [TriggerNestLevel]
      FROM   sys.objects trggr
      WHERE  trggr.[object_id] = @@PROCID;
END;
GO
EXEC sp_SetTriggerOrder @TriggerName = N'[SchemaName].[tr_SetTriggerInfo]',
                        @Order = N'First',
                        @StmtType = N'DELETE';
EXEC sp_SetTriggerOrder @TriggerName = N'[SchemaName].[tr_SetTriggerInfo]',
                        @Order = N'First',
                        @StmtType = N'INSERT';
EXEC sp_SetTriggerOrder @TriggerName = N'[SchemaName].[tr_SetTriggerInfo]',
                        @Order = N'First',
                        @StmtType = N'UPDATE';
GO

这个应该工作，原因如下:

  • 一个SQLCLR触发器，使用"Context Connection = true;"的ConnectString可以读取本地临时表

  • 本地临时表在嵌套场景中的行为如下:

    • 如果一个临时表已经从父上下文中存在，那么DML语句将看到它并可以与它交互，并且这些更改将能够同时添加嵌套级别以及父级别(一个非常方便的功能)
    • 如果一个临时表已经在父上下文中存在，并且CREATE TABLE语句对相同的临时表名执行，它将创建一个新的表的副本，并且来自父上下文的同名临时表现在将不可访问(即隐藏)。这意味着，如果你处于第3关，那么当该关结束时，在第2关中运行的触发器代码仍然具有第2关的值，而不是第3关的值。

    这是不可能的CONTEXT_INFO，这就是为什么CONTEXT_INFO不能用于嵌套场景:3级将覆盖2级的值，所以当控制回到2级时，它现在在CONTEXT_INFO中有错误的值。鉴于我们只能将单个触发器设置为"First"，而不能控制哪个触发器是"second"(除非您从未有超过3个触发器，在这种情况下，您也可以使用"Last"位置)，那么您不能保证SQLCLR触发器将在"First"触发器之后立即触发，并且如果另一个触发器触发修改了具有此触发器设置的表，那么您在CONTEXT_INFO中已经损坏了数据。


实际上，上面的临时表解决方案可能不起作用，因为一旦T-SQL触发器结束，本地临时表可能会消失，这是在SQLCLR触发器触发之前。我下周还需要测试这个。如果这是实际的行为，并且本地temp不起作用，那么我有另一个应该起作用的想法。

相关文章：