6

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?

  • 2
    What is the actual problem you are trying to solve? What are you going to do with the TMPIns... table after you have populated it with all of the data from inserted? – Aaron Bertrand Nov 4 '15 at 23:28
  • 1
    What version and edition of SQL Server are you using? Is this only going to be used on one table? Or is the "TMPIns" just a generic prefix for the question, but you plan on using table-name specific prefixes to distinguish? And 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. Creating a table for each INSERT / UPDATE / DELETE statement is not wise as I believe it requires a schema-lock to create the table. Have you looked into Change Tracking or Change Data Capture? – Solomon Rutzky Nov 5 '15 at 2:00
  • This is just part of an auditing SQL Service Broker System... It's a trigger that calls a procedure to send messages. I tried to send XML messages passing XML inserted and deleted to a procedure, but performance was very poor. So now, I'm letting the trigger create 2 unique Tables (tmpins and Tmpdel) and send the reference in the message. The procedure that processes the Queue takes the references and uses the existing tables to audit the changes. The performance is much better but I want to minimize the load in the trigger. – JohnG Nov 5 '15 at 13:17
  • I assume this question "Trigger to create a variable Table to be sent to service broker" is this same project. Why has it not been mentioned? There is a lot more info in that question that would have really helped put this into context. – Solomon Rutzky Nov 5 '15 at 16:06
  • "The procedure that processes the Queue takes the references and uses the existing tables to audit the changes." How is it that the receiver of the messages has access to those tables? Is it on the same system, or using a Linked Server? How many tables are you auditing? Are you tracking changes in all fields or just some? Have you looked into Change Data Capture? – Solomon Rutzky Nov 5 '15 at 16:09
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.