gatilhos - usando as tabelas inseridas/excluídas no SQL dinâmico

Sem mais informações sobre o objetivo pretendido dessa solicitação, certamente parece que, mesmo com esse problema imediato resolvido, o código de trabalho pode não fornecer nada realmente útil. Algumas preocupações são:

• Dependendo de quantas tabelas este Trigger será colocado e/ou com que frequência as operações DML são, isso pode causar pequenos problemas de desempenho no(s) banco(s) de dados onde este Trigger está criando as tabelas porque a criação de tabelas requer um Schema-Lock (I acredito) e fazer isso com muita frequência pode complicar algumas outras operações.
• Se este Trigger for colocado em mais de uma Tabela, como você distinguirá as operações entre as diferentes Tabelas (a menos que dê a elas seu próprio prefixo para as tabelas criadas dinamicamente)?
• Você tem um UpdatedDateou algum campo de data na tabela? Caso contrário, não há senso de cronologia sem olhar para a data de criação da tabela.
• Como você planeja limpar todas essas várias tabelas? Talvez seja melhor criar um Schema só para segurar essas tabelas?
• Você planeja indicar em algum lugar a operação DML que ocorreu?
• Se você quiser rastrear os valores "antes" e "depois" em um UPDATE, precisará capturar as tabelas insertede . deletedMas se eles tiverem nomes baseados em GUID, você não poderá correlacionar entre as tabelas de cópia "inseridas" e "excluídas" para uma determinada operação UPDATE. Você teria que reutilizar o mesmo valor GUID e denotar "inserir" ou "excluir" no prefixo do nome da tabela. Se você não estivesse criando a tabela dinamicamente, poderia incluir uma coluna especificando a ação DML, despejar ambas as insertedtabelas deletedna tabela já existente e apenas usar um GUID ou INT de uma sequência para correlacionar entre 2 linhas da mesma UPDATEoperação .
• Dependendo de qual versão e edição do SQL Server você está usando, você pode querer olhar para Change Tracking e Change Data Capture .

No entanto, com tudo isso dito, a questão da interação com as tabelas insertede por meio do SQL dinâmico é um problema interessante. deletedInfelizmente, isso não pode ser feito em T-SQL. Então agora também é um desafio :-). Felizmente, isso pode realmente ser feito. Como assim? Com uma ajudinha do nosso amigo Mr SQLCLR.

Agora, não parece haver muitas situações que realmente exijam ou mesmo se beneficiem de Triggers SQLCLR. Eles parecem ser as coisas menos úteis que você pode criar com o SQLCLR. No entanto, aqui temos um cenário para o qual eles se encaixam perfeitamente. O SQL enviado do código SQLCLR é SQL dinâmico. E os gatilhos SQLCLR têm acesso às tabelas insertede deleted, portanto, parece que os gatilhos SQLCLR podem acessar as tabelas insertede deletedno SQL dinâmico. Abaixo está o código que faz exatamente isso para realizar esta solicitação (observe que a conexão do banco de dados está usando a "Conexão de Contexto" em processo, portanto, o Assembly pode ser marcado com PERMISSION_SET = SAFE; não há necessidade de uma Chave Assimétrica ou de definir o banco de dados para TRUSTWORTHY ON):

Tabela de teste para o Trigger a ser criado (se estiver usando Visual Studio / SSDT, a definição da tabela deve ser incluída no projeto):

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
);

O código 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();
        }
    }
}

Objeto wrapper T-SQL para colocar o gatilho SQLCLR na tabela:

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

(Alguns anos depois....)

Na verdade, o que o título do tópico descreve PODE ser feito com T-SQL sob a condição de que você possa fazer o processamento do inserido com um cursor.

Ou seja: É permitido criar um cursor acessando [Inserted] e depois passar esse nome para o código dinâmico. Você pode até fazer com que o sql dinâmico faça todo o open, while, fetch, close e desaloque coisas.

Portanto, no exemplo do OP, isso não seria bom, mas no caso de um gatilho mais complexo percorrendo o inserido um por um para fazer coisas, essa poderia ser uma solução muito real.