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
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
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
- 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
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
deleted tables, so it would seem that SQLCLR Triggers can access the
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
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:
public class Triggers
[SqlTrigger(Target = "TableThatHasTriggers", Event = "FOR INSERT, UPDATE")]
public static void tr_TableThatHasTriggers_audit()
string _AuditSQL = @"
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
T-SQL wrapper object to place the SQLCLR Trigger onto the Table:
CREATE TRIGGER [dbo].[tr_TableThatHasTriggers_SQLCLRaudit]
AFTER INSERT, UPDATE
AS EXTERNAL NAME