主页 / dba / 问题 / 120131
Accepted
JohnG
Asked: 2015-11-05 14:11:43 +0800 CST

触发器 - 在动态 SQL 中使用插入/删除的表

  • 772

在触发器中，我试图创建一个唯一的表名（使用NEWID()），我可以存储在插入和删除的表中找到的数据。

Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)

我收到以下错误：无效的对象名称“已插入”

我知道我可以做到：

Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)

但我不想使用 TempDB，因为这些表可能会变得很大，而且我也觉得它是多余的。有没有办法避免创建#inserted？

sql-server trigger

2 个回答

  1. Best Answer

    如果没有更深入地了解此请求的预期目标，似乎即使解决了这个直接问题，工作代码也可能无法提供任何真正有用的东西。一些担忧是：

    • 根据此触发器将放置在多少表上和/或 DML 操作的频率，这可能会导致此触发器正在创建表的数据库中出现轻微的性能问题，因为创建表需要 Schema-Lock（我相信）并且过于频繁地这样做可能会使其他一些操作复杂化。
    • 如果这个 Trigger 将被放置在多个 Table 上，您将如何区分不同 Table 之间的操作（除非为动态创建的表赋予它们自己的前缀）？
    • 表中有一个UpdatedDate或某个日期字段吗？如果不是，不看表创建日期就没有年代感。
    • 你打算如何清理所有这些不同的表？也许最好创建一个模式来保存这些表？
    • 您是否打算在任何地方指示发生的 DML 操作？
    • 如果要跟踪 an 上的“之前”和“之后”值UPDATE，则需要同时捕获inserted和deleted表。但是，如果它们具有基于 GUID 的名称，那么您将无法关联特定 UPDATE 操作的“插入”和“删除”复制表。您必须重新使用相同的 GUID 值并在表名前缀中表示“插入”或“删除”。如果您没有动态创建表，那么您可以包含一个指定 DML 操作的列，将表inserted和deleted表都转储到已经存在的表中，并且只需使用序列中的 GUID 或 INT 来关联同一UPDATE操作的 2 行.
    • 根据您使用的 SQL Server 版本和版本，您可能需要查看Change Tracking和Change Data Capture。

    然而，话虽如此，通过动态 SQLinserted与表交互的问题是一个有趣的问题。deleted不幸的是，它不能在 T-SQL 中完成。所以现在这也是一个挑战:-)。幸运的是，这实际上是可以做到的。怎么会这样？在我们的朋友 SQLCLR 先生的帮助下。

    现在，似乎没有很多情况真正需要，甚至受益于 SQLCLR 触发器。它们似乎是您可以使用 SQLCLR 创建的最没用的东西。但是，在这里我们有一个场景，它们非常适合。从 SQLCLR 代码提交的 SQL 是动态 SQL。并且 SQLCLR 触发器可以访问inserted和deleted表，因此 SQLCLR 触发器似乎可以访问动态 SQL 中的inserted和表。deleted下面是完成此请求的代码（请注意，数据库连接正在使用进程内“上下文连接”，因此程序集可以标记为PERMISSION_SET = SAFE；不需要非对称密钥或将数据库设置为TRUSTWORTHY ON):

    要在其上创建触发器的测试表（如果使用 Visual Studio / SSDT，则表定义必须包含在项目中）：

    CREATE TABLE TableThatHasTriggers
(
   TableThatHasTriggersID INT IDENTITY(1, 1) NOT NULL
                          CONSTRAINT [PK_TableThatHasTriggers] PRIMARY KEY, 
   InsertTime DATETIME NOT NULL
              CONSTRAINT [DF_TableThatHasTriggers_InsertTime] DEFAULT (GETDATE()),
   SomeValue NVARCHAR(50) COLLATE Latin1_General_100_CI_AS NULL
);

    SQLCLR C# 代码： 

    using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class Triggers
{
    [SqlTrigger(Target = "TableThatHasTriggers", Event = "FOR INSERT, UPDATE")]
    public static void tr_TableThatHasTriggers_audit()
    {
        string _AuditSQL = @"
                SELECT ins.*
                INTO   dbo.[TMPIns_" + Guid.NewGuid().ToString().Replace("-", "") + @"]
                FROM   INSERTED ins;
";

        SqlConnection _Connection = new SqlConnection("Context Connection = true");
        SqlCommand _Command = _Connection.CreateCommand();
        _Command.CommandText = _AuditSQL;

        // SqlContext.Pipe.Send(_AuditSQL); // display query for debugging purposes ONLY

        try
        {
            _Connection.Open();
            _Command.ExecuteNonQuery();
        }
        finally
        {
            _Command.Dispose();
            _Connection.Dispose();
        }
    }
}

    将 SQLCLR 触发器放置到表上的 T-SQL 包装器对象：

    CREATE TRIGGER [dbo].[tr_TableThatHasTriggers_SQLCLRaudit]
  ON [dbo].[TableThatHasTriggers]
  AFTER INSERT, UPDATE
  AS EXTERNAL NAME
             [InsertedTableViaDynamicSQL].[Triggers].[tr_TableThatHasTriggers_SQLCLRaudit];
    • 2

  2. （若干年后……）

    实际上，线程标题描述的可以在您明智地使用游标进行插入的处理的条件下使用T-SQL完成。

    即：允许创建一个访问 [Inserted] 的游标，然后将该名称传递给动态代码。您甚至可以让动态 sql 执行所有打开、同时、获取、关闭和解除分配的操作。

    因此，在来自 OP 的示例中，这并不好，但在更复杂的触发器遍历插入的一个一个来做事情的情况下，这可能是一个非常真实的解决方案。

    • 0

相关问题