November 4, 2015 at 12:46 pm
Hi all,
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?
Thanks in advance.
JG
November 4, 2015 at 5:46 pm
What you're doing is very dangerous code inside a trigger, as it's likely to run quite a while.
That said, you can only reference the "inserted" from within the trigger itself, not from dynamic SQL at all.
Therefore, create a permanent table with a NEWID() column which you can use to determine which rows are from the current trigger, something like below. And, yes, that will slow down the trigger even more:
INSERT INTO dbo.permanent_trigger_table ( newid_value, ... )
SELECT @newid_value AS id_col, ...
FROM inserted
...
SELECT ...
FROM dbo.permanent_trigger_table
WHERE newid_value = @newid_value
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 5, 2015 at 7:35 am
Thanks Scott... Didn't think of that one...
I like it...
😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply