triggers - using the inserted / deleted tables in Dynamic SQL

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.

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


I get the following error: Invalid object name 'inserted'

I know I can do:

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


But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?

Without more insight into the intended goal of this request, it certainly seems as though even with this immediate issue solved, the working code might not provide anything truly useful. Some concerns are:

• Depending on how many tables this Trigger will be placed on and/or how frequent the DML operations are, this could cause minor performance issues in the database(s) where this Trigger is creating the tables because creating tables requires a Schema-Lock (I believe) and doing that too frequently might complicate some other operations.

• If this Trigger will be placed on more than one Table, how will you distinguish the operations between the different Tables (unless giving them their own prefix for the dynamically created tables)?

• Do you have an UpdatedDate or some date field in the table? If not, there is no sense of chronology without looking at the table creation date.

• How do you plan to clean up all of these various tables? Maybe it would be best to create a Schema just to hold these tables?

• Do you plan on indicating anywhere the DML operation that took place?

• If you want to track the "before" and "after" values on an UPDATE, then you need to capture both inserted and deleted tables. But if they have GUID-based names, then you won't be able to correlate between the "inserted" and "deleted" copy tables for a particular UPDATE operation. You would have to re-use the same GUID value and denote the "insert" or "delete" in the table name prefix. If you weren't dynamically creating the table, then you could include a column specifying the DML action, dump both inserted and deleted tables into the already existing table, and just use a GUID or INT from a Sequence to correlate between 2 rows of the same UPDATE operation.

• Depending on what version and edition of SQL Server you are using, you might want to look into Change Tracking and Change Data Capture.

However, all of that being said, the issue of interacting with the inserted and deleted tables via Dynamic SQL is an interesting problem. Unfortunately, it can't be done in T-SQL. So now it's also a challenge :-). Fortunately, this can actually be done. How so? With a little help from our friend, Mr SQLCLR.

Now, there doesn't seem to be a lot of situations that really demand, or even benefit from, SQLCLR Triggers. They seem to be the least useful things you can create with SQLCLR. However, here we have a scenario that they are a wonderful fit for. SQL submitted from SQLCLR code is Dynamic SQL. And SQLCLR Triggers have access to the inserted and deleted tables, so it would seem that SQLCLR Triggers can access the inserted and deleted tables in Dynamic SQL. Below is the code that does exactly that to accomplish this request (please note that the DB connection is using the in-process "Context Connection", so the Assembly can be marked with PERMISSION_SET = SAFE; no need for an Asymmetric Key or to set the database to TRUSTWORTHY ON):

Test table for the Trigger to be created on (if using Visual Studio / SSDT, the table definition has to be included in the project):

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


The SQLCLR C# code:

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

T-SQL wrapper object to place the SQLCLR Trigger onto the Table:

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


StackExchange Database Administrators Q#120131, answer score: 2