觸發器 - 在動態 SQL 中使用插入/刪除的表

在觸發器中，我試圖創建一個唯一的表名（使用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？

如果沒有更深入地了解此請求的預期目標，似乎即使解決了這個直接問題，工作程式碼也可能無法提供任何真正有用的東西。一些擔憂是：

• 根據此觸發器將放置在多少表上和/或 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];

引用自：https://dba.stackexchange.com/questions/120131